1. cursor Mode
CopyCode The Code is 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 into @ 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 into @ ID
Fetch next from dataitem_cursor into @ name
Close dataitem_cursor
Deallocate dataitem_cursor
/*
Perform logical processing, insert, or update operations here...
Www.jb51.net
*/
End
Close data_cursor
Deallocate data_cursor
2. While Mode Copy codeThe Code is as follows: declare @ data nvarchar (max)
Set @ DATA = 'tanw, keenboy '-- id, name
Declare @ temp table
(
Id int identity (1, 1 ),
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 @ Results = @ results + @ name + ','
Www.jb51.net
/*
Perform logical processing, insert, or update operations here...
*/
End
Select @ results
For a simple single table batch insert operation, the above method is not required
Author keenboy