Storage implementation of data row BULK INSERT scripts in SQL Server _mssql

Accidentally saw a friend wrote an article "The Table data batch generation INSERT statement implementation of the stored procedure." I look at the text of the two storage code, I feel that two are not satisfied, are generated by the single mode of insertion, the data row slightly larger performance will be affected. A similar implementation of a second version exists in the company, but it is based on multiline mode, or it needs to be manually added unaion all to accommodate multiple-line inserts. See this blog post and the shortcomings of the storage based on the company data row bulk script, this time rewriting and enhancing the functionality of the storage.

This store runs in SQL Server 2005 or later, and 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: Get data table record Insert SQL Script--Description: Concrete implementation Elaboration--author: XXX--Create: YYYY-MM-DD--fix Modified: YYYY-MM-DD XXX Modify Content Description--================================== CREATE PROCEDURE dbo.usp_getinsertsql (@chvnTable NVA     Rchar (),--Data table name (recommended only use table name, do not have delimiter []) @chvnWhere NVARCHAR () = N ',--where query condition (without the WHERE keyword) @bitIsSingleRow BIT = --Single-line mode, the default is Single-line mode (single-line mode is a single-line insert into values format; non-single-line mode (multiline mode) for multiple-line insert into select format)--$Encode $--as BEGIN SET NOC
  Ount on;
  SET @bitIsSingleRow = ISNULL (@bitIsSingleRow,); DECLARE @intTableID as INT, @chvnSchemaTableName NVARCHAR ();/* Format: [schema].
  [table]--++++++ (each part corresponds to the number of characters)/SELECT @intTableID =, @chvnSchemaTableName = N '; SELECT @intTableID = object_id, @chvnSchemaTableName = QuoteName (schema_name (schema_id)) + N '. ' + QuoteName (@chvnTab Le)/* Joins the combination schema name and table name/from sys.objects WHERE name = @chvnTable and type = ' U ';  DECLARE @chvnColumnNames NVARCHAR (),--field column name set, multiple comma ', ' delimited, format such as: [Column_name],[column_name],... @chvnColumnValues as    NVARCHAR (MAX);   --Field column value set, multiple comma ', ' delimited DECLARE @chvnTSQL as NVARCHAR (MAX)--TSQL script variable @chvnInsertIntoBoday as NVARCHAR ();
  --insertinto main variable SELECT @chvnTSQL = N ', @chvnInsertIntoBoday = n '; SELECT @chvnColumnNames = ISNULL (@chvnColumnNames + n ', ', n ') + QuoteName (t.column_name), @chvnColumnValues = Isnul L (@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, TI Nyint, smallint, int, bigint), data types with precision and decimal numbers (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 type: Dates Time, SmalldatetIME (SQL Server new Date, DateTime, and Time)/THEN ' case when ' + name + ' is null THEN ' ' null ' ' ELSE ' ' "' + Replac
        E (CONVERT (VARCHAR (), ' + name + ',), '::. ', ', ' ') + ' ', ' ' ', ' ', ' ', ' ', ' system_type_id in ()/* String data type: varchar*/ THEN ' case when ' + name + ' is null THEN ' ' null ' ' ELSE ' ' ' + ' ' + ', ' ' ', ' ', ' ' ', ' ' ' ' '  ' "' When system_type_id in ()/*unicode string data type: nvarchar*/THEN ' case when ' + name + ' is NULL THEN ' NULL ' ELSE ' N ' ' + REPLACE (' + name + ', ' ' ', ', ', ', ' ', ' ', ' ', ' ', ' ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', system_type_id String data type: char*/THEN ' case when ' + name + ' is null THEN ' ' null ' ' ELSE ' ' ' + ' + CAST (REPLACE (' + name + ', ' ' ') ' ', ' ', ', ', ' as CHAR (' + CAST (max_length as VARCHAR) + ')] + ', ' ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ' String data type: nchar*/THEN ' case when ' + name + ' is null THEN ' ' null ' ELSE ' ' N ' ' ' + CAST (REPLACE (' + name + ', ' ' "') ' ', ', ' ', ', ' as CHAR (' + CAST (max_length as VARCHAR) + ')) + ' ' ' ' ' ' End ' ELSE ' ' NULL ' ' "', '" ', ', '
  D = @intTableID) as T; 
  SET @chvnInsertIntoBoday = N ' ' INSERT into ' + @chvnSchemaTableName + n ' (' + @chvnColumnNames + n ') '; --mode One, code format using 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 ' + @chvnSchemaTableNa Me----you cannot use goto wherecondition here, because the following code will not be executed-IF @chvnWhere > '------the SET @chvnTSQL = @chvn
  TSQL + ' WHERE ' + @chvnWhere; --end----handle multi-line mode, need to use row_number window function--SET @chvnTSQL = N ' SELECT case t.rownum = THEN REPLICATE (n ' "', LE
  N (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 line 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 + ' WH
  ERE ' + @chvnWhere; 
    --end--multirow:/* multi-line mode goto label empty tag/--end-mode two, the existence of partial code redundancy 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 IF @chvnWhere > ' BEGIN SET @chvnTSQL = @chvnTSQL + ' WHERE '
    + @chvnWhere; End-Multi-line mode special code, need to use the Row_number window function SET @chvnTSQL = N ' SELECT case t.rownum = THEN REPLICATE (n ' "', LEN (n ') ' UN ION 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 mode/BEGIN SET @chvnTSQL = n ' SELECT ' + @chvnInsertIntoBoday + n ' + ' ' VALUES (' + ' + @chvnColumnValues + ' + ');
    From ' + @chvnSchemaTableName;
    IF @chvnWhere > ' BEGIN SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere;  
  End End PRINT @chvnTSQL;
 EXEC (@chvnTSQL);  End Go

To test the effect of the above storage, prepare a data table with data, 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 ', '--:: '), (' Li ', '--:: '), (' Wang ', '--:: '), (' Zhang ', '--:: '), ( ' Li ', '--:: ', (' Wang ', '--:: '), (' Zhang ', '--:: '), (' Li ', '--:: '), (' Wang ', '--:: '), (' Zhang ', '--:: '), (' Li ' , '--:: '), (' Wang ', '--:: '), (' Zhang ', '--:: ', (' Li ', '--:: '), (' Li ', '--:: '), (' Li ', '--:: '), (' Li ', '--: '), ' ), (' Li ', '--:: '), (' Li ', '--:: '), (' Li ', '--:: '), (' Li ', '--:: '), (' Wang ', '-:: '), (' Zhang ', '--:: '), (' Li ' , '--:: '), (' Wang ', '--:: '), (' Zhang ', '--:: '), (' Li ', '--:: '), (' Wang ', '--:: '), (' Zhang ', '--:: '), (' Li ', '-
 -:: '), (' Wang ', '--:: '); Go first test the effect of 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 results of the executed query are as follows:

To test the effect of multiple-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 results of the query after execution are as follows:

Note: Multi-line mode, you need to combine the above two results before and after a file can be.

The above content is small to share in the SQL Server data row Bulk Insert script storage implementation, I hope you like.