MSSQL存储过程生成insert into 语句
2020-06-11 本文已影响0人
_意义
USE [数据库]
GO
/****** Object: StoredProcedure [dbo].[sp_get_InsertSql] Script Date: 2020/5/20 17:13:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <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 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' no exist！ ' )
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' , '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 '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='''' 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 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 ( @SQL+ @SQLIdentityOn + @cols+ @colsData + @SQLIdentityOff)
FETCH NEXT FROM tab_cur INTO @tabName, @SQLWhere
END
CLOSE tab_cur
DEALLOCATE tab_cur
SELECT insert_sql FROM @tb
END
GO
用法: tabList以,分隔。
@GetMapping( "/getInsert")
public ReturnResult getInsertSql(String tabList) {
ReturnResult retVal = new ReturnResult();
try {
List<?> resultList = jdbcTemplate.execute(new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) throws SQLException {
String storedProc = "{call sp_get_InsertSql(?)}";
CallableStatement cs = con.prepareCall(storedProc);
cs.setString(1, tabList);
return cs;
}
}, cs-> {
List<String> list = new ArrayList<>();
try (ResultSet rs = cs.executeQuery()) {
ResultSetMetaData rmd = rs.getMetaData();
int columnCount = rmd.getColumnCount();
while (rs.next()) {
list.add("" + rs.getObject(columnCount));
}
}
return list;
});
retVal.setResult(resultList);
return retVal;
} catch (Exception e) {
retVal.setMessage(e.getMessage());
retVal.setFlag(false);
retVal.setErrorCode(ViBorderUtil.getErrorCode(ErrorCode.E250001));
return retVal;
}
}
