SQL Server export Insert INSERT statement

SQL Server is a bit difficult, and the tools that come with it do not work. The verified data can be done by writing a stored procedure.

The stored procedures are as follows:

SET quoted_identifier OFF Goset ANSI_NULLS on goif exists (select 1 from sysobjects where id=object_id (' Bicashyoutputdata ') and xtype= ' P ') drop procedure bicashyoutputdata; Gocreate procedure dbo. Bicashyoutputdata (@tablename varchar, @whereStr varchar) as DECLARE @column varchar (DECLARE) @columndata varchar (DECLARE) @sql varchar (4000) DECLARE @xtype tinyint declare @name sysname DECLARE @objectId int DECLARE @ ObjectName sysname declare @ident int SET NOCOUNT ON set @objectId =object_id (@tablename) If @objectId is null--the judgment is No presence begin print ' The object Not EXISTS ' return end set @objectname =rtrim (object_name (@objectId)) if @objectname is n Ull or Charindex (@objectname, @tablename) =0--This judgment is not strict at Begin print ' object not in current database ' return End If object Property (@objectId, ' istable ') < > 1--The judgment is whether the table begin print ' The object is not table ' return End Select @id ent=status&0x80 from syscolumns where [email protected] and Status&0x80=0x80 If @ident is not a null print ' SET identity_insert ' [email protected]+ ' on ' Declare SYSCOLUMNS_CU Rsor cursorfor Select C.name,c.xtype from syscolumns c where [email protected] ORDER by c.colid Open syscolumns_curs or set @column = ' Set @columndata = ' fetch NEXT from Syscolumns_cursor to @name, @xtype while @ @fetch_status < ; -1 BEGIN IF @ @fetch_status < >-2 begin if @xtype not in (189,34,35,99,98)--timestamp no processing required, Image,text,ntext,sql_v Ariant temporarily does not process begin set @[email protected]+case when Len (@column) =0 then ' Else ', ' [email protected] set @[ema Il protected]+case when Len (@columndata) =0 then ' Else ', ' ', ' ', ' end of +case when @xtype in (167,175) and then ' "' + ' [ email protected]+ ' + ' "--varchar,char when @xtype" and "N" "" + "+" [email protected]+ ' + ') "" "--nvarchar,nchar when @xtype =61 and then" "" "+convert (char), ' [email protected]+ ', 121 ' + '" '--"-- datetime when @xtype =58 """'" ' +convert (char), ' [email protected]+ ', ' + + ') + ' "'" '--smalldatetime when @xtype =36 then "" + Convert (char), ' [email protected]+ ') + ' "'--uniqueidentifier else @name end end Fetch NEXT from Syscol  Umns_cursor to @name, @xtype end close Syscolumns_cursor deallocate syscolumns_cursor Set @sql = ' Set NOCOUNT ON select ' Insert ' [email protected]+ ' (' [email protected]+ ') VALUES (' ' as '--', ' [email protected]+ ', ' '); From ' [email protected]+ ' [email protected] print '--' [email protected] EXEC (@sql) If @ident are not nul L print ' SET identity_insert ' [email protected]+ ' off ' goset quoted_identifier off Goset ANSI_NULLS on GO

After you have established the stored procedure, you can call the stored procedure to view the generated INSERT INSERT statement.

Specific operation:

Execute exec bicashyoutputdata cardinfo, ' where drawoutper= ' Li Peijuan '

Note that Cardinfo is the name of the table to be executed, ' where drawoutper= ' Li Peijuan "' is the Where condition and can be written directly as: exec bicashyoutputdata cardinfo, if you do not want all the query conditions to be exported,"

It is also important to note that if a query condition contains a string, it needs to be preceded by a string ""

Finally, all the generated records can be copied out.

SQL Server export Insert INSERT statement