Solved

Insert from a comma separated list (CSV)

Posted on 2014-11-14
7
87 Views
Last Modified: 2014-11-14
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
Comment
Question by:Andy Green
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40442287
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
 
LVL 3

Author Comment

by:Andy Green
ID: 40442299
Sorry my mistake - its not a file its a text string passed into a stored proc.

Andy
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40442336
Can you give an example of how are those records?
0
Industry Leaders: 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!

 
LVL 3

Author Comment

by:Andy Green
ID: 40442366
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
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40442399
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
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 40442525
Very cool. Thank you.

Andy
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 40442718
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

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question