add data separate with a coma in temp table !?

Question

Post reply

add data separate with a coma in temp table !?

christophe.bernard 47659

Ten Centuries

Points: 1169
More actions
December 2, 2010 at 2:27 am

#227681

Hi,
I'm a beginner with t sql and sql server 2005..
i must to create a stored procedure who do an insert ..but the problem is :
the @variable i must to receive is separate with some pipe => " | "
here is it a sample of data i must to receive :
'12456|name|domaine|category|2010/11/15|2011/10/30|2| ..... and so on '
for to do that i have create a temp table as this :
CREATE TABLE #ListTable
(
listIdToAdd int
, nameToAdd varchar(350)
, domaineToAdd varchar(350)
, categoryToAdd varchar(350)
, dateBeginToAdd datetime
, dateEndToAdd datetime
, rankToAdd int
)
now how i can parse each line ?
i know that one record contains 7 columns (listId, name, domaine, category, dateBegin, dateEnd, rank)
how ? any sample ?
thanks for your time and sample ..
christophe

Viewing 9 posts - 1 through 8 (of 8 total)

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

tommyh SSCertifiable Points: 6302 More actions · Answer 1

There are several threads about this in these forums. Search is your friend 🙂

This is

http://www.sqlservercentral.com/Forums/Topic943562-338-1.aspx#bm944589 is according to most one of the better ways to do it.

/T

tyson.price SSCrazy Points: 2093 More actions · Answer 2

This is one the first page of the scripts forum and I'm thinking you can convert it from commas to pipes.

http://www.sqlservercentral.com/scripts/String+Manipulation/71602/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

-- make some sample data, about 100,000 rows

DECLARE @variable TABLE (OneRowOfData VARCHAR(200))

INSERT INTO @variable (OneRowOfData)

SELECT '12347|name|domaine|category|2010/11/15|2011/10/30|2|'

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

-- use varchar for all columns in the staging table,

-- reduces chance of failure due to impossible implicit conversions

CREATE TABLE #ListTable

(

listIdToAdd VARCHAR(10)

, nameToAdd varchar(350)

, domaineToAdd varchar(350)

, categoryToAdd varchar(350)

, dateBeginToAdd VARCHAR(10)

, dateEndToAdd VARCHAR(10)

, rankToAdd VARCHAR(10)

)

-- About 5 seconds for 100,000 rows for the SELECT, add two or three seconds for INSERT

INSERT INTO #ListTable (listIdToAdd, nameToAdd, domaineToAdd, categoryToAdd, dateBeginToAdd, dateEndToAdd, rankToAdd)

SELECT

listIdToAdd= LEFT(v.OneRowOfData, Pos1.n-1),

nameToAdd= CASE WHEN Pos2.n > 0 THEN SUBSTRING(v.OneRowOfData, Pos1.n+1, Pos2.n-Pos1.n-1) END,

domaineToAdd= CASE WHEN Pos3.n > 0 THEN SUBSTRING(v.OneRowOfData, Pos2.n+1, Pos3.n-Pos2.n-1) END,

categoryToAdd= CASE WHEN Pos4.n > 0 THEN SUBSTRING(v.OneRowOfData, Pos3.n+1, Pos4.n-Pos3.n-1) END,

dateBeginToAdd= CASE WHEN Pos5.n > 0 THEN SUBSTRING(v.OneRowOfData, Pos4.n+1, Pos5.n-Pos4.n-1) END,

dateEndToAdd= CASE WHEN Pos6.n > 0 THEN SUBSTRING(v.OneRowOfData, Pos5.n+1, Pos6.n-Pos5.n-1) END,

rankToAdd= CASE WHEN Pos7.n > 0 THEN SUBSTRING(v.OneRowOfData, Pos6.n+1, Pos7.n-Pos6.n-1) END

FROM @variable v

CROSS APPLY (SELECT CHARINDEX('|',v.OneRowOfData, 1)) Pos1 (n)

CROSS APPLY (SELECT CHARINDEX('|',v.OneRowOfData, Pos1.n+1)) Pos2 (n)

CROSS APPLY (SELECT CHARINDEX('|',v.OneRowOfData, Pos2.n+1)) Pos3 (n)

