Generate the storage process enhanced version of the INSERT statement in batches from the data in the table,

Generate the storage process enhanced version of the INSERT statement in batches from the data in the table,

Sometimes, we need to export all or all the data in a table to another database with the same structure according to the query conditions.

Currently, SQL Server does not have related tools to generate INSERT statements based on query conditions. Only the third-party tools (third party tools)

There are also a lot of such scripts on the Internet, but the online scripts still lack some specifications and functions. For example, I only want to export data with specific query conditions, and the online scripts all export full table data.

If the table is large, the performance will be greatly affected.

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

-- Author: <Hua Zai> -- Blog: < http://www.cnblogs.com/lyhabc/ > -- Create date: <//> -- Description: <insert script for exporting table data according to the query conditions> -- ==================================== ===================== create procedure InsertGenerator (@ tableName NVARCHAR (MAX ), @ whereClause NVARCHAR (MAX) AS -- Then it should des a cursor to fetch column specific information (column name and the data type thereof) -- from information_schema.columns pseudo entity and loop through for building the INSERT and VALUES clses -- of an insert dml statement. DECLARE @ string NVARCHAR (MAX) -- for storing the first half of INSERT statementDECLARE @ stringData NVARCHAR (MAX) -- for storing the data (VALUES) related statementDECLARE @ dataType NVARCHAR (MAX) -- data types returned for respective columnsDECLARE @ schemaName NVARCHAR (MAX) -- schema name returned from sys. schemasDECLARE @ schemaNameCount int -- shema countDECLARE @ QueryString NVARCHAR (MAX) -- provide for the whole query, set @ QueryString = ''-- if there are multiple schemas, select one schemaSELECT @ schemaNameCount = COUNT (*) FROM sys. tables tINNER JOIN sys. schemas s ON t. schema_id = s. schema_idWHERE t. name = @ tableNameWHILE (@ schemaNameCount>) BEGIN -- if there are multiple schemas, specify select @ schemaName = name from (SELECT ROW_NUMBER () over (order by s. schema_id) RowID, s. nameFROM sys. tables tINNER JOIN sys. schemas s ON t. schema_id = s. schema_idWHERE t. name = @ tableName) as vwhere RowID = @ schemaNameCount -- Declare a cursor to retrieve column specific information -- for the specified tableDECLARE cursCol CURSOR FAST_FORWARDFORSELECT column_name, data_typeFROM information_schema.columnsWHERE table_name = @ tableNameAND table_schema = @ schemaNameOPEN cursColSET @ string = 'insert INTO ['+ @ schemaName +']. ['+ @ tableName +'] ('set @ stringData = ''DECLARE @ colName NVARCHAR () fetch next from cursCol INTO @ colName, @ dataTypePRINT @ schemaNamePRINT @ colNameIF @ fetch_status <> BEGINPRINT 'table' + @ tableName + 'not found, processing skipped. 'Close curscolDEALLOCATE curscolRETURNENDWHILE @ FETCH_STATUS = BEGINIF @ dataType IN ('varchar ', 'Char', 'nchar ', 'nvarchar ') BEGINSET @ stringData = @ stringData + ''' + isnull ('+ @ colName +', ''') + '''''', ''+ 'endelseif @ dataType IN ('text', 'ntext ') -- if the datatype -- is text or something else BEGINSET @ stringData = @ stringData + ''' + isnull (cast ('+ @ colName +' as nvarchar (max )), ''') + '''''', ''+ 'endelseif @ ype = 'money' -- because money doesn' t get converted -- from varchar implicitlyBEGINSET @ stringData = @ stringData + ''' convert (money, ''''' + isnull (cast ('+ @ colName +' as nvarchar (max )),''. '') + '''),'' + 'endelseif @ dataType = 'datetime' ininset @ stringData = @ stringData + ''' convert (datetime, ''''' + isnull (cast ('+ @ colName +' as nvarchar (max), ''') + ''''''), ''+ 'endelseif @ dataType = 'image' BEGINSET @ stringData = @ stringData + ''' + isnull (cast (convert (varbinary, '+ @ colName +') as varchar (), ''') + ''', ''+ 'endelse -- presuming the data type is int, bit, numeric, decimal BEGINSET @ stringData = @ stringData + ''' + isnull (cast ('+ @ colName +' as nvarchar (max )), '''') + ''''', ''+ 'endset @ string = @ string + '[' + @ colName + ']' + ', 'fetch next from cursCol INTO @ colName, @ dataTypeEND -- After both of the clures are built, the VALUES clause contains a trailing comma which needs to be replaced with a single quote. the prefixed clause will only face removal of the trailing comma. DECLARE @ Query NVARCHAR (MAX) -- provide for the whole query, -- you may increase the sizePRINT @ whereClauseIF (@ whereClause is not nulland @ whereClause <> '') begin set @ query = 'select' + SUBSTRING (@ string, LEN (@ string) + ') VALUES (''+' + SUBSTRING (@ stringData ,, LEN (@ stringData)-) + ''' + '') ''' FROM '+ @ schemaName + '. '+ @ tableName + 'where' + @ whereClausePRINT @ query -- EXEC sp_executesql @ query -- load and run the built query -- Eventually, close and de-allocate the cursor created for columns information. endelsebegin set @ query = 'select' + SUBSTRING (@ string, LEN (@ string) + ') VALUES (''+' + SUBSTRING (@ stringData ,, LEN (@ stringData)-) + ''' + '') ''' FROM '+ @ schemaName + '. '+ @ tableNameENDCLOSE cursColDEALLOCATE cursColSET @ schemaNameCount = @ schemaNameCount-IF (@ schemaNameCount =) BEGINSET @ QueryString = @ QueryString + @ queryENDELSEBEGINSET @ QueryString = @ QueryString + @ query + 'Union all' ENDPRINT convert (varchar (max), @ schemaNameCount) + '---' + @ QueryStringENDEXEC sp_executesql @ QueryString -- load and run the built query -- Eventually, close and de-allocate the cursor created for columns information.

