Storage Implementation of batch insert scripts for data rows in SQL Server,

Storage Implementation of batch insert scripts for data rows in SQL Server,

I accidentally saw an article written by a friend, "how to generate the INSERT statement stored procedure in batches based on the data in the table ". I carefully read the two storage codes in this article. I feel that both of them are not satisfied. They are generated in single-row mode insertion, and the performance of data rows is slightly larger. A similar implementation of the second version exists in the company, but it is based on the multiline mode. You still need to manually add unaion all to enable multiline insertion. This blog post shows the disadvantages of storing Batch scripts based on the company's data lines. This time, the storage function is rewritten and enhanced.

This storage runs on SQL Server 2005 or later, and the T-SQL code is as follows:

IF OBJECT_ID (n' dbo. usp_GetInsertSQL ', 'P') is not null begin drop procedure dbo. usp_GetInsertSQL; end go -- ======================================================== -- function: obtain the SQL script for inserting data table records -- Description: Implementation description -- Author: XXX -- create: yyyy-MM-dd -- modify: description of yyyy-MM-dd XXX modification -- ============================== ==== create procedure dbo. usp_GetInsertSQL (@ chvnTable NVARCHAR (), -- data table name (we recommend that you only use the table name without the [] separator) @ chvnWhere NVARCHAR () = n '', -- where query condition (without the WHERE keyword) @ bitIsSingleRow BIT = -- whether to use single row mode. The default value is single row mode (single row mode is in single row insert into values format; non-single-row mode (multi-row mode) -- $ Encode $ -- as begin set nocount on; SET @ bitIsSingleRow = ISNULL (@ bitIsSingleRow ,); DECLARE @ intTableID as int, @ chvnSchemaTableName NVARCHAR ();/* Format: [schema]. [table] -- ++ (number of characters corresponding to each part) */SELECT @ intTableID =, @ chvnSchemaTableName = n''; SELECT @ intTableID = object_id, @ chvnSchemaTableName = QUOTENAME (SCHEMA_NAME (schema_id) + N '. '+ QUOTENAME (@ chvnTable)/* connection between the composite schema name and table name */FROM sys. objects WHERE name = @ chvnTable AND type = 'U'; DECLARE @ chvnColumnNames NVARCHAR (), -- field column name set, separated by commas (,). The format is as follows: [column_name], [column_name],... @ chvnColumnValues as nvarchar (MAX); -- field column value set. Multiple values are separated by commas ), -- TSQL script variable @ chvnInsertIntoBoday as nvarchar (); -- InsertInto subject variable SELECT @ chvnTSQL = n'', @ chvnInsertIntoBoday = n ''; SELECT @ chvnColumnNames = ISNULL (@ chvnColumnNames + N', ', n') + QUOTENAME (T. column_name), @ chvnColumnValues = ISNULL (@ chvnColumnValues + N' + '','' + ', n'') + CAST (T. column_value as nvarchar () FROM (SELECT name AS column_name/* field column name * // * field column value */, column_value = case when system_type_id IN (,)/* numeric data type: integer data type (bit, tinyint, smallint, int, bigint ), data Type with precision and decimal (decimal, numeric) and currency data types (monery and smallmoney */THEN 'case when' + name + 'is null then' 'null' 'else CAST (' + name + 'as varchar) END 'when system_type_id IN (,)/* date and time data types: datetime, smalldatetime (compatible with SQL server's new date, datetime, and time) */THEN 'case when' + name + 'is null then' 'null' 'else' ''' + REPLACE (CONVERT (VARCHAR (), '+ name + ',),''::. ''', ''') + ''' '''''end' WHEN system_type_id IN ()/* string data type: varchar */THEN 'case when' + name + 'is null then' 'null' 'else' ''' + REPLACE (' + name + ', ''', ''') + ''' '''''end' WHEN system_type_id IN () /* Unicode string data type: nvarchar */THEN 'case when' + name + 'is null then' 'null' 'else ''' + REPLACE (' + name + ', ''', ''') + ''' '''''end' WHEN system_type_id IN () /* string data type: char */THEN 'case when' + name + 'is null then' 'null' 'else' ''' + CAST (REPLACE (' + name + ', ''', ''') as char ('+ CAST (max_length as varchar) + ')) + ''' '''end' WHEN system_type_id IN ()/* nicode string data type: nchar */THEN 'case when' + name + 'is null then' 'null' 'else ''' + CAST (REPLACE (' + name +', ''', ''') as char ('+ CAST (max_length as varchar) + ')) + '''''''''end 'else' ''null''' end from sys. columns WHERE object_id = @ intTableID) as t; SET @ chvnInsertIntoBoday = n'''insert into' + @ chvnSchemaTableName + N' ('+ @ chvnColumnNames + N ')'''; -- Method 1: the code format uses the GOTO and Label -- BEGIN -- IF @ bitIsSingleRow =/* multiline mode */-- BEGIN -- SET @ chvnTSQL = n'select' 'select'' + '+ @ chvnColumnValues +' AS RowData, ROW_NUMBER () OVER (order by (select null) AS RowNum FROM '+ @ chvnSchemaTableName -- GOTO WhereCondition cannot be used here ;, because the subsequent code will not be executed -- IF @ chvnWhere> ''-- BEGIN -- SET @ chvnTSQL = @ chvnTSQL + 'where' + @ chvnWhere; -- END -- process multi-row mode, use the ROW_NUMBER Window Function -- SET @ chvnTSQL = n' select case when t. rownum = then replicate (n'''', LEN (n''union all'') + T. rowData ELSE n''union all'' + T. rowData END '+ -- n' FROM (' + @ chvnTSQL + N') AS T'; -- SET @ chvnTSQL = n' select' + @ chvnInsertIntoBoday + N '; '+ -- @ chvnTSQL; -- GOTO MultiRow; -- END -- else if @ bitIsSingleRow =/* When row mode */-- BEGIN -- SET @ chvnTSQL = n'select' + @ chvnInsertIntoBoday + -- n' + ''values ('' + '+ @ chvnColumnValues +' + ''); ''from' + @ chvnSchemaTableName; -- GOTO WhereCondition; -- END -- where query condition -- WhereCondition: -- IF @ chvnWhere> ''-- BEGIN -- SET @ chvnTSQL = @ chvnTSQL + 'where' + @ chvnWhere; -- END -- MultiRow: /* Label empty mark of GOTO in multiline mode */-- END -- method 2. Redundant begin if @ bitIsSingleRow =/* multiline mode */begin set @ chvnTSQL = N 'select' + '+ @ chvnColumnValues +' AS RowData, ROW_NUMBER () OVER (order by (select null) AS RowNum FROM '+ @ chvnSchemaTableName IF @ chvnWhere> ''begin set @ chvnTSQL = @ chvnTSQL + 'where' + @ chvnWhere; END -- special code in multiline mode. You need to use the ROW_NUMBER window function SET @ chvnTSQL = n' select case when t. rownum = then replicate (n'''', LEN (n''union all'') + T. rowData ELSE n''union all'' + T. rowData END '+ N' FROM (' + @ chvnTSQL + N') AS T'; SET @ chvnTSQL = n' select' + @ chvnInsertIntoBoday + N'; '+ @ chvnTSQL; end else if @ bitIsSingleRow =/* single row mode */begin set @ chvnTSQL = n'select' + @ chvnInsertIntoBoday + N' + ''values (''+ '+ @ chvnColumnValues + '+ ''); ''from' + @ chvnSchemaTableName; IF @ chvnWhere> ''in in SET @ chvnTSQL = @ chvnTSQL + 'where' + @ chvnWhere; end print @ chvnTSQL; EXEC (@ chvnTSQL); END GO

To test the effect of the above storage, prepare a data table below, the T-SQL code is as follows:

IF OBJECT_ID (n' dbo. userLoginInfo ', n'u') is not null begin drop table dbo. userLoginInfo; end go -- create testing table UserLoginInfo create table dbo. userLoginInfo (id int identity (,) primary key, Name VARCHAR () not null, LoginTime datetime not null); GO -- insert testing data INSERT dbo. userLoginInfo (Name, LoginTime) VALUES ('zhang ',' --: '), ('lil',' --: '), ('wang ','--:: '), ('zhang', '--:'), ('lil', '--:'), ('wang ','--::'), ('zhang ',' --: '), ('lil',' --: '), ('wang ','--::'), ('zhang ',' --: '), ('lil',' --: '), ('wang ','--::'), ('zhang ',' --: '), ('lil',' --: '), ('lil ','--::'), ('lil ','--::'), ('lil ','--::'), ('wang ',' --: '), ('zhang', '--:'), ('lil ','--::'), ('wang ',' --: '), ('zhang', '--:'), ('lil ','--::'), ('wang ',' --: '), ('zhang', '--:'), ('lil ','--::'), ('wang ',' --: '); GO first test the effect of the single line mode, the corresponding T-SQL code is as follows: EXEC dbo. usp_GetInsertSQL @ chvnTable = n'userlogininfo', -- nvarchar () @ chvnWhere = n', -- nvarchar () @ bitIsSingleRow =; -- bit GO

The query result after execution is as follows:

 

Then test the multi-line mode effect, the corresponding T-SQL code is as follows:

 EXEC dbo.usp_GetInsertSQL  @chvnTable = N'UserLoginInfo',   -- nvarchar()  @chvnWhere = N'',      -- nvarchar()  @bitIsSingleRow = ;     -- bit GO

The query results after execution are as follows:

 

Note: In the multiline mode, you also need to merge the preceding two results into one file.

The above content is the storage implementation of batch insert scripts for data rows in SQL Server. I hope you will like it.

Articles you may be interested in:

• SqlServer2005 automatically backs up and stores stored procedure functions on another computer
• Sqlserver export and insert script code
• Batch insert and update in SQL server (Stored Procedure)
• Use SQL statements in SQL Server to query the names of stored procedures referenced by all other stored procedures.
• Instance for SQL Server to obtain the returned values of Stored Procedures
• Example of If Else usage in SQL Server Stored Procedure
• How to easily debug T-SQL statements and stored procedures in SQL Server 2008
• SQL Server Stored Procedure generation insert statement instance
• SQL Server Stored Procedure syntax
• Analysis on the stored procedure of transactions contained in SQL Server
• SQL Server 2008 Stored Procedure example
• Differences between SQL Server user-defined functions and stored procedures
• SQL Server Stored Procedure calling in Java