auto generate insert script

转载于 2010-11-08 17:13:00 发布 · 50 阅读

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/laoyumi/archive/2010/11/08/1871928.html

本文介绍了一种使用T-SQL动态生成针对特定表的插入语句的方法。通过声明变量和临时表，从系统目录视图中获取字段信息，并构造出完整的插入语句。

DECLARE @TableName VARCHAR(128);
SET @TableName = 'Order_Item'
DECLARE @FieldInfo TABLE(FieldName SYSNAME, TypeName SYSNAME);

INSERT INTO @FieldInfo
SELECT c.[name],t.[name]
FROM sys.[columns] c
JOIN sys.types t ON t.user_type_id = c.user_type_id
WHERE c.[object_id] = OBJECT_ID(@TableName);

DECLARE @sql VARCHAR(MAX);
SET @sql = 'select ''insert into ' + @TableName + '(';

SELECT @sql = @sql + FieldName + ','
FROM @FieldInfo

SET @sql = LEFT(@sql,LEN(@sql) - 1) + ') values('''

SELECT 
	@sql = @sql + '
		+ case when ' + FieldName + ' is null then ''NULL'' else '
	+ CASE WHEN TypeName IN('char', 'varchar') THEN ''''''''' + ' + FieldName + ' + ''''''''' 
	ELSE 'cast(' + FieldName + ' as varchar)'
	END + ' end + '','''
FROM @FieldInfo

SET @sql = LEFT(@sql, LEN(@sql) - 2) + ')'' from ' + @TableName
SELECT @sql