Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

add data separate with a coma in temp table !? Expand / Collapse
Author
Message
Posted Thursday, December 2, 2010 2:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 12, 2014 5:35 AM
Points: 144, Visits: 131
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
Post #1029112
Posted Thursday, December 2, 2010 2:57 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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
Post #1029127
Posted Thursday, December 2, 2010 3:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:26 AM
Points: 74, Visits: 620
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/

Post #1029137
Posted Thursday, December 2, 2010 4:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:36 AM
Points: 7,128, Visits: 13,507
-- 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
Post #1029158
Posted Thursday, December 2, 2010 10:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 6,582, Visits: 8,861
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
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
Post #1029391
Posted Friday, December 3, 2010 2:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:36 AM
Points: 7,128, Visits: 13,507
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
Post #1029713
Posted Friday, December 3, 2010 3:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 12, 2014 5:35 AM
Points: 144, Visits: 131
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
Post #1029722
Posted Friday, December 3, 2010 5:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 6,582, Visits: 8,861
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
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
Post #1029778
Posted Friday, December 3, 2010 11:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1030243
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse