sql server 2005生成insert语句，同时完成多表(Bug：插入语句列列表最后一列列名后还带有,)
Posted on 2012-02-27 14:54 X龙 阅读(1519) 评论(0) 编辑 收藏 举报-- =======================================================================================
/*
Example:
EXEC sp_get_InsertSql @dbName='',
@tabList='tb1 where id=0,
tb2 WHERE id=0,
tb3, tb4',
@IncludeIdentity=1,
@DeleteOldData=0
*/
-- =======================================================================================
CREATE PROC [dbo]. [sp_get_InsertSql]
@dbName VARCHAR ( 32)= '' , -- 数据库名称
@tabList VARCHAR ( max ), -- 要导出数据的表名，表名之间用逗号隔开，过滤条件跟在表名后面，用空格隔开如 tab1 where col1!=2, tab2, tab3
@IncludeIdentity BIT = 1, -- 是否包含自增字段
@DeleteOldData BIT = 1 -- 插入前删除所有数据
AS
DECLARE
@index INT ,
@wi INT ,
@SQL VARCHAR ( max ),
@SQL1 VARCHAR ( max ),
@tabName VARCHAR ( 128),
@colName VARCHAR ( 128),
@colType VARCHAR ( 128),
@tabPrefix VARCHAR ( 32),
@cols VARCHAR ( max ),
@colsData VARCHAR ( max ),
@SQLWhere VARCHAR ( 1024),
@SQLIdentityOn VARCHAR ( MAX ),
@SQLIdentityOff VARCHAR ( MAX ),
@SQLDelete VARCHAR ( max ),
@SQLIfBegin VARCHAR ( 1024),
@SQLIfEnd VARCHAR ( 1024),
@SQLNull VARCHAR ( 1024);
DECLARE @t_tb TABLE ( TB varchar ( 128), Sqlwhere varchar ( 1024), SN BIGINTIDENTITY ( 1, 1))
DECLARE @tb TABLE ( insert_sql VARCHAR ( max ), SN BIGINT IDENTITY ( 1, 1));
DECLARE @colList TABLE ( colName VARCHAR ( 128), colType VARCHAR ( 128),
colValueL VARCHAR ( 120), colValueR VARCHAR ( 120), selColName VARCHAR ( 128));
BEGIN
SET NOCOUNT ON
SET @tabList = REPLACE ( @tabList, CHAR ( 9), '' )
SET @tabList = REPLACE ( @tabList, CHAR ( 10), '' )
SET @tabList = REPLACE ( @tabList, CHAR ( 13), '' )
SET @dbName = LTRIM ( RTRIM ( @dbName))
SET @index = CHARINDEX ( ',' , @tabList)
IF LEN ( @dbName) > 0
SET @tabPrefix = @dbName + '..'
ELSE
SET @tabPrefix = '' ;
WHILE @index > 0 AND @index IS NOT NULL
BEGIN
SET @tabName = SUBSTRING ( @tabList, 1, @index- 1)
SET @wi= CHARINDEX ( ' where' , LTRIM ( @tabName))
IF @wi= 0
SET @wi = LEN ( @tabName)
INSERT INTO @t_tb( tb, Sqlwhere) VALUES ( SUBSTRING ( @tabName, 1, @wi),SUBSTRING ( @tabName, @wi+ 1, LEN ( @tabName)- @wi))
SET @tabList = SUBSTRING ( @tabList, @index+ 1, LEN ( @tabList)- @index)
SET @index = CHARINDEX ( ',' , @tabList)
END
IF @index = 0 OR @index IS NULL
SET @tabName = @tabList
ELSE
SET @tabName = SUBSTRING ( @tabList, 1, @index)
SET @wi= CHARINDEX ( ' where' , LTRIM ( @tabName))
IF @wi= 0
SET @wi = LEN ( @tabName)
INSERT INTO @t_tb( tb, Sqlwhere) VALUES ( SUBSTRING ( @tabName, 1, @wi), SUBSTRING( @tabName, @wi+ 1, LEN ( @tabName)- @wi))
SELECT @SQL1 = 'select INSERT_SQL='';SET NOCOUNT ON' + CHAR ( 13) + '''' +
' union all '
SELECT @SQLNull = 'select INSERT_SQL='' '' union all ' ,
@SQLIfBegin = 'select INSERT_SQL='' If @Error=0 begin ''' +
' union all ' ,
@SQLIfEnd = ' union all ' + 'select INSERT_SQL='' end;'''
DECLARE tab_cur CURSOR FOR
SELECT t. name , tb. Sqlwhere FROM sys.tables t
INNER JOIN @t_tb tb ON t. name = RTRIM ( LTRIM ( tb. TB))
ORDER BY tb. SN
OPEN tab_cur
FETCH NEXT FROM tab_cur INTO @tabName, @SQLWhere
WHILE @@FETCH_STATUS = 0 BEGIN
DELETE FROM @colList
IF NOT EXISTS( SELECT 1 FROM sys.objects WHERE name = @tabName AND type = 'U' )BEGIN
PRINT ( @tabName + N' 不存在！ ' )
RAISERROR ( @tabName, 16, - 1);
FETCH NEXT FROM tab_cur INTO @tabName, @SQLWhere
CONTINUE ;
END
INSERT INTO @colList( colName, colType, colValueL, colValueR)
SELECT c. NAME , t. name , '' , ''
FROM sys.columns c
INNER JOIN sys.tables tab
ON c. object_id = tab. object_id
INNER JOIN sys.types t
ON c. user_type_id = t. user_type_id
WHERE c. is_computed= 0
AND tab. name = @tabName
IF @IncludeIdentity= 0
DELETE FROM @colList WHERE colName IN(
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID ( @tabName) ANDis_identity= 1)
UPDATE @colList SET colValueL= 'RTRIM(' , colValueR = ')'
WHERE colType IN( 'text' , 'varchar' , 'nvarchar' , 'char' , 'uniqueidentifier', 'datetime' , 'nchar' , 'sysname' )
SELECT @cols= '' , @colsData = '' , @SQL = '' ;
UPDATE @colList SET colName = '[' + colName + ']'
UPDATE @colList SET selColName= colName
UPDATE @colList SET colValueL= 'replace(' + colValueL, colValueR = colValueR+','''''''','''''''''''')'
WHERE colType IN( 'text' , 'varchar' , 'nvarchar' , 'char' , 'nchar' ,'sysname' )
UPDATE @colList SET colValueL=
CASE WHEN colType IN( 'text' , 'varchar' , 'nvarchar' , 'char' ,'uniqueidentifier' , 'datetime' , 'nchar' , 'sysname' ) THEN '''''''''+' ELSE '' END
+ colValueL,
colValueR = colValueR + CASE WHEN colType IN( 'text' , 'varchar' ,'nvarchar' , 'char' , 'nchar' , 'datetime' , 'uniqueidentifier' , 'sysname' ) THEN'+''''''''' ELSE '' END
SELECT @cols = @cols + colName + ', ' ,
@colsData = @colsData + 'isnull(' +
colValueL +
CASE WHEN colType= 'datetime' THEN 'convert(varchar(20),' + colName+',120)'
WHEN colType= 'uniqueidentifier' THEN 'convert(varchar(50),' + colName+')'
WHEN colType= 'text' THEN 'convert(nvarchar(max),' + colName+ ')'
WHEN colType= 'sysname' THEN 'convert(nvarchar(max),' + colName+ ')'
WHEN colType= 'varbinary' OR colType= 'BINARY' OR colType= 'image'
THEN 'master.dbo.fn_varbintohexsubstring(1,' + colName+ ',1,0)'
ELSE 'cast(' + colName+ ' as nvarchar(max))' END
+ colValueR + ',''null'')+'', ''+'
FROM @colList
SELECT @cols = LEFT( @cols, LEN ( @cols)- 1),
@colsData = LEFT( @colsData, LEN ( @colsData)- 5),
@SQL = 'select INSERT_SQL=''print ''''Table Name: ' + CHAR ( 9)+@tabName + '''''''' +
' union all '
SELECT @cols = 'select INSERT_SQL=''INSERT INTO ' + @tabPrefix + @tabName + '('+ @cols+ ')' ,
@colsData = ' VALUES(''+' + @colsData + '+'');'' FROM ' + @tabPrefix +@tabName
SELECT @colsData = @colsData + ' ' + ISNULL ( @SQLWhere, '' )
IF @DeleteOldData= 1
SET @SQLDelete = 'select INSERT_SQL='''' +
''Delete from ' + @tabPrefix + @tabName + '; ''' +
' union all '
ELSE
SET @SQLDelete= ''
IF @IncludeIdentity= 1 AND EXISTS( SELECT 1 FROM sys.columns WHERE object_id =OBJECT_ID ( @tabName) AND is_identity= 1)
BEGIN
SELECT @SQLIdentityOn = 'select INSERT_SQL=''SET IDENTITY_INSERT ' +@tabPrefix + @tabName + ' ON;''' +
' union all ' ,
@SQLIdentityOff = ' union all ' + 'select INSERT_SQL=''SET IDENTITY_INSERT ' + @tabPrefix + @tabName + ' OFF;'''
END
ELSE
BEGIN
SELECT @SQLIdentityOff = '' ,
@SQLIdentityOn = '' ;
END
INSERT INTO @tb( insert_sql)
EXECUTE ( @SQLNull + @SQLIfBegin + @SQL+ @SQLDelete+ @SQLIdentityOn + @cols+@colsData + @SQLIdentityOff + @SQLIfEnd)
FETCH NEXT FROM tab_cur INTO @tabName, @SQLWhere
END
CLOSE tab_cur
DEALLOCATE tab_cur
SELECT insert_sql FROM @tb ORDER BY sn
END
