2023年7月13日发(作者：)

MSSQL存储过程⽣成insertinto语句USE [数据库]GO/****** Object: StoredProcedure [dbo].[sp_get_InsertSql] Script Date: 2020/5/20 17:13:49 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: -- Create date: -- Description: -- =============================================CREATE PROCEDURE [dbo].[sp_get_InsertSql] --@dbName VARCHAR ( 32)= '' , -- 数据库名称 @tabList VARCHAR ( max )-- 要导出数据的表名，表名之间⽤逗号隔开AS DECLARE @dbName VARCHAR ( 32), @IncludeIdentity BIT = 1, @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 );

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' + ' union all ' DECLARE tab_cur CURSOR FOR SELECT t. name , tb. Sqlwhere FROM 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 s WHERE name = @tabName AND type = 'U' ) BEGIN PRINT ( @tabName + N' no exist！ ' ) RAISERROR ( @tabName, 16, - 1); 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 s c INNER JOIN tab ON c. object_id = tab. object_id INNER JOIN 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 s WHERE object_id = OBJECT_ID ( @tabName) AND is_identity= 1)

UPDATE @colList SET colValueL= 'RTRIM(' , colValueR = ')' WHERE colType IN( 'text' , 'varchar' , 'nvarchar' , 'char' , 'uniqueidentifier' , 'datetime2' , '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' , 'datetime2' , 'nchar' , 'sysname' ) THEN '''''''''+' ELSE '' END + colValueL, colValueR = colValueR + CASE WHEN colType IN( 'text' , 'varchar' , 'nvarchar' , 'char' , 'nchar' , 'datetime2' , 'uniqueidentifier' , 'sysname' ) THEN '+''''''''' ELSE '' END SELECT @cols = @cols + colName + ', ' , @colsData = @colsData + 'isnull(' + colValueL +

CASE WHEN colType= 'datetime2' 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 '_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='''' union all ' SELECT @cols = 'select INSERT_SQL=''INSERT INTO ' + @tabPrefix + @tabName + '(' + @cols+ ')' , @colsData = ' VALUES(''+' + @colsData + '+'');'' FROM ' + @tabPrefix + @tabName SELECT @colsData = @colsData + ' ' + ISNULL ( @SQLWhere, '' )

IF @IncludeIdentity= 1 AND EXISTS( SELECT 1 FROM s 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 ( @SQL+ @SQLIdentityOn + @cols+ @colsData + @SQLIdentityOff) FETCH NEXT FROM tab_cur INTO @tabName, @SQLWhere END CLOSE tab_cur DEALLOCATE tab_cur SELECT insert_sql FROM @tbENDGO⽤法: tabList以,分隔。 @GetMapping( "/getInsert") public ReturnResult getInsertSql(String tabList) { ReturnResult retVal = new ReturnResult(); try { List resultList = e(new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection con) throws SQLException { String storedProc = "{call sp_get_InsertSql(?)}"; CallableStatement cs = eCall(storedProc); ing(1, tabList); return cs; } }, cs-> { List list = new ArrayList<>(); try (ResultSet rs = eQuery()) { ResultSetMetaData rmd = aData(); int columnCount = umnCount(); while (()) { ("" + ect(columnCount)); } }

return list; }); ult(resultList); return retVal; } catch (Exception e) { sage(sage()); g(false); orCode(orCode(ErrorCode.E250001)); return retVal; }}