Shulou(Shulou.com)05/31 Report--

This article introduces you how to achieve SQL Server data row bulk insertion script storage, the content is very detailed, interested friends can refer to, hope to be helpful to you.

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

IF OBJECT_ID (Numbdbo.uspspeak GetInsertSQLstores,'P') IS NOT NULL BEGIN DROP PROCEDURE dbo.usp_GetInsertSQL END GO-- =-- function: get the SQL script for inserting data table records-- description: specific implementation description-- author: XXX-- create: yyyy-MM-dd-- modify: yyyy-MM-dd XXX modify content description-- = = CREATE PROCEDURE dbo.usp_GetInsertSQL (@ chvnTable NVARCHAR (),-- data table name is recommended. Do not have the delimiter [] @ chvnWhere NVARCHAR () = INSERT INTO VALUES,-- where query condition (without the WHERE keyword) @ bitIsSingleRow BIT =-- whether it is in single-line mode, which defaults to single-line mode (single-line INSERT INTO VALUES format) Non-single-line mode (multiline mode) is in multiline INSERT INTO SELECT format)-- $Encode$-- AS BEGIN SET NOCOUNT ON; SET @ bitIsSingleRow = ISNULL (@ bitIsSingleRow,); DECLARE @ intTableID AS INT, @ chvnSchemaTableName NVARCHAR (); / * format: [schema]. [table]-+ (the number of characters corresponding to each part) * / SELECT @ intTableID =, @ chvnSchemaTableName = Numeric' SELECT @ intTableID = object_id, @ chvnSchemaTableName = QUOTENAME (SCHEMA_NAME (schema_id)) + Number.' + QUOTENAME (@ chvnTable) / * join of combined schema name and table name * / FROM sys.objects WHERE name = @ chvnTable AND type = 'Utility; DECLARE @ chvnColumnNames NVARCHAR (),-- Field column name set, separated by commas',', format such as [column_name], [column_name],... @ chvnColumnValues AS NVARCHAR (MAX) -- Field column value sets, separated by a comma',', separated by DECLARE @ chvnTSQL AS NVARCHAR (MAX),-- TSQL script variable @ chvnInsertIntoBoday AS NVARCHAR ();-- InsertInto body variable SELECT @ chvnTSQL = unknown, @ chvnInsertIntoBoday = invalid' SELECT @ chvnColumnNames = ISNULL (@ chvnColumnNames + Numeric') + QUOTENAME (T.column_name), @ chvnColumnValues = ISNULL (@ chvnColumnValues + 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 types with precision and decimals (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 date, datetime and time added by sql server) * / 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''N' + 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''N' + CAST (REPLACE ('+ name +',' ') AS CHAR (' + CAST (max_length AS VARCHAR) +') + 'END' ELSE' NULL''' END FROM sys.columns WHERE object_id = @ intTableID) AST SET @ chvnInsertIntoBoday = N'''INSERT INTO'+ @ chvnSchemaTableName + N' ('+ @ chvnColumnNames + N')'' First, the code format uses 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-to deal with multiline mode, you need to use the ROW_NUMBER window function-- SET @ chvnTSQL = N'SELECT CASE WHEN T.rownum = THEN REPLICATE (nasty'', LEN (N''UNION ALL'') +) + T.RowData ELSE N''UNION ALL''+ T.RowData END' +-- N' FROM ('+ @ chvnTSQL + N') AS checking;-- SET @ chvnTSQL = N'SELECT'+ @ chvnInsertIntoBoday + N' '+-- @ chvnTSQL;-- GOTO MultiRow;-- END-- ELSE IF @ bitIsSingleRow = / * current line mode * /-- BEGIN-- SET @ chvnTSQL = N'SELECT' + @ chvnInsertIntoBoday +-- N' + 'VALUES (''+'+ @ chvnColumnValues +'+'); 'FROM' + @ chvnSchemaTableName;-- GOTO WhereCondition -- END-where query criteria-- WhereCondition:-- IF @ chvnWhere >''--BEGIN-- SET @ chvnTSQL = @ chvnTSQL + 'WHERE' + @ chvnWhere -- END-- Label null tag of MultiRow:/* multiline mode GOTO * /-- END-- method 2. Redundant BEGIN IF @ bitIsSingleRow = / * multiline mode * / BEGIN SET @ chvnTSQL = N'SELECT''SELECT' +'+ @ chvnColumnValues +'AS RowData with partial code ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) AS RowNum FROM'+ @ chvnSchemaTableName IF @ chvnWhere > 'BEGIN SET @ chvnTSQL = @ chvnTSQL +' WHERE'+ @ chvnWhere END-multiline mode special code, you need to use the ROW_NUMBER window function SET @ chvnTSQL = N'SELECT CASE WHEN T.rownum = THEN REPLICATE (N''UNION ALL'', LEN (N''UNION ALL'') +) + T.RowData ELSE N''UNION ALL''+ T.RowData END' + N' FROM ('+ @ chvnTSQL + N') AS T'; SET @ chvnTSQL = N'SELECT'+ @ chvnInsertIntoBoday + nasty'+ @ chvnTSQL END ELSE IF @ bitIsSingleRow = / * single-line mode * / BEGIN SET @ chvnTSQL = N'SELECT'+ @ chvnInsertIntoBoday + N' + 'VALUES (''+'+ @ chvnColumnValues +'+');''FROM' + @ chvnSchemaTableName; IF @ chvnWhere >''BEGIN SET @ chvnTSQL = @ chvnTSQL +' WHERE'+ @ chvnWhere; END PRINT @ chvnTSQL; EXEC (@ chvnTSQL); END GO

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

IF OBJECT_ID (ID INT IDENTITY (,) PRIMARY KEY, Name VARCHAR () NOT NULL, LoginTime DATETIME NOT NULL) IS NOT NULL BEGIN DROP TABLE dbo.UserLoginInfo; END GO-- create testing table UserLoginInfo CREATE TABLE dbo.UserLoginInfo 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','--::'), ('wang','--::'), ('zhang','--:') ('li','--:'), ('wang','--::'), ('zhang','--::'), ('li','--::'), ('wang','--::'), ('zhang','--::'), ('li','--::'), ('wang','--:') GO first tests the effect of the single-line mode. The corresponding T-SQL code is as follows: EXEC dbo.usp_GetInsertSQL @ chvnTable = Native UserLoginInfotainment,-- nvarchar () @ chvnWhere = Numeric,-- nvarchar () @ bitIsSingleRow =;-- bit GO

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

EXEC dbo.usp_GetInsertSQL @ chvnTable = Noble UserLoginInfotainment,-- nvarchar () @ chvnWhere = Nabilistic,-- nvarchar () @ bitIsSingleRow =;-- bit GO's storage of how to implement bulk data row insertion scripts in SQL Server is shared here. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.