none
Data Transfer from staging to Working table RRS feed

  • Question

  • Hi,

    What would be the best way to move data from staging to sql working table

    i have product data as per below. product can be sold in more than one ratio (i.e 1,6,24 in below example)

    Each ratio can have more than one barcode.

    location id plucode barcode Ratio
    1001 plu1001 bxxx 1
    1001 plu1001 bxxxx 1
    1001 plu1001 xxxx 6
    1001 plu1001 xxxxy 24
    1001 plu1001 xxxxyy 24

    Working table 

    product barcode 

    location id plucode Ratio barcode
    1001 plu1001 max(barcode)
    1001 plu1001 6  max(barcode)
    1001 plu1001 24  max(barcode)

    Product Ratio(if ratio exists in staging table - mark as active else inactive)

    location id plucode Ratio isactive
    1001 plu1001 1 1
    1001 plu1001 6 1
    1001 plu1001 24 1

    Will SQL Merge will be a best option or SSIS?

    Sunday, October 13, 2019 10:14 PM

All replies

  • Hi,

    Would you lie this one ?

    IF OBJECT_ID('staging') IS NOT NULL drop table  staging 
    IF OBJECT_ID('product barcode') IS NOT NULL drop table  [product barcode] 
    go 
    create table staging
    ([location id] int ,
    plucode	varchar(10),
    barcode	varchar(10),
    Ratio int )
    insert into staging values 
    (1001,'plu1001','bxxx',1),
    (1001,'plu1001','bxxxx',1),
    (1001,'plu1001','xxxx',6),
    (1001,'plu1001','xxxxy',24),
    (1001,'plu1001','xxxxyy',24)
    create table [product barcode]
    ([location id] int ,
    plucode	varchar(10),
    Ratio	int,
    barcode varchar(10), )
    insert into [product barcode] values 
    (1001,'plu1001',1,'bxxxx'),
    (1001,'plu1001',6,'xxxx'),
    (1001,'plu1001',24,'xxxxyy')
    
    select *, 
    case when exists(select 1 from staging b where a.Ratio=b.Ratio) 
    then 1 else 0 end  isactive
    from [product barcode] a
    /*
    location id plucode    Ratio       barcode    isactive
    ----------- ---------- ----------- ---------- -----------
    1001        plu1001    1           bxxxx      1
    1001        plu1001    6           xxxx       1
    1001        plu1001    24          xxxxyy     1
    
    */

    Could you  please share us your table structure (CREATE TABLE …) and some sample data (INSERT INTO…) along with your expected result? So that we’ll get a right direction and make some test.

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 14, 2019 8:15 AM
  • If you are already using SSIS for other parts of the solution, use it.

    You can also do it the way Rachel_Wang mentioned.

    Wednesday, October 16, 2019 12:42 PM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 17, 2019 9:37 AM