Dynamic Table Insert TSQL

Hi all i have been struggling this for a while now! I have some data files with a date in them, these are dumped into a staging table. What i would like my script/function to do is to re开发者_JAVA百科ad the date for each record in the staging table and move to the appropriate table. Now i know i could do this very easily just using some static inserts, for example

INSERT INTO TABLE_2011_08
WHERE Datafields = 2011_08

However i want it to be dynamic, so i was thinking something along the lines of a function/stored procedure to pass in the date for each record. However my brain is melting a bit with this!

The data records in the staging table could be something like this:-

RecordA 2011-08-30 Data Data Data
RecordB 2011-08-31 Data Data Data
RecordC 2011-09-01 Data Data Data
RecordD 2011-09-02 Data Data Data

Here you go:

CREATE TABLE dbo.Some_Meaningful_Descriptive_Name (
    record_id    CHAR(7)        NOT NULL,
    some_date    DATETIME       NOT NULL,
    some_data    VARCHAR(20)    NOT NULL,
    ...
)

Now your import process just has to put them in the table. No dynamic requirements and no need to create a new table every month.

If you really need to make them look separate then create views over the table:

CREATE VIEW dbo.Some_Meaningful_Descriptive_Name_2011_08
AS
BEGIN

    SELECT
        record_id,
        some_data
    FROM
        dbo.Some_Meaningful_Descriptive_Name
    WHERE
        some_date >= '2011-08-01 00:00:00.000' AND
        some_date <  '2011-09-01 00:00:00.000'
END

It's going to be pretty rare when you have so many rows that you need to actually put them into separate tables. (we're talking 10's of millions of rows). With partitioning now, even then it probably isn't necessary.

Putting them into separate tables is just going to make it harder to work with them in the future.

If i understand you right, you want to generate INSERT statements with a dynamically changing table name?

You can construct your SQL query as a string and execute it with EXEC:

DECLARE @sql nvarchar(MAX)
SELECT @sql = N'INSERT ' + @tableName + ' VALUES (...)'

EXEC (@sql)

The easiest way to achieve your data routing is to iterate your staging data with a cursor and create the dynamic insert sql string in that loop.

Afterwards execute the insert batch with a single EXEC command.

The table T is similar to your table, I populated it with test data close to yours, the tables you populate will be created if they don't exist.

An attempt to recreate your table

CREATE TABLE T(name varchar(10), date datetime)

insert t values('RecordA','2011-08-30')
insert t values('RecordB','2011-08-31')
insert t values('RecordC','2011-09-01')
insert t values('RecordD','2011-09-02')

This syntax will if needed create and populate tables like TABLE_YYYY_MM. YYYY and MM is any combination found it table T

Declare @tablename varchar(64)
Declare @sql as varchar(max)
Declare @d as datetime
Declare dCursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT dateadd(month, datediff(m, 0, date), 0) date from t
OPEN dCursor
FETCH NEXT FROM dCursor
INTO @d
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tablename = '[TABLE_'+replace(CONVERT(VARCHAR(7), @d, 121), '-', '_') + ']'
SET @SQL = 
'if OBJECT_ID('''+@tablename+''', ''U'') is null 
BEGIN
Declare @sql2 varchar(max)
SET @sql2 = ''SELECT * INTO '+@tablename+'
FROM t WHERE 1 = 2''
EXEC(@sql2)
END
INSERT INTO '+ @tablename+'
SELECT * FROM t
WHERE datediff(m, 0, date)=' + CAST(datediff(m, 0, @d) AS VARCHAR(10))

EXEC(@SQL)
FETCH NEXT FROM dCursor
INTO @d

END
CLOSE dCursor
DEALLOCATE dCursor