SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


add data separate with a coma in temp table !?


add data separate with a coma in temp table !?

Author
Message
christophe.bernard 47659
christophe.bernard 47659
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 199
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
tommyh
tommyh
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2572 Visits: 2000
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
tyson.price
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 642
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
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42637 Visits: 20015

-- make some sample data, about 100,000 rows
DECLARE @variable TABLE (OneRowOfData VARCHAR(200))
INSERT INTO @variable (OneRowOfData)
SELECT '12345|name|domaine|category|2010/11/15|2011/10/30|2|' UNION ALL
SELECT '12346|name|domaine|category|2010/11/15|2011/10/30|2|' UNION ALL
SELECT '12347|name|domaine|category|2010/11/15|2011/10/30|2|'
INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable
INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable
INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable
INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable
INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable
INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable
INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable
INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable
INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable
INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable
INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable
INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable
INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable
INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable
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
Exploring Recursive CTEs by Example Dwain Camps
WayneS
WayneS
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22194 Visits: 10656
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42637 Visits: 20015
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
Exploring Recursive CTEs by Example Dwain Camps
christophe.bernard 47659
christophe.bernard 47659
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 199
hi all,

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

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 Wink

thanks for your time
christ

ps : i've copy paste your sample and i will try to understand your way / solution
WayneS
WayneS
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22194 Visits: 10656
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220760 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search