In MS SQL Server, exporting data can only use several formats specified by Microsoft-MS, but cannot be exported as a standard INSERT statement, which is very bad because there will often be such a need.
If you want to automatically block the automatic growth column, you need to add the condition of Autoval Is Null when Query System Table Syscolumns, see the red part of the following.
Original posted URL: http://www.im286.com/viewthread.php? TID = 1195089
[SQLServer] Exporting the data in Table is an Insert statement whether it is online or usually testing, this stored procedure is now given. Create Proc dbo.proc_insert (@tablename varchar (256), @ colid INT = 1) / * Enter: (must) Table name, varchar (optional) starts from the first few fields (such as ID self-increased) The field does not need explicit insert, int output: generated insert statement, one per line. Explanation: Do not use in the query analyzer, otherwise only the truncated statement is obtained.
(With winsql avoid) * / asbeginset nocount ondeclare @sqlstr varchar (8000) declare @ sqlstr1 varchar (8000) declare @ sqlstr2 varchar (8000) select @ sqlstr = 'select' 'insert' @ tablenameselect @ sqlstr1 = '' select @ SQLSTR2 = '(' select @ SQLSTR1 = 'VALUES (' ' ' select @ SQLSTR1 = @ SQLSTR1 COL ' ' ',' ' ', @ SQLSTR2 = @ SQLSTR2 Name ',' from (SELECT CASE - - when a.xtype = 173 THEN 'CASE WHEN' '[' a.name ']' 'Is Null Then' 'Null' 'Else' 'Convert (VARCHAR (VARCHAR (4), A.LENGTH * 2 2) '),' '[' a.name ']' ')' 'end'when a.xtype = 104 THEN' CASE WHEN ' ' [' a. Name ']' 'is Null Then' '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 Ten' 'Null' 'Else' '' '' '
'' ' ' 'Convert (VARCHAR (23),' '[' A.NAME ']' ', 121)' ' ' '' '' '' 'end'when a .XTYPE = 106 THEN 'CASE WHEN' '[' A.NAME ']' 'Is Null Ten' 'Null' 'Else' 'Convert (Varchar (VARCHAR (4), A.XPREC 2) '),' '[' a.name ']' ')' 'end'when a.xtype = 62 TEN' 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 TEN' CASE WHEN ' ' [' A.NAME '] ' ' Is Null Ten '' Null '' Else ' ' Convert (Varchar (22), ' '[ a.name '] ')' 'end'when a.xtype = 239 TEN' CASE WHEN ' ' [' A.NAME '] ' ' Is Null Ten '' Null '' Else ' ' '' '' '' ' ' Replace (' ' [' a.name '] ',' '' '' ',' '' '' '' '' '') ' ' '' '' '
'' 'end'when a.xtype = 108 THEN' 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 Then' 'Null' 'Else' 'Convert (VARCHAR (23),' '[' a.name '] ', 2) ' end'when a.xtype = 58 TEN' CASE WHEN ' ' [ A.NAME ']' 'IS NULL THEN' '' '' '' '' '' '' ' ' 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 Ten' 'Null' 'Else' 'Convert (Varchar (22),' '[' A .name '] ') ' ' end'when a.xtype = 48 TEN 'CASE WHEN' '[' a.Name '] ' is null dam '' Null '' Else ' ' Convert (VARCHAR (6), ' ' [' a.name '] ')' 'end' - when a.xtype = 165 TEN 'CASE WHEN' '[' a.name ']' 'Is Null Then' 'Null' Else ' ' Convert (VARCHAR (VARCHAR (4), A.Length * 2 2) '),' '[' a.name '] ' ') ' ' end'when a.xtype = 167 THEN 'CASE WHEN' '[' A.NAME ']' 'IS NULL TEN' '' '' 'ELSE' '' '' '' '' ' ' 'REPLACE (' '[' a.name '] ', '' '' '' ',' '' '' '' '' ')' ' '' '' '' '' 'end' 'End As Col, a.colid,' [' a.name '] 'as namefrom syscolumn a where a.colid> = @ Colid and a.id = Object_id (@tablename) and a.xtype <> 189 and a.xtype <> 34 and a.xtype <> 35 and a.xtype <> 36 and a.autoval is null) T Order by ColidSelect @ Sqlstr = @ SQLSTR LEFT (@ sqlstr2, len (@ SQLSTR2) -1) ')'
