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

Create Proc Proc_Insert (@tablename varchar (256))

AS

Begin

Set nocount on

Declare @sqlstr varchar (4000)

Declare @ SQLSTR1 VARCHAR (4000)

Declare @ SQLSTR2 VARCHAR (4000)

SELECT @ SQLSTR = 'SELECT' 'INSERT' @ TableName

SELECT @ SQLSTR1 = ''

SELECT @ SQLSTR2 = '('

SELECT @ SQLSTR1 = 'VALUES (' ' '

SELECT @ SQLSTR1 = @ SQLSTR1 COL ' ' ',' ' ', @ SQLSTR2 = @ SQLSTR2 Name ',' from (SELECT CASE

--Hen a.xtype = 173 TEN 'CASE WHEN' A.NAME 'IS NULL TEN' 'NULL' 'ELSE' 'Convert (Varchar (VARCHAR (4), A.LENGTH * 2 2 ) '),' a.name ')' 'end'

When a.xtype = 104 THEN 'CASE WHEN' A.NAME 'IS NULL TEN' 'NULL' 'ELSE' 'Convert (varchar (1),' a.name ')' 'end'

When a.XTYPE = 175 THEN 'CASE WHEN' A.NAME 'IS NULL THEN' '' '' 'ELSE' '' '' '' ' ' 'Replace (' a.name ',' '' '' '' ',' '' '' '' '' '' ' ' '' '' ' ' End '

When a.xtype = 61 THEN 'CASE WHEN' A.NAME 'IS NULL THEN' '' '' 'ELSE' '' '' '' ' ' 'Convert (Varchar (23),' A .name ', 121)' ' ' '' '' 'end'when a.xtype = 106 TEN' CASE WHEN ' A.NAME ' Is Null Ten '' Null '' Else ' 'Convert (VARCHART (VARCHAR (VARCHAR (4), A.XPREC 2) '), ' A.NAME ') ' ' end '

When a.xtype = 62 THEN 'CASE WHEN' A.NAME 'IS NULL THEN' 'NULL' 'ELSE' 'Convert (varchar (23),' A.NAME ', 2)' 'end'

When a.XTYPE = 56 THEN 'CASE WHEN' A.NAME 'Is Null Ten' 'Null' 'Else' 'Convert (varchar (11),' a.name ')' 'end'

When a.XTYPE = 60 THEN 'CASE WHEN' A.NAME 'IS NULL TEN' 'NULL' 'ELSE' 'Convert (varchar (22),' a.name ')' End '

When a.xtype = 239 THEN 'CASE WHEN' A.NAME 'IS NULL TEN' '' '' 'ELSE' '' '' '' '' ' ' Replace (' a.name ', ' '' '' '' ',' '' '' '' '' '' ' ' '' '' ' ' End '

When a.xtype = 108 TEN 'CASE WHEN' A.NAME 'Is Null Ten' 'Null' 'Else' 'Convert (Varchar (' Convert (varchar (4), A.XPREC 2) ') , ' a.name ') ' ' end'when a.xtype = 231 TEN 'CASE WHEN' A.NAME 'IS NULL THEN' '' '' 'ELSE' '' '' '' '' '' ' ' Replace (' a.name ', '' '' '' ',' '' '' '' '') ' ' '' '' '' '' ' END '

When a.xtype = 59 THEN 'CASE WHEN' A.NAME 'Is Null Ten' 'Null' 'Else' 'Convert (varchar (23),' A.NAME ', 2)' 'End'

When a.xtype = 58 TEN 'CASE WHEN' A.NAME 'Is Null Ten' '' '' 'Else' '' '' '' ' ' 'Convert (Varchar (23),' A .name ', 121)' '' '' '' '' 'end'

When a.XTYPE = 52 THEN 'CASE WHEN' A.NAME 'Is Null Ten' 'Null' 'Else' 'Convert (varchar (12),' A.NAME ')' 'end'

When a.xtype = 122 THEN 'CASE WHEN' A.NAME 'Is Null Then' 'Null' 'Else' 'Convert (varchar (22),' A.NAME ')' 'end'

When a.XType = 48 TEN 'CASE WHEN' A.NAME 'Is Null Ten' 'Null' 'Else' 'Convert (varchar (6),' a.name ')' 'end' - When a.Xtype = 165 THEN 'CASE WHEN' A.NAME 'Is Null Ten' 'Null' 'Else' 'Convert (VARCHAR (VARCHAR (VARCHAR (4), A.LENGTH * 2 2) '),' a.name ')' 'end'

When a.XTYPE = 167 THEN 'CASE WHEN' A.NAME 'Is Null Then' '' '' 'Else' '' '' '' ' ' Replace (' a.name ', ' '' '' '' ',' '' '' '' '' '' ' ' '' '' ' ' End '

Else '' 'Null' ''

End as col, a.colid, a.name

From syscolumns a where a.id = Object_id (@tablename) and a.XTYPE <> 189 and A.XTYPE <> 34 and A.XTYPE <> 35 and A.XTYPE <> 36

) T order by colid

SELECT @ SQLSTR = @ SQLSTR LEFT (@ sqlstr2, len (@ SQLSTR2) -1) ')' Left (@ sqlstr1, len (@ SQLSTR1) -3) ')' from ' @ TableName

- Print @sqlstr

EXEC (@SQLSTR)

Set nocount off

end

Go