1、创建存储过程
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 BIGINT IDENTITY ( 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) AND is_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 snEND 2、执行存储过程
EXECUTE [dv_etc].[dbo].[sp_get_InsertSql] @dbName='dv_etc' --数据库名称 ,@tabList='etc_city' --表名称 ,@IncludeIdentity=0 ,@DeleteOldData=0