The CREATE TABLE command create a high-performance un-columnized table for inserting scripted data using the INSERT INTO command. For the table to be available in the InMemory data store the command COLUMNIZE must be used after completing insertions.


/* Example of creating a table with the name periodtable that will contain all dates from 2015-2016 */

DECLARE @mindate as datetime
DECLARE @maxdate as datetime
SET @mindate=’2015-01-01’
SET @maxdate=’2016-01-01’

/* Create a new un-columnized table structure for mass insertions - much quicker than running multiple IMPORTs */
CREATE TABLE periodtable ([Posting Date] datetime, [year] int, [quarter] int, [month] int, [DateName] string)
WHILE @mindate<@maxdate

/* inserts like this only works for un-columnized tables. */
/*Please note that not all sql functions are supported, in the example below calculations */
/* are done for quarter and date instead of using the appropriate functions as of now this is by design. */
INSERT INTO periodtable ([Posting Date], [Year], [quarter], [month], [datename])
VALUES (@mindate,year(@mindate),(1+month(@mindate)-1)/3, month(@mindate),
SET @mindate=dateadd('d',1,@mindate)

/* columnize it so it gets to the data warehouse and we can perform normal operations on the table */
COLUMNIZE periodtable


Was this article helpful?
0 out of 0 found this helpful



Please sign in to leave a comment.