Stored procedure enhanced version _mssql to generate INSERT statements in batches of data in a table or table

Sometimes, we need to migrate the data from a table to a different Couchen of the same structure by exporting it all or based on query criteria

Currently, there is no relevant tool in SQL Server to generate INSERT statements based on query criteria, only with the help of Third-party tools (third party tools)

There are a lot of such scripts on the Web, but the web script still lacks some specifications and functions, such as: I just want to export the data of the specific query criteria, the online script is to export the full table data

If the table is large, it will have a significant effect on performance

Here is a stored procedure (applicable to SQLServer2005 or above)

--Author: < Birch >--Blog:  

Here's a statement that if you have multiple schemas and you have the same table under each schema, the script will only generate the table insert script under one of the schemas

For example, I now have three schemas, and below are the customer this table

CREATE TABLE dbo. [Customer] (City int,region int)
Create SCHEMA test
create TABLE test.[ Customer] (city int,region int)
CREATE SCHEMA test1

When executing the script, he will only generate the table insert script below the DBO schema

This script has a flaw

Regardless of the data type of the field of your table, the export can only be a character

Table structure

The insert script that came out

I'll show you how to use it.

There are two different ways

1, guide the whole table data

Or

2, according to the query conditions guide data

Or

Click, select All

and then copy

Create a new query window, and then paste

In fact, there are a lot of SQL Server skills

Finally, we can look at the code, very simple, if you want to support SQLServer2000, just change the code on it

Add: Create a Test table

CREATE TABLE testinsert (id int,name VARCHAR (MB), Cash money,dtime DATETIME)
INSERT into [dbo].[  Testinsert]
([id], [name], [cash], [dtime])
VALUES (1,--id-int
' Nihao ',--Name-varchar (m)
8.8, --Cash-money
GETDATE ()--dtime-datetime
)

Test

Insertgenerator ' Testinsert ', '
insertgenerator ' Testinsert ', ' name= ' ' Nihao '

There are some problems with DateTime types

The resulting results will automatically help you convert

--------------------------------------------------------------------------------

Another script shared by people in the group

IF object_id (' spgeninsertsql ', ' P ') is not NULL DROP PROC spgeninsertsql go CREATE PROC spgeninsertsql (@tablename varcha R (256), @number BIGINT, @whereClause NVARCHAR (MAX)) as BEGIN declare @sql varchar (8000) DECLARE @sqlValues varchar (8000) SE T @sql = ' (' Set @sqlValues = ' VALUES (' + ' Select @sqlValues = @sqlValues + cols + ' + ', ' + ', @sql = @sql + ' [' + Nam E + '], ' from (Select Case if xtype in (48,52,56,59,60,62,104,106,108,122,127) Then ' case ' + name + ' is NULL then ' null ' else ' + ' cast (' + name + ' as varchar ') ' + ' when xtype in (58,61,40,41,42) Then ' case ' + name + ' is NULL  Then ' ' NULL ' else ' + ' + ' + ' + ' + '-' + ' + ' as varchar ' + ' + ' ' as ' + ' + ' ', ' ' + ', ' ' + ' ' when xtype in (167) Then ' case ' + name + ' is null then ' ' null ' else ' + ' ' + ' ' + ' + ' replace (' + name+ ', ' ' ', ', ', ' ', ' ', ' ', ' ' ' + ' ', ' ' ' Hen xtype in (231) then "case when" + name + ' is null then ' ' null ' ' Else ' + ' ' + ' ' + ' + ' replace (' + name+ ', ' ' ', ' ', ' ', ' ', ' ' '''''''''')' + '+''''''''' + ' ' when xtype in (175) Then ' case ' + name + ' is null then ' null ' ' Else ' + ' ' + ' + ' + ' cast (replace (' + name + ', ' ', ' ', ' ' ' ' as Char (' + cast (length as varchar) + ')] + ' + ' ' ' ' + ' ' when xtype in (239) Then ' case ' + name + ' is null then ' null ' else ' + ' ' N ' ' ' + ' + ' cast (replace (' + name+ ', ' ', ', ', ' ', ' ', ' ', ', ' 
Ngth as varchar) + ') + ' ' ' ' ' + ' end ' Else ' ' NULL ' ' End as Cols,name from syscolumns where id = object_id (@tablename)  ) T IF (@number!=0 and @number is not NULL) BEGIN set @sql = ' Select top ' + CAST (@number as VARCHAR (6000)) + ' "INSERT into [' + @tablename + '] ' + left (@sql, Len (@sql)-1) + ' "+ Left (@sqlValues, Len (@sqlValues)-4) +") ' from ' + @tablename print @s QL End ELSE BEGIN Set @sql = ' SELECT ' inserts into [' + @tablename + '] ' + left (@sql, Len (@sql)-1) + ') ' + left (@sqlValues, le  N (@sqlValues)-4) + ' "from" + @tablename print @sql end print @whereClause IF (@whereClause are not NULL and @whereClause <> ') BEGIN set@sql = @sql + ' where ' + @whereClause print @sql end exec (@sql) End Go  

Call Example

--Non-dbo default schemas need to be aware of-
-support data types: bigint,int, bit,char,datetime,date,time,decimal,money, nvarchar (m), tinyint, nvarchar ( MAX), varchar (max), datetime2
-invoke example if the top row or the Where condition is empty, simply fill in the parameter
with null Spgeninsertsql ' customer '--table name
, 2-- Top rows
, ' city=3 and didian= ' Dalian '--where condition
--exports the entire table where condition is empty
spgeninsertsql ' customer '--table name
, null--T Number of OP lines
, null--where condition
INSERT into [Department] ([departmentid],[name],[groupname],[company],[modifieddate] ) VALUES (1,n ' aftermarket, n ' Sales group ', n ' China Hello Co., Ltd. XX branch ', ' 5 2015 5:58pm ')

The above is the article for you to share the table and table data batch generated INSERT statement of the stored procedures enhanced version, I hope you like.