(Transfer) Generate the stored procedure of the INSERT statement according to the data in the table

xiaoxiao2021-03-06  22

Create Proc SpgeninSertsql (@tablename varchar (256))

AS

Begin

Declare @SQL VARCHAR (8000)

Declare @sqlvalues ​​varchar (8000)

Set @SQL = '('

Set @sqlvalues ​​= 'VALUES (' ' '

SELECT @Sqlvalues ​​= @Sqlvalues ​​ COLS ' ' ',' ' ', @ SQL = @SQL '[' Name '],'

From

(SELECT CASE

WHEN Xtype in (48, 52, 56, 59, 60, 62, 104, 106, 108, 122, 127)

THEN 'CASE WHEN' NAME 'is NULL TEN' 'NULL' 'ELSE' 'CAST (' Name 'As Varchar)' 'end'

When Xtype in (58, 61)

THEN 'CASE WHEN' NAME 'IS NULL THEN' '' '' 'ELSE' '' '' '' '' ' ' CAST (' Name ' As Varchar) ' ' '' '' '' ' ' end '

WHEN Xtype in (167)

THEN 'CASE WHEN' NAME 'is NULL THEN' '' '' '' ELSE ' ' '' '' '' ' ' Replace (' Name ', '' '' ',' '' '' '' '' '' ' ' '' '' '' ' ' End '

WHEN Xtype in (231)

THEN 'CASE WHEN' NAME 'Is Null TEN' 'NULL' 'ELSE' '' '' '' '' ' ' 'Replace (' Name ',' '' '', '' '' '' '' '' ' ' '' '' '' 'end'when xtype in (175)

THEN 'CASE WHEN' NAME 'is NULL THEN' '' '' '' ELSE ' ' '' '' '' ' ' Cast (Replace (' Name ', '' '' '" , '' '' '' '' '') AS Char (' Cast As Varchar) ') '' '' '' '' 'End'

When Xtype in (239)

THEN 'CASE WHEN' NAME 'IS NULL THEN' 'NULL' 'ELSE' '' '' '' '' ' ' 'CAST (Replace (' Name ',' '' '" ',' '' '' '' '') AS Char (' CAST (Length AS VARCHAR) ') '' '' '' '' 'End'

Else '' 'Null' ''

End as cols, name

From syscolumns

Where id = Object_id (@tablename)

T

Set @SQL = 'SELECT' '' INSERT INTO [' @tablename '] ip (@ SQL, LEN (@SQL) -1) ')' Left (@ Sqlvalues, Len (@sqlvalues) -4 ) ')' 'from' @ TableName

--Print @SQL

EXEC (@SQL)

end

Go

转载请注明原文地址:https://www.9cbs.com/read-39701.html

New Post(0)