Parse SQL data and load into two different tables "Help"

  • Hi all,

    I couldn't figure it out how to parse the file and load into two SQL table. Source and destination are in SQL. Below are the sample SQL code for Source Sample table/data and destination tables. Please let me know if my question is not clear. I really appreciated.

    Here is my sample Source data
    Source_id,sta_id    ,Address1     ,City     ,PaymentMethod,Invoice_Detail
    21     ,KXV        ,Rd         ,       ,       ,
    22     ,KXV        ,PO Box234    ,Dallas,       ,
    31     ,         ,         ,     ,Cash       ,;8555-4
    42     ,Paid Sat    ,         ,     ,       ,
    Y     ,180217    ,6         ,23550     ,     ,800-345-969
    21     ,CNN        ,TV         ,     ,       ,
    22     ,CNN        ,PO Box 99    ,Chicag,       ,
    31     ,         ,         ,     ,Cash       ,224;86799
    42     ,Paid Sun    ,         ,     ,       ,
    Y     ,180218    ,6         ,3456     ,     ,888-191-1900
    Y     ,180226    ,7         ,456     ,         ,800-900-8999

    Here are my two Destination Tables that I want to load my source file

    Table A
    Destination_Header
    id,Invoice#,Address1 ,City ,Sta_id
    1 ,8555-4 ,PO Box234,Dallas,KXV
    2 ,86799 ,PO Box 99,Chicag,CNN

    Table B
    Destination_Detail
    id,Header_id,Month,Day,Year,Air,Phone#
    1 ,1 ,02 ,17 ,18 ,6 ,800-345-969
    2 ,2 ,02 ,18 ,18 ,6 ,888-191-1900
    3 ,2 ,02 ,26 ,18 ,7 ,800-900-8999

    I couldn't figure it out how to parse the file and load into two SQL table. Source and destination are in SQL. Below are the sample SQL code for Source Sample table/data
    and destination tables. Please let me know if my question is not clear. I really appreciated.


    ----*********Source Table SQL***************************
    CREATE TABLE ##SourceTable
    (
    source_id        VARCHAR(20)
    ,sta_id            VARCHAR(20)
    ,Address1        VARCHAR(50)
    ,City            VARCHAR(50)         
    ,PaymentMethod    VARCHAR(50)
    ,Invoice_Detail    VARCHAR(50)
    )

    INSERT INTO ##SourceTable
    SELECT '21','KXV','Rd',NULL,NULL,NULL
    UNION
    SELECT '22','KXV','PO Box234','Dallas',NULL,NULL
    UNION
    SELECT '31',NULL,NULL,NULL,'Cash',';8555-4'
    UNION
    SELECT '42','Paid Sat',NULL,NULL,NULL,NULL
    UNION
    SELECT 'Y','180217','6','23550',NULL,'800-345-969'

    INSERT INTO ##SourceTable
    SELECT '21','CNN','TV',NULL,NULL,NULL
    UNION
    SELECT '22','CNN','PO Box 99','Chicago',NULL,NULL
    UNION
    SELECT '31',NULL,NULL,NULL,'Cash','224;86799'
    UNION
    SELECT '42','Paid Sun',NULL,NULL,NULL,NULL
    UNION
    SELECT 'Y','180218','6','3456',NULL,'888-191-1900'
    UNION
    SELECT 'Y','180226','7','456',NULL,'800-900-8999'

    SELECT * FROM ##SourceTable

    ----*********Destination Table A SQL***************************
    CREATE TABLE ##DestinationTable_A
    (

    id            INT IDENTITY(1,1),
    Invoice#    VARCHAR(50),
    Address1    VARCHAR(50),
    City        VARCHAR(50),
    Sta_id        VARCHAR(20)
    )

    ----*********Destination Table B SQL***************************
    CREATE TABLE ##DestinationTable_B
    (

    id            INT IDENTITY(1,1),
    Header_id    VARCHAR(50),
    [Month]        VARCHAR(50),
    [Day]        VARCHAR(50),
    [Year]        VARCHAR(20),
    Air            VARCHAR(20),
    Phone#        VARCHAR(20)

    )

    DROP TABLE ##SourceTable
    DROP TABLE ##DestinationTable_A
    DROP TABLE ##DestinationTable_B

  • Please will you show us what you've already tried, because it's not clear where you're stuck?

    The best advice is to change the design - there's no way you should be mixing those two data sets in one table.  But if you're stuck with it, surely you just insert into Destination B all the rows where sta_id is Y, and into Destination A everything else?

    John

  • Hi John,
    Thanks for your time to reply. 
    In above code, I missed important source field "Source_ID" that will tell where the batch start and ends.

    John, I was trying to accomplish through SSIS, because my source is .txt. I am not very good in SQL that's the reason I asked for help or show me the right path.

    Thank You!

  • Sorry, but you've confused me.  First you said the source is SQL, now you're saying it's .txt.  But if it's .txt, just bring it into a staging table - as you already appear to have done - and manipulate it from there.  If you're not very good in SQL then that's going to be a problem for you.  I'm happy to help out, but I need you to tell me where you're stuck.

    John

  • Sorry If I confuse you. Yes, first I said my source is SQL and yes the original source is my .txt. I thought it is easy for me to load the data into STG and do SQL to parse that data. Anyway, Below is the "While Loop" that I am using to parse the data.
    I couldn't figure it out how can I pull One Batch (One Bath start from source_id = '21' to Next source_id = '21') then I can parse the field one by one and load into my destination tables.

    Jon, I am sure now I didn't confuse you more.


      DECLARE @loopA_id INT ,
                    @loopA_times INT ,
                        
                    @Address VARCHAR(50),
                    @CITY     VARCHAR(20),
                    @iNVOICE    VARCHAR(200),
                    @Sta_id        VARCHAR(20);

       SET @loopA_id = 1;
       SET @loopA_times = 0;

    -- Create Loop Tables
       IF OBJECT_ID('tempdb..#ExactMatch') IS NOT NULL
        BEGIN
          DROP TABLE #ExactMatch;
        END;

       CREATE TABLE #ExactMatch
        (
         codA_id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY ,
         sta_id VARCHAR(20),
           );

                        -- Load Loop Table
       INSERT INTO #ExactMatch
          (
                        sta_id
          )
          SELECT DISTINCT
            sta_id
                         FROM SourceTable_EIVA
          WHERE source_id = '21'

                                -- Set Loop Times Variable
       SET @loopA_times = @@rowcount;

       WHILE @loopA_id <= @loopA_times
        BEGIN

          SELECT
                            @Address = e.Address1
                            ,@CITY = e.City
                            ,@iNVOICE = (
                                                SELECT Invoice_Detail FROM SourceTable_EIVA e WHERE e.source_id = '31' --s.sta_id
                                                    --OR source_id = '31'
                                        )
                            ,@Sta_id = s.sta_id
          FROM  #ExactMatch s
                    INNER JOIN SourceTable_EIVA e ON e.sta_id = s.sta_id
          WHERE codA_id = @loopA_id;

                    INSERT INTO DestinationTable_A_Eivav (Invoice#,Address1,City,Sta_id)
                    VALUES (@iNVOICE,@Address,@CITY,@Sta_id)

          SET @loopA_id = @loopA_id + 1;
        END;

  • If I can add "RANKID" in my source then I can easily Pull my data through above While Loop. Here is source table as an example.

    RankID,Source_id,sta_id    ,Address1 ,City    ,PaymentMethod,Invoice_Detail
    1          ,21    ,KXV    ,Rd    , ,    ,
    1          ,22    ,KXV    ,PO Box234    ,Dallas,    ,
    1          ,31    ,    ,    ,    ,Cash    ,;8555-4
    1          ,42    ,Paid Sat    ,    ,    ,    ,
    1          ,Y    ,180217    ,6    ,23550    , ,800-345-969
    2          ,21    ,CNN    ,TV    ,    ,    ,
    2          ,22    ,CNN    ,PO Box 99    ,Chicag,    ,
    2          ,31    ,    ,    ,    ,Cash    ,224;86799
    2          ,42    ,Paid Sun    ,    ,    ,    ,
    2           ,Y    ,180218    ,6    ,3456    , ,888-191-1900
    2           ,Y    ,180226    ,7    ,456    , ,800-900-8999

  • Is someone can help me to write a code to add "RankID" column in my source file?

    Sample Source Data
    Source_id,sta_id ,Address1 ,City ,PaymentMethod,Invoice_Detail
    21 ,KXV ,Rd , , ,
    22 ,KXV ,PO Box234 ,Dallas, ,
    31 , , , ,Cash ,;8555-4
    42 ,Paid Sat , , , ,
    Y ,180217 ,6 ,23550 , ,800-345-969
    21 ,CNN ,TV , , ,
    22 ,CNN ,PO Box 99 ,Chicag, ,
    31 , , , ,Cash ,224;86799
    42 ,Paid Sun , , , ,
    Y ,180218 ,6 ,3456 , ,888-191-1900
    Y ,180226 ,7 ,456 , ,800-900-8999 

    What I want after add/populate "RankID"

    RankID,Source_id,sta_id ,Address1 ,City ,PaymentMethod,Invoice_Detail
    1 ,21 ,KXV ,Rd , , ,
    1 ,22 ,KXV ,PO Box234 ,Dallas, ,
    1 ,31 , , , ,Cash ,;8555-4
    1 ,42 ,Paid Sat , , , ,
    1 ,Y ,180217 ,6 ,23550 , ,800-345-969
    2 ,21 ,CNN ,TV , , ,
    2 ,22 ,CNN ,PO Box 99 ,Chicag, ,
    2 ,31 , , , ,Cash ,224;86799
    2 ,42 ,Paid Sun , , , ,
    2 ,Y ,180218 ,6 ,3456 , ,888-191-1900
    2 ,Y ,180226 ,7 ,456 , ,800-900-8999 

    Thanks in advance.

  • There is absolutely nothing in your source data to guarantee the order of the data to do any batching or grouping with.  If this is actually starting out as a text file, please attach it along with a record layout and a target table and one of us might be able to show you how to pull this off.

    We also need to know the meaning behind the various Source_IDs and how many of each a "batch" can possibly have.

    --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 8 posts - 1 through 7 (of 7 total)

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