asked on

#TempTable from comma separated textdata

I want to declare a list:

DECLARE @myList nvarchar(max) = '1,2,3~4,5,6';

Do cross appply (or similar) to get:

col 1, col 2, col 3
1 2 3
4 5 6

How would you do that?

/BK

lcohan

Below is an example similar to your request but from a csv file which is essentially close to what you need:

--create a table to host imported data
CREATE TABLE #SampleOutput
(
[CreatedDate] varchar(50) null,
[FailureReason] varchar(255) null,
[FromUser] varchar(50) null,
[QSource] varchar(255) null
)
GO


--Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.
BULK
INSERT #SampleOutput
FROM 'c:\SampleOutput.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

--Check the content of the table.
SELECT * FROM #SampleOutput;

DROP TABLE #SampleOutput;

Open in new window

Berhan Karagoez

ASKER

No, I do not want to use BULK insert of any kind, sorry I forgot to mention this.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

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

Start Free Trial

Berhan Karagoez

ASKER

Yes Ryan - that is the solution.

I've also used xml to solve this but your solution was better and shorter code as well, plus it did exactly what I wanted!

Thank you.

Ryan Chong

Coool great