动态表插入 TSQL [英] 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 read 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

推荐答案

表 T 与您的表相似，我填充了与您的表相近的测试数据，如果您填充的表不存在，则会创建它们.

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.

尝试重新创建您的表

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')

如果需要，此语法将创建和填充表，如 TABLE_YYYY_MM.YYYY和MM是任意组合找到它表T

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

这篇关于动态表插入 TSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！