动态T-SQL存储过程插入不同的表 [英] Dynamic T-SQL stored procedure to insert in differents tables
问题描述
实现此目标的最佳方式是
What is the best way to achieve this
INSERT INTO @TableName (@ColumnNames)
EXEC sp_executesql @SQLResult;
其中 @TableName
, @ColumnNames
, @SQLResult
是 varchar
变量
我试图避免为每个表单独插入。
I am trying to avoid do a separate insert for each table.
推荐答案
首先我感谢您的所有评论。而且我同意SQL动态是一个痛苦的调试(感谢上帝,管理工作室有这种可能性)。而且,当然还有数百种不同的解决方案
First of all I appreciate all your comments. And I agree that SQL dynamic is a pain to debug (Thanks God, management studio has this possibility). And, of course there are hundreds of different solutions
我以这种方式终于解决了,或多或少我试图解释为什么这个SQL解决方案的动态。客户端使用xlsx电子表格输入某些数据,因此我读取电子表格并插入(数据取决于电子表格插入正确的表格)。稍后,表格中的数据将导出为XML,以发送第三方软件。
I solved it in this way finally, more or less I try to explain why this solution of SQL dynamic. The client uses xlsx spreadsheets to enter certain data, so I read the spreadsheets and I insert the (data depends on the spreadsheet to insert into the proper table). Later the data in the tables are exported to XML to send a third party sofware.
SET @SEL = N'';
DECLARE sel_cursor CURSOR
FOR (SELECT sc.name as field
FROM sys.objects so INNER JOIN sys.columns sc ON so.[object_id]=sc.[object_id]
WHERE so.name= @TableName and sc.name not in ('InitDate', 'EndDate', 'Version', 'Status'));
SET @SEL = ''; set @i = 0;
OPEN sel_cursor
FETCH NEXT FROM sel_cursor INTO @field
WHILE @@FETCH_STATUS = 0
BEGIN
set @sel = @sel + ', '+ @field
set @i = 1;
FETCH NEXT FROM sel_cursor INTO @field
END
CLOSE sel_cursor;
DEALLOCATE sel_cursor;
SET @SQL = N''
SET @SQL = @SQL + N'SELECT * INTO XLImport FROM OPENROWSET'
SET @SQL = @SQL + N'('
SET @SQL = @SQL + N'''Microsoft.ACE.OLEDB.12.0'''+','
SET @SQL = @SQL + N'''Excel 12.0 Xml; HDR=YES;'
SET @SQL = @SQL + N'Database='+@file +''''+ ','
SET @SQL = @SQL + N'''select * from ['+ @SheetName + '$]'''+');'
EXEC sp_executesql @SQL
SET @SQL = N'';
SET @SQL = @SQL + N'
SELECT '+''''+CAST(@initDate AS VARCHAR(10))+'''' +', '+ ''''+CAST(@endDate AS VARCHAR(10))+''''
+ ', '+ CAST(@version AS VARCHAR(2)) +', ' +''''+@status+''''
+ @SEL
+' FROM DBO.XLImport '
DECLARE cols_cursor CURSOR
FOR (Select COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS where table_name = @tableName);
SET @SEL = ''; set @i = 0;
OPEN cols_cursor
FETCH NEXT FROM cols_cursor INTO @field
WHILE @@FETCH_STATUS = 0
BEGIN
set @sel = @sel + @field + ', '
set @i = 1;
FETCH NEXT FROM cols_cursor INTO @field
END
CLOSE cols_cursor;
DEALLOCATE cols_cursor;
SET @SEL = LEFT(@SEL, LEN(@SEL) - 1) -- remove last ,
SET @SQL = N''
SET @SQL = @SQL + N'SELECT * INTO XLImport FROM OPENROWSET'
SET @SQL = @SQL + N'('
SET @SQL = @SQL + N'''Microsoft.ACE.OLEDB.12.0'''+','
SET @SQL = @SQL + N'''Excel 12.0 Xml; HDR=YES;'
SET @SQL = @SQL + N'Database='+@file +''''+ ','
SET @SQL = @SQL + N'''select * from ['+ @SheetName + '$]'''+');'
EXEC sp_executesql @SQL
SET @SQLString =
N'INSERT INTO '+ @TableName + '('+ @SEL +') ' + @SQL;
EXEC sp_executesql @SQLString
这篇关于动态T-SQL存储过程插入不同的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!