?
Solved

Insert from a comma separated list (CSV)

Posted on 2014-11-14
7
Medium Priority
?
99 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 51

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 51

Expert Comment

by:Vitor Montalvão
ID: 40442336
Can you give an example of how are those records?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 51

Accepted Solution

by:
Vitor Montalvão earned 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

771 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