• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 102
  • Last Modified:

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
0
Andy Green
Asked:
Andy Green
  • 3
  • 3
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
 
Andy GreenAuthor Commented:
Sorry my mistake - its not a file its a text string passed into a stored proc.

Andy
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you give an example of how are those records?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Andy GreenAuthor Commented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, I understood now.
You need to use Common Table Expression (CTE) for do the recursive split:
DECLARE @BookingID as int = 1
DECLARE @UserID as nvarchar(200) = 'ab,cd,ef'

;WITH SplitString(StartPos,EndPos)
    AS(
        SELECT 0, CHARINDEX(',',@UserID)
        UNION ALL
        SELECT EndPos+1, CHARINDEX(',',@UserID,EndPos+1)
        FROM SplitString
        WHERE EndPos > 0
    )
SELECT @BookingID Booking, SUBSTRING(@UserID,StartPos,COALESCE(NULLIF(EndPos,0),LEN(@UserID)+1)-StartPos) UserID
FROM SplitString

Open in new window

0
 
Andy GreenAuthor Commented:
Very cool. Thank you.

Andy
0
 
Valliappan ANSenior Tech ConsultantCommented:
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now