Bulk INSERT and update solution Sharing in SQL Server (stored procedure)

Transfer from http://www.shangxueba.com/jingyan/1940447.html

1. Cursor mode

Copy the Code code as follows:

DECLARE @Data NVARCHAR (max)

SET @Data = ' 1,tanw,2,keenboy '--id,name

DECLARE @dataItem NVARCHAR (100)

DECLARE data_cursor Cursor for (SELECT * from Split (@Data, '; '))

OPEN Data_cursor

FETCH NEXT from Data_cursor to @dataItem

While @ @FETCH_STATUS =0

BEGIN

DECLARE @Id INT

DECLARE @Name NVARCHAR (50)

DECLARE dataitem_cursor Cursor for (SELECT * from Split (@dataItem, ', '))

OPEN Dataitem_cursor

FETCH NEXT from Dataitem_cursor to @Id

FETCH NEXT from Dataitem_cursor to @Name

CLOSE Dataitem_cursor

Deallocate dataitem_cursor

/*

Here to do the logical processing, insert or update operation ...

Www.jb51.net

*/

END

CLOSE Data_cursor

Deallocate data_cursor

2.While Mode

Copy the Code code as follows:

DECLARE @Data NVARCHAR (max)

SET @Data = ' Tanw,keenboy '--id,name

DECLARE @Temp TABLE

(

Id INT IDENTITY (+),

Name NVARCHAR (50)

)

DECLARE @Id INT

DECLARE @Name NVARCHAR (50)

DECLARE @Results NVARCHAR (MAX) SET @Results = "

INSERT into @Temp Select (SELECT * from Split (@Data, '; '))

While EXISTS (SELECT * from @Temp)

BEGIN

SELECT TOP 1 @Id =id, @Name =name from @Temp

DELETE from @Temp where [id] = @Id

SET @[email protected][email protected]+ ', '

Www.jb51.net

/*

Here to do the logical processing, insert or update operation ...

*/

END

SELECT @Results

If it is a simple single-table bulk insert operation, the above method is not necessary

Bulk INSERT and update solution Sharing in SQL Server (stored procedure)