Here we should declare that if you have multiple Schemas and each schema has the same table, the script will only generate the insert script for the table under one of the schemas.

For example, I have three schemas, and the table "customer" is shown below.

CREATE TABLE dbo.[customer](city int,region int)CREATE SCHEMA testCREATE TABLE test.[customer](city int,region int)CREATE SCHEMA test1CREATE TABLE test1.[customer](city int,region int) 

When executing the script, he will only generate the insert script for the table under the dbo schema.

INSERT INTO [dbo].[customer]([city],[region]) VALUES('1','2') 

This script has a defect

No matter what data type the field in your table is, it can only be a character when exported.

Table Structure

CREATE TABLE [dbo].[customer](city int,region int) 

Exported insert script

INSERT INTO [dbo].[customer]([city],[region]) VALUES('1','2') 

Here I will demonstrate how to use

There are two methods

1. Guide full table data

InsertGenerator 'customer', null 

Or

InsertGenerator 'customer', ' ' 

2. Export data based on query Conditions

InsertGenerator 'customer', 'city=3' 

Or

InsertGenerator 'customer', 'city=3 and region=8' 

Click here and select all

Then copy

Create a query window and paste

In fact, there are many SQL Server skills

Finally, let's take a look at the code, which is very simple. If you want to support SQLServer2000, you just need to change the code.

Supplement: Create a test table

CREATE TABLE testinsert (id INT,name VARCHAR(100),cash MONEY,dtime DATETIME)INSERT INTO [dbo].[testinsert]( [id], [name], [cash], [dtime] )VALUES ( 1, -- id - int'nihao', -- name - varchar(100)8.8, -- cash - moneyGETDATE() -- dtime - datetime)SELECT * FROM [dbo].[testinsert] 

Test

InsertGenerator 'testinsert' ,''InsertGenerator 'testinsert' ,'name=''nihao'''InsertGenerator 'testinsert' ,'name=''nihao'' and cash=8.8' 

The datetime type has some problems.

The generated results are automatically converted for you.

INSERT INTO [dbo].[testinsert]([id],[name],[cash],[dtime]) VALUES('1','nihao',convert(money,'8.80'),convert(datetime,'02 8 2015 5:17PM')) 

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

Another script shared by people in the group

IF OBJECT_ID('spGenInsertSQL','P') IS NOT NULL DROP PROC spGenInsertSQLGOCREATE proc spGenInsertSQL (@tablename varchar(256),@number BIGINT,@whereClause NVARCHAR(MAX))asbegindeclare @sql varchar(8000)declare @sqlValues varchar(8000)set @sql =' ('set @sqlValues = 'values (''+'select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'from(select casewhen xtype in (48,52,56,59,60,62,104,106,108,122,127) then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'when xtype in (58,61,40,41,42)then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'when xtype in (167)then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'when xtype in (231)then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'when xtype in (175)then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'when xtype in (239)then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'else '''NULL'''end as Cols,namefrom syscolumns where id = object_id(@tablename)) TIF (@number!=0 AND @number IS NOT NULL)BEGINset @sql ='select top '+ CAST(@number AS VARCHAR(6000))+' ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablenameprint @sqlENDELSEBEGIN set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablenameprint @sqlENDPRINT @whereClauseIF ( @whereClause IS NOT NULL AND @whereClause <> '')BEGINset @sql =@sql+' where '+@whereClauseprint @sqlENDexec (@sql)endGO 

Call example

-- Pay attention to non-dbo default Architecture -- supported data types: bigint, int, bit, char, datetime, date, time, decimal, money, nvarchar (50), tinyint, nvarchar (max), varchar (max), datetime2 -- call example if the top row or where condition is null, you only need to fill in the parameter nullspGenInsertSQL 'custom' -- table name, 2 -- top row, 'city = 3 and didian = ''dalian ''' -- where condition -- export full table where condition is null spGenInsertSQL 'custom' -- table name, null -- top row, null -- where condition insert into [Department] ([partition mentid], [Name], [GroupName], [Company], [ModifiedDate]) values (1, N 'aftersales Department ', N 'sales group', N 'Hello China Company XX branch', '05 5 2015 PM ') insert into [Department] ([partition mentid], [Name], [GroupName], [Company], [ModifiedDate]) values (2, n'aftersales Department ', n'sales group ', N 'Hello China Co., Ltd. XX branch ', '05 5 2015 PM ')

The above is an enhanced version of the stored procedure for generating INSERT statements in batches based on the data in the table.

Articles you may be interested in:

• Use bulk insert to import large batches of data to SQLSERVER
• SQL Server Stored Procedure generation insert statement instance
• Oracle + mybatis uses dynamic SQL to implement batch insert when the inserted fields are uncertain