Solved

Insert from a comma separated list (CSV)

Posted on 2014-11-14
7
79 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
  • 3
  • 3
7 Comments
 
LVL 46

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 46

Expert Comment

by:Vitor Montalvão
ID: 40442336
Can you give an example of how are those records?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 46

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now