CROSS APPLY (SELECT CHARINDEX('|',v.OneRowOfData, Pos3.n+1)) Pos4 (n)

CROSS APPLY (SELECT CHARINDEX('|',v.OneRowOfData, Pos4.n+1)) Pos5 (n)

CROSS APPLY (SELECT CHARINDEX('|',v.OneRowOfData, Pos5.n+1)) Pos6 (n)

CROSS APPLY (SELECT CHARINDEX('|',v.OneRowOfData, Pos6.n+1)) Pos7 (n)

SELECT * FROM #ListTable

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

WayneS SSC Guru Points: 95460 More actions · Answer 4

Chris,

Interesting code. Do you have any performance testing on your method compared to the DelimitedSplit8k function? (Click here for the latest Delimited Split Function.)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

WayneS (12/2/2010)
Chris,
Interesting code. Do you have any performance testing on your method compared to the DelimitedSplit8k function? (Click here for the latest Delimited Split Function.)

Hi Wayne, no I've not done serious performance testing on this method yet. My guess is that the DelimitedSplit8k function would be faster, however 5 seconds per 100,000 rows on a slow box is very reasonable.

Thanks for the link, it's easy to lose track of version updates. I'll do some testing when the time becomes available.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

christophe.bernard 47659 Ten Centuries Points: 1169 More actions · Answer 6

hi all,

for to begin thanks for your time and sorry for to take some time for to add reply 😉

the solution was to count number of pipe => 7

7 = one record for db !

and i have to create a xml document in c# and pass this xml to a stored procedure.

i build in t sql a temp table where i fill the temp table and with some sample find internet for to access in T SQL xml

here is it some code :

declare @xmlFile xml
set @xmlFile = '<?xml version="1.0"?><Items><Item><ListId>12456</ListId><Name>nom999</Name><Domain>domaine</Domain><Category>categorie</Category><DateBegin>2013/12/15</DateBegin><DateEnd>2013/12/31</DateEnd><Rank>69</Rank></Item><Item><ListId>12545</ListId><Name>nom1</Name><Domain>domaine</Domain><Category>categorie1</Category><DateBegin>2010/06/15</DateBegin><DateEnd>2010/01/10</DateEnd><Rank>2</Rank></Item></Items>'
--fill temp table
CREATE Table #TempTable
(
listId int,
[name] nvarchar(350),
domain nvarchar(350),
category nvarchar(350),
dateBegin datetime,
dateEnd datetime,
rank int
)
--INSERT SELECT
INSERT INTO #TempTable(listid, [name], domain, category, dateBegin, dateEnd, [rank])
SELECT
convert(int ,Convert(varchar(max), i.query('ListId/text()'))) as listid
, convert(varchar(max), i.query('Name/text()')) as [name]
, convert(varchar(max), i.query('Domain/text()')) as [domain]
, convert(varchar(max), i.query('Category/text()')) as category
, convert(datetime, convert(varchar(max), i.query('DateBegin/text()'))) as dateBegin
, convert(datetime, convert(varchar(max), i.query('DateEnd/text()'))) as dateEnd
, convert(int, convert(varchar(max), i.query('Rank/text()'))) as [rank]
FROM @xmlFile.nodes('/Items/Item') as x(i)

that's news for me and it's enough hard ..but it's fun 😉

thanks for your time

christ

ps : i've copy paste your sample and i will try to understand your way / solution

WayneS SSC Guru Points: 95460 More actions · Answer 7

Chris Morris-439714 (12/3/2010)
WayneS (12/2/2010)
Chris,
Interesting code. Do you have any performance testing on your method compared to the DelimitedSplit8k function? (Click here for the latest Delimited Split Function.)
Hi Wayne, no I've not done serious performance testing on this method yet. My guess is that the DelimitedSplit8k function would be faster, however 5 seconds per 100,000 rows on a slow box is very reasonable.
Thanks for the link, it's easy to lose track of version updates. I'll do some testing when the time becomes available.

I'm not so sure - your method can eliminate the disk access for the tally table by doing everything all in memory. I hope to get to testing this out sometime soon.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Where in DelimitedSplit8K do you find a physical Tally Table?

--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)