利用存储过程将表中的数据生成Insert语句
2024-10-19 02:19:17
1、创建存储过程
CREATE PROC [dbo].[sp_get_InsertSql] @dbName VARCHAR ( )= '' , -- 数据库名称 @tabList VARCHAR ( max ), -- 要导出数据的表名,表名之间用逗号隔开,过滤条件跟在表名后面,用空格隔开如 tab1 where col1!=, tab2, tab3 @IncludeIdentity BIT = , -- 是否包含自增字段 @DeleteOldData BIT = -- 插入前删除所有数据 AS DECLARE @index INT , @wi INT , @SQL VARCHAR ( max ), @SQL1 VARCHAR ( max ), @tabName VARCHAR ( ), @colName VARCHAR ( ), @colType VARCHAR ( ), @tabPrefix VARCHAR ( ), @cols VARCHAR ( max ), @colsData VARCHAR ( max ), @SQLWhere VARCHAR ( ), @SQLIdentityOn VARCHAR ( MAX ), @SQLIdentityOff VARCHAR ( MAX ), @SQLDelete VARCHAR ( max ), @SQLIfBegin VARCHAR ( ), @SQLIfEnd VARCHAR ( ), @SQLNull VARCHAR ( ); DECLARE @t_tb TABLE ( TB varchar ( ), Sqlwhere varchar ( ), SN BIGINT IDENTITY ( , )) DECLARE @tb TABLE ( insert_sql VARCHAR ( max ), SN BIGINT IDENTITY ( , )); DECLARE @colList TABLE ( colName VARCHAR ( ), colType VARCHAR ( ), colValueL VARCHAR ( ), colValueR VARCHAR ( ), selColName VARCHAR ( )); BEGIN SET NOCOUNT ON SET @tabList = REPLACE ( @tabList, CHAR ( ), '' ) SET @tabList = REPLACE ( @tabList, CHAR ( ), '' ) SET @tabList = REPLACE ( @tabList, CHAR ( ), '' ) SET @dbName = LTRIM ( RTRIM ( @dbName)) SET @index = CHARINDEX ( ',' , @tabList) IF LEN ( @dbName) > SET @tabPrefix = @dbName + '..' ELSE SET @tabPrefix = '' ; WHILE @index > AND @index IS NOT NULL BEGIN SET @tabName = SUBSTRING ( @tabList, , @index- ) SET @wi= CHARINDEX ( ' where' , LTRIM ( @tabName)) IF @wi= SET @wi = LEN ( @tabName) INSERT INTO @t_tb( tb, Sqlwhere) VALUES ( SUBSTRING ( @tabName, , @wi), SUBSTRING ( @tabName, @wi+ , LEN ( @tabName)- @wi)) SET @tabList = SUBSTRING ( @tabList, @index+ , LEN ( @tabList)- @index) SET @index = CHARINDEX ( ',' , @tabList) END IF @index = OR @index IS NULL SET @tabName = @tabList ELSE SET @tabName = SUBSTRING ( @tabList, , @index) SET @wi= CHARINDEX ( ' where' , LTRIM ( @tabName)) IF @wi= SET @wi = LEN ( @tabName) INSERT INTO @t_tb( tb, Sqlwhere) VALUES ( SUBSTRING ( @tabName, , @wi), SUBSTRING ( @tabName, @wi+ , LEN ( @tabName)- @wi)) SELECT @SQL1 = 'select INSERT_SQL='';SET NOCOUNT ON' + CHAR ( ) + '''' + ' 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 = BEGIN DELETE FROM @colList IF NOT EXISTS( SELECT FROM sys.objects WHERE name = @tabName AND type = 'U' ) BEGIN PRINT ( @tabName + N' 不存在! ' ) RAISERROR ( @tabName, , - ); 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= AND tab. name = @tabName IF @IncludeIdentity= DELETE FROM @colList WHERE colName IN( SELECT name FROM sys.columns WHERE object_id = OBJECT_ID ( @tabName) AND is_identity= ) 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)- ), @colsData = LEFT( @colsData, LEN ( @colsData)- ), @SQL = 'select INSERT_SQL=''print ''''Table Name: ' + CHAR ( )+ @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= SET @SQLDelete = 'select INSERT_SQL='''' + ''Delete from ' + @tabPrefix + @tabName + '; ''' + ' union all ' ELSE SET @SQLDelete= '' IF @IncludeIdentity= AND EXISTS( SELECT FROM sys.columns WHERE object_id = OBJECT_ID ( @tabName) AND is_identity= ) 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
2、执行存储过程
EXECUTE [dv_etc].[dbo].[sp_get_InsertSql]
@dbName='dv_etc' --数据库名称
,@tabList='etc_city' --表名称
,@IncludeIdentity=
,@DeleteOldData=
最新文章
- 微信开发中网页授权access_token与基础支持的access_token异同
- 大型网站seo优化之行业网站seo优化具体操作思路
- CCF 节日
- 【HDU5955】Guessing the Dice Roll/马尔科夫
- ubuntu下安装oracle
- VMware (威睿) 虚拟化产品简介
- TI CC254x BLE教程 4
- python中的urlencode与urldecode
- errno与perror
- JAVA进阶-注解
- HDU - 4994 Revenge of Nim (取石子游戏)
- python 异常 反射
- 20165223《信息安全系统设计基础》第九周学习总结 &; 第八周课上测试
- 团队——League of Programers简介
- pyCharm最新激活码(2018激活码)
- 流媒体压力测试rtmp&;hls(含推流和拉流)
- python安装办法
- 限制 input 输入框只能输入纯数字
- java开发一个应用的总结
- .net如何发送格式化的文本内容
热门文章
- android开发无障碍app
- delphi使用 第三方控件
- 1521. War Games 2(线段树解约瑟夫)
- 1494. Monobilliards(栈)
- poj 1611 The Suspects(简单并查集)
- poj 2503 快排+二分
- 浏览器以外的Javascript
- x86、i386、i486、i586、i686和x86_64
- HDU 5874 Friends and Enemies 【构造】 (2016 ACM/ICPC Asia Regional Dalian Online)
- [Bhatia.Matrix Analysis.Solutions to Exercises and Problems]ExI.5.4