Reading data from two CSV files

Question

Post reply

Reading data from two CSV files

SQL006

SSCertifiable

Points: 5071
More actions
March 9, 2012 at 3:26 am

#257207

Hi
I had two CSV parameter, i want to read this CSV parameter and insert into a table
Declare @Adult_csv varchar(50) = '1,2,3,4'
Declare @child_csv varchar(50) = '0,2,4,6'
CREATE TABLE #temp(ID int identity(1,1),Adultcount tinyint, ChildCount tinyint)
GO
The result should display like this
ID AdultCount ChildCount
1 1 0
2 2 2
3 3 4
4 4 6
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Use the splitter here

http://www.sqlservercentral.com/articles/Tally+Table/72993/

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Once you've downloaded the splitter function from the bottom of the article that Mark pointed you to, you would solve the problem as follows...

--===== Declare the variables and preset them for this test.

-- I did it this way so folks still using 2K5 can play, as well.

DECLARE @Adult_csv varchar(50),

@child_csv varchar(50)

;

SELECT @Adult_csv = '1,2,3,4',

@child_csv = '0,2,4,6'

;

--===== Conditionally drop the test table to make reruns in SSMS easier

IF OBJECT_ID('tempdb..#Temp','U') IS NOT NULL

DROP TABLE #Temp

;

--===== Create the table from the original post

CREATE TABLE #Temp

(

ID INT IDENTITY(1,1),

Adultcount TINYINT,

ChildCount TINYINT

)

;

--===== Solve the problem

INSERT INTO #Temp

(Adultcount, ChildCount)

SELECT AdultCount = adult.Item,

ChildCount = child.Item

FROM dbo.DelimitedSplit8K(@Adult_csv,',') adult

FULL JOIN dbo.DelimitedSplit8K(@child_csv,',') child

ON adult.ItemNumber = child.ItemNumber

ORDER BY adult.ItemNumber

;

--===== Display the result

SELECT *

FROM #Temp

ORDER BY ID

;

Result set:

ID Adultcount ChildCount

----------- ---------- ----------

1 1 0

2 2 2

3 3 4

4 4 6

(4 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL006 SSCertifiable Points: 5071 More actions · Answer 3

Thanks Mark and jeff

Jeff great article written by you...trying to understand the tally table generation 🙂