add data separate with a coma in temp table !?

  • 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

  • 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

  • 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/

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

  • 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

  • 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

  • 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

  • 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

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

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

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