asked on

Insert from a comma separated list (CSV)

Hi

I have a table with 2 columns UserID & BookingID.

I'll know the bookingID and a comma separated list of UserID's.

How would I insert the CSV list with a common BookingID, witout a cursor.

Andy

Vitor Montalvão

With BULK INSERT command. Here's an example:

BULK INSERT TableName
   FROM Filename -- Provide a string with full path to the filename
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      );

Open in new window

Andy Green

ASKER

Sorry my mistake - its not a file its a text string passed into a stored proc.

Andy

Vitor Montalvão

Can you give an example of how are those records?

Andy Green

ASKER

Table
BookingID UserID
1 ab
1 cd
1 ef
2 ab
2 xy

This is how the table looks the userID are in fact GUIDS

Passed into the proc for the first 3 in the table:

@BookingID as int = 1
@UserID as nvarchar(200) = 'ab,cd,ef'

Andy

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andy Green

ASKER

Very cool. Thank you.

Andy

Valliappan AN

You can also use XML and Cross apply as follows:

DECLARE @BookingID int = 1
DECLARE @UserID nvarchar(200) = 'ab,cd,ef'

SELECT @BookingID As BookingID,
Split.a.value('.', 'VARCHAR(100)') AS UserID
FROM (SELECT
CAST ('<U>' + REPLACE(@UserID, ',', '</U><U>') + '</U>' AS XML) AS String
) AS A CROSS APPLY String.nodes ('/U') AS Split(a);

HTH