none
SQL View Needed RRS feed

  • Question

  • I am looking for someone to create a view based on the following data:

    There are about 1000 rows in the ITEM_TABLE and maybe 5000 rows in the PRICE_TABLE

    Each entry in the ITEM_TABLE can have 1 to 6 entries in the PRICE_TABLE.

    This is for a business application and not a homework assignment.  I am looking for a cost for this and also a time frame.

    Basically I want a script I can copy and paste and run it on the client's server.

    If you are creating a table then a view to the table, the table needs to be updated in real time or on a job scheduler.

    ITEM_TABLE
    No_ Description
    .250BP Test Description for .250BP
    .250BR1WRAP Test Description for .250BR1WRAP
    PRICE_TABLE
    No_ [Minimum Quantity] [Maximum Quantity] [Unit Price]
    .250BP 0.001 249.999 0.51
    .250BP 250 499.999 0.47
    .250BP 500 100000000 0.43
    .250BR1WRAP 0.001 1999.999 0.41
    .250BR1WRAP 2000 100000000 0.39
    View
    No_ Description [Minimum Quantity-1] [Maximum Quantity-1] [Unit Price-1] [Minimum Quantity-2] [Maximum Quantity-2] [Unit Price-2] [Minimum Quantity-3] [Maximum Quantity-3] [Unit Price-3]
    .250BP Test Description for .250BP 0.001 249.999 0.51 250 499.999 0.47 500 100000000 0.43
    .250BR1WRAP Test Description for .250BR1WRAP 0.001 1999.999 0.41 2000 100000000 0.39

    Friday, November 15, 2019 1:07 PM

Answers

  • Hi Brian Branco,

    Please try following script .

    ---------dynamic code
    IF OBJECT_ID('ITEM_TABLE') IS NOT NULL drop table  ITEM_TABLE   
    IF OBJECT_ID('PRICE_TABLE') IS NOT NULL drop table  PRICE_TABLE   
    go 
    create table ITEM_TABLE  (
    No_	varchar(20),
    Description varchar(50)
    );
    INSERT INTO ITEM_TABLE VALUES
    ('.250BP','Test Description for .250BP'),
    ('.250BR1WRAP','Test Description for .250BR1WRAP')
    
    create table PRICE_TABLE  (
    No_	varchar(20),
    [Minimum Quantity]	decimal(20,3),
    [Maximum Quantity]	decimal(20,3),
    [Unit Price] decimal(20,2)
    );
    INSERT INTO PRICE_TABLE VALUES
    ('.250BP',0.001,249.999,0.51),
    ('.250BP',250,499.999,0.47),
    ('.250BP',500,100000000,0.43),
    ('.250BR1WRAP',0.001,1999.999,0.41),
    ('.250BR1WRAP',2000,100000000,0.39)
    
    DECLARE @sql_str VARCHAR(max)
    DECLARE @sql_col VARCHAR(max)
    DECLARE @sql_colp VARCHAR(max)
    DECLARE @sql_colpp VARCHAR(max)
    DECLARE @sql_colstring VARCHAR(max)
    DECLARE @sql VARCHAR(max)
    
    
    DECLARE @max_vaue int=(select max(rn) from (select ROW_NUMBER()OVER(partition by No_ Order by [Minimum Quantity]) as rn  from PRICE_TABLE) t  )
    declare @i int=1
    
    while (@i<=@max_vaue)
    begin
    SELECT @sql_col =ISNULL(@sql_col + ',','')+ QUOTENAME(@i) 
    SELECT @sql_colp =ISNULL(@sql_colp + ',','')+ QUOTENAME('P'+cast (@i as varchar(10))) 
    SELECT @sql_colpp =ISNULL(@sql_colpp + ',','')+ QUOTENAME('PP'+cast (@i as varchar(10))) 
    SELECT @sql_colstring=ISNULL(@sql_colstring + ',','')+ 'max('+QUOTENAME(cast (@i as varchar(10)))
    +') AS  [Minimum Quantity-'+cast (@i as varchar(10))+'],max('+QUOTENAME('P'+cast (@i as varchar(10)))
    +') as [Maximum Quantity-'+cast (@i as varchar(10))+'],max('+QUOTENAME('PP'+cast (@i as varchar(10)))
    +') as [Unit Price-'+cast (@i as varchar(10))+']'
    set @i=@i+1
    end
    
    set @sql = '
    ;with cte as (
    select a.*, b.[Minimum Quantity],b.[Maximum Quantity],b.[Unit Price]
    from ITEM_TABLE a join PRICE_TABLE b  on a.No_=b.No_ 
    )
    Select No_,Description,'+@sql_colstring+'
    from  (
    select *,
    ROW_NUMBER()OVER(partition by No_ Order by [Minimum Quantity]) P,
    ''P''+CAST(ROW_NUMBER()OVER(partition by No_ Order by [Minimum Quantity])AS VARCHAR) PP,
    ''PP''+CAST(ROW_NUMBER()OVER(partition by No_ Order by [Minimum Quantity])AS VARCHAR) PPP
    from cte  )T
    PIVOT (MAX([Minimum Quantity]) FOR P IN  ('+@sql_col+')) AS P
    PIVOT (MAX([Maximum Quantity]) FOR PP IN ('+@sql_colp+')) AS P
    PIVOT (MAX([Unit Price]) FOR PPP IN ('+@sql_colpp+')) AS P
    group by No_,Description
    '
    print (@sql)
    exec (@sql)
    /*
    ;with cte as (
    select a.*, b.[Minimum Quantity],b.[Maximum Quantity],b.[Unit Price]
    from ITEM_TABLE a join PRICE_TABLE b  on a.No_=b.No_ 
    )
    Select No_,Description,max([1]) AS  [Minimum Quantity-1],max([P1]) as [Maximum Quantity-1],max([PP1]) as [Unit Price-1],max([2]) AS  [Minimum Quantity-2],max([P2]) as [Maximum Quantity-2],max([PP2]) as [Unit Price-2],max([3]) AS  [Minimum Quantity-3],max([P3]) as [Maximum Quantity-3],max([PP3]) as [Unit Price-3]
    from  (
    select *,
    ROW_NUMBER()OVER(partition by No_ Order by [Minimum Quantity]) P,
    'P'+CAST(ROW_NUMBER()OVER(partition by No_ Order by [Minimum Quantity])AS VARCHAR) PP,
    'PP'+CAST(ROW_NUMBER()OVER(partition by No_ Order by [Minimum Quantity])AS VARCHAR) PPP
    from cte  )T
    PIVOT (MAX([Minimum Quantity]) FOR P IN  ([1],[2],[3])) AS P
    PIVOT (MAX([Maximum Quantity]) FOR PP IN ([P1],[P2],[P3])) AS P
    PIVOT (MAX([Unit Price]) FOR PPP IN ([PP1],[PP2],[PP3])) AS P
    group by No_,Description
    No_                  Description                                        Minimum Quantity-1                      Maximum Quantity-1                      Unit Price-1                            Minimum Quantity-2                      Maximum Quantity-2                      Unit Price-2                            Minimum Quantity-3                      Maximum Quantity-3                      Unit Price-3
    -------------------- -------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
    .250BP               Test Description for .250BP                        0.001                                   249.999                                 0.51                                    250.000                                 499.999                                 0.47                                    500.000                                 100000000.000                           0.43
    .250BR1WRAP          Test Description for .250BR1WRAP                   0.001                                   1999.999                                0.41                                    2000.000                                100000000.000                           0.39                                    NULL                                    NULL                                    NULL
    */
    
    
    
    

    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, November 18, 2019 7:33 AM

All replies

  • A "VIEW" is simply a SELECT statement.  There is nothing special about a VIEW.

    There is no way to create a VIEW to display a random number of columns, the number and types of columns must be known at creation time. 

    Your example data has "No_" 3 times in PRICE_TABLE.  Can there be more than 3 rows for a "No_"?   If so, what is the expected results?

    Friday, November 15, 2019 1:16 PM
    Moderator
  • We can assume that the maximum number of rows for any NO_ will be 6 in the PRICE_TABLE - if there are more, they can be ignored.  So there can be between 1 and 6 rows for any NO_ in the PRICE_TABLE.

    I have a 3rd party connector that connects SQL to Big Commerce from eBridge (https://ebridgeconnections.com/Home.aspx) - they are saying they need a VIEW setup to the data to read it.


    Friday, November 15, 2019 1:28 PM
  • There is no concept of order in SQL Server unless you order the query.  How do you order the query to get the "first" 6 rows, PIRCE_TABLE does not have anything obvious to sort by?  Does the query need to be repeatable, return the same results every time?

    Friday, November 15, 2019 1:55 PM
    Moderator
  • It can be sorted by NO_ + [Minimum Quantity]

    I am not user what you mean by repeatable and return the same results. 

    Friday, November 15, 2019 2:01 PM
  • This is the simplest way and will make the most sense:

    CREATE VIEW ITEM_TIER 
    AS
    WITH tier as (
    SELECT 
        i.No_, i.[Description],
        pt.[Minimum Quantity], pt.[Maximum Quantity], pt.[Unit Price],
        ROW_NUMBER() OVER (PARTITION BY i.[No_] ORDER BY i.[No_],pt.[Minimum Quantity]) as rownum
    FROM ITEM_TABLE i
        INNER JOIN PRICE_TABLE pt
        ON pt.No_ = i.No_
    )
    SELECT 
        t.No_, t.[Description], 
        t.[Minimum Quantity] as  [Minimum Quantity-1],
        t.[Maximum Quantity] as  [Maximum Quantity-1],
        t.[Unit Price] as [Unit Price-1],
    
        t2.[Minimum Quantity] as  [Minimum Quantity-2],
        t2.[Maximum Quantity] as  [Maximum Quantity-2],
        t2.[Unit Price] as [Unit Price-2],
    
        t3.[Minimum Quantity] as  [Minimum Quantity-3],
        t3.[Maximum Quantity] as  [Maximum Quantity-3],
        t3.[Unit Price] as [Unit Price-3],
    
        t4.[Minimum Quantity] as  [Minimum Quantity-4],
        t4.[Maximum Quantity] as  [Maximum Quantity-4],
        t4.[Unit Price] as [Unit Price-4],
    
        t5.[Minimum Quantity] as  [Minimum Quantity-5],
        t5.[Maximum Quantity] as  [Maximum Quantity-5],
        t5.[Unit Price] as [Unit Price-5],
    
        t6.[Minimum Quantity] as  [Minimum Quantity-6],
        t6.[Maximum Quantity] as  [Maximum Quantity-6],
        t6.[Unit Price] as [Unit Price-6]
    
    FROM tier t
        LEFT OUTER JOIN tier t2
        ON t2.[No_] = t.[No_] 
        AND t2.rownum = 2
        LEFT OUTER JOIN tier t3
        ON t3.[No_] = t.[No_] 
        AND t3.rownum = 3
        LEFT OUTER JOIN tier t4
        ON t4.[No_] = t.[No_] 
        AND t4.rownum = 4
        LEFT OUTER JOIN tier t5
        ON t5.[No_] = t.[No_] 
        AND t5.rownum = 5
        LEFT OUTER JOIN tier t6
        ON t6.[No_] = t.[No_] 
        AND t6.rownum = 6
    WHERE t.rownum = 1
    ;

    It is not very efficient, but for the number of rows you are describing it won't make any difference.

    Friday, November 15, 2019 2:18 PM
    Moderator
  • Thanks, I'll try it out this afternoon
    Friday, November 15, 2019 2:21 PM
  • >> Each entry in the ITEM_TABLE [sic] can have 1 to 6 entries in the PRICE_TABLE. [sic]  <<

    Thank you for no DDL so we know we can be just as rude as we want to because you're just as rude as you want to be to us. Putting the postfix "_table" is a design flaw called a "tibble" and RDBMS people even have little jingles and poems about how bad it is. The basic flaws, your mixing data and metadata.

    >> This is for a business application and not a homework assignment.  I am looking for a cost for this and also a time frame. <<

    If you really want a schema design, then we need to talk off-line. Please look at my credentials and see if you think I'm worth $$$ er hour for your application. I've been doing this for over 30 years and I think I'm worth it. But then I do have delusions of grandeur. :-)

    CREATE TABLE Catalog 
    (item_gtin CHAR(15) NOT NULL PRIMARY KEY,
     , ..);

    CREATE TABLE Price_List
    (item_gtin CHAR(15) NOT NULL
      REFERENCES Inventory (item_gtin)
      ON UPDATE CASCADE
      ON DELETE CASCADE,
     price_level INTEGER NOT NULL
      CHECK(price_level BETWEEN 1 AND 6),
    PRIMARY KEY (item_gtin, price_level ),
    item_price  DECIMAL(10,2) NOT NULL
      CHECK (item_price >= 0.00),
     ...);

    Here's some basic modeling tips. The items you sell are clearly entities and entities get a table. Every table must have a primary key; this is not an option. This is the definition. The global trade item number (GTIN) is an industry standard, much like the old UPC codes on merchandise. Since we have no idea what were doing here because we don't have your specs, this is the most general form, you could have for merchandise identifier. 

    You said your price list have 6 levels of pricing. If you ever get around to reading Codd's 12 laws of RDBMS, you know, this must be modeled as a scalar value in a column in a row of the table. I choose to call it price level, but use whatever your industry favors (we don't even know what modeling do we?) See how this becomes the primary key. Look to the other constraints to guarantee that you don't sell something for less than 0.00. I'm not trying to be funny with that, because I have had to repair databases that allowed negative quantities and absurd prices.

    I hope you don't actually use things like "[Minimum Quantity]" in your code. 1st of all, the use of square brackets is strictly a Microsoft dialect and we see it, we know that you're sort of a hillbilly that doesn't really know SQL. But more than that embedding spaces in identifier like that fox up all kinds of ISO standards. It says you're trying to do a COBOL style formatting in the database tier of your tiered architecture. That's right up there with tibbling :-(

    Would you like to start over and give us some usable specs?

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, November 16, 2019 4:49 AM
  • CELKO -

    I have no idea of what you are talking about and I really don't need a lecture.  I have a client with a requirement for their new web site to pull data from their ERP System and that's what I have.

    I gave you my table layouts and my required output.  The column names don't need brackets, the names can be MIN1, MAX1, PRICE1, MIN2, MAX2, PRICE2 etc...

    If you are interested in providing an estimate, then please do, that is what I asked for.  If not then have a good day.



    • Edited by Brian Branco Saturday, November 16, 2019 9:52 PM
    Saturday, November 16, 2019 9:51 PM
  • Hi,

    Here you go. Modify it if necessary and mark as answer if it helps. Report any issues etc. Thanks.

    create table ITEM_TABLE (No_ varchar(200), Description varchar(200))
    insert ITEM_TABLE values ('.250BP','Test Description for .250BP'),('.250BR1WRAP','Test Description for .250BR1WRAP')

    create table PRICE_TABLE(No_ varchar(200), MinQ float,MaxQ float, Price float)
    insert PRICE_TABLE values ('.250BP',0.001,249.999,0.51),('.250BP',250,499.999,0.47),('.250BP',500,100000000,0.43),
    ('.250BR1WRAP', 0.001,1999.999,0.41),('.250BR1WRAP',2000,100000000,0.39)

    select t.No_, t.[Description], 
    max(case when r = 1 then MinQ end) Min1,
    max(case when r = 1 then MaxQ end) Max1,
    max(case when r = 1 then Price end) Price1,

    max(case when r = 2 then MinQ end) Min2,
    max(case when r = 2 then MaxQ end) Max2,
    max(case when r = 2 then Price end) Price2,

    max(case when r = 3 then MinQ end) Min3,
    max(case when r = 3 then MaxQ end) Max3,
    max(case when r = 3 then Price end) Price3,

    max(case when r = 4 then MinQ end) Min4,
    max(case when r = 4 then MaxQ end) Max4,
    max(case when r = 4 then Price end) Price4,

    max(case when r = 5 then MinQ end) Min5,
    max(case when r = 5 then MaxQ end) Max5,
    max(case when r = 5 then Price end) Price5,

    max(case when r = 6 then MinQ end) Min6,
    max(case when r = 6 then MaxQ end) Max6,
    max(case when r = 6 then Price end) Price6
    from
    (select a.No_, a.[description], b.MinQ, b.MaxQ, b.Price, rank() over (partition by a.No_ order by a.No_, b.MinQ) r
    from ITEM_TABLE a, PRICE_TABLE b
    where a.No_ = b.No_) t
    where r <= 6
    group by t.No_, t.[Description]

    Sunday, November 17, 2019 3:42 AM
  • Hi Brian Branco,

    Please try following script .

    ---------dynamic code
    IF OBJECT_ID('ITEM_TABLE') IS NOT NULL drop table  ITEM_TABLE   
    IF OBJECT_ID('PRICE_TABLE') IS NOT NULL drop table  PRICE_TABLE   
    go 
    create table ITEM_TABLE  (
    No_	varchar(20),
    Description varchar(50)
    );
    INSERT INTO ITEM_TABLE VALUES
    ('.250BP','Test Description for .250BP'),
    ('.250BR1WRAP','Test Description for .250BR1WRAP')
    
    create table PRICE_TABLE  (
    No_	varchar(20),
    [Minimum Quantity]	decimal(20,3),
    [Maximum Quantity]	decimal(20,3),
    [Unit Price] decimal(20,2)
    );
    INSERT INTO PRICE_TABLE VALUES
    ('.250BP',0.001,249.999,0.51),
    ('.250BP',250,499.999,0.47),
    ('.250BP',500,100000000,0.43),
    ('.250BR1WRAP',0.001,1999.999,0.41),
    ('.250BR1WRAP',2000,100000000,0.39)
    
    DECLARE @sql_str VARCHAR(max)
    DECLARE @sql_col VARCHAR(max)
    DECLARE @sql_colp VARCHAR(max)
    DECLARE @sql_colpp VARCHAR(max)
    DECLARE @sql_colstring VARCHAR(max)
    DECLARE @sql VARCHAR(max)
    
    
    DECLARE @max_vaue int=(select max(rn) from (select ROW_NUMBER()OVER(partition by No_ Order by [Minimum Quantity]) as rn  from PRICE_TABLE) t  )
    declare @i int=1
    
    while (@i<=@max_vaue)
    begin
    SELECT @sql_col =ISNULL(@sql_col + ',','')+ QUOTENAME(@i) 
    SELECT @sql_colp =ISNULL(@sql_colp + ',','')+ QUOTENAME('P'+cast (@i as varchar(10))) 
    SELECT @sql_colpp =ISNULL(@sql_colpp + ',','')+ QUOTENAME('PP'+cast (@i as varchar(10))) 
    SELECT @sql_colstring=ISNULL(@sql_colstring + ',','')+ 'max('+QUOTENAME(cast (@i as varchar(10)))
    +') AS  [Minimum Quantity-'+cast (@i as varchar(10))+'],max('+QUOTENAME('P'+cast (@i as varchar(10)))
    +') as [Maximum Quantity-'+cast (@i as varchar(10))+'],max('+QUOTENAME('PP'+cast (@i as varchar(10)))
    +') as [Unit Price-'+cast (@i as varchar(10))+']'
    set @i=@i+1
    end
    
    set @sql = '
    ;with cte as (
    select a.*, b.[Minimum Quantity],b.[Maximum Quantity],b.[Unit Price]
    from ITEM_TABLE a join PRICE_TABLE b  on a.No_=b.No_ 
    )
    Select No_,Description,'+@sql_colstring+'
    from  (
    select *,
    ROW_NUMBER()OVER(partition by No_ Order by [Minimum Quantity]) P,
    ''P''+CAST(ROW_NUMBER()OVER(partition by No_ Order by [Minimum Quantity])AS VARCHAR) PP,
    ''PP''+CAST(ROW_NUMBER()OVER(partition by No_ Order by [Minimum Quantity])AS VARCHAR) PPP
    from cte  )T
    PIVOT (MAX([Minimum Quantity]) FOR P IN  ('+@sql_col+')) AS P
    PIVOT (MAX([Maximum Quantity]) FOR PP IN ('+@sql_colp+')) AS P
    PIVOT (MAX([Unit Price]) FOR PPP IN ('+@sql_colpp+')) AS P
    group by No_,Description
    '
    print (@sql)
    exec (@sql)
    /*
    ;with cte as (
    select a.*, b.[Minimum Quantity],b.[Maximum Quantity],b.[Unit Price]
    from ITEM_TABLE a join PRICE_TABLE b  on a.No_=b.No_ 
    )
    Select No_,Description,max([1]) AS  [Minimum Quantity-1],max([P1]) as [Maximum Quantity-1],max([PP1]) as [Unit Price-1],max([2]) AS  [Minimum Quantity-2],max([P2]) as [Maximum Quantity-2],max([PP2]) as [Unit Price-2],max([3]) AS  [Minimum Quantity-3],max([P3]) as [Maximum Quantity-3],max([PP3]) as [Unit Price-3]
    from  (
    select *,
    ROW_NUMBER()OVER(partition by No_ Order by [Minimum Quantity]) P,
    'P'+CAST(ROW_NUMBER()OVER(partition by No_ Order by [Minimum Quantity])AS VARCHAR) PP,
    'PP'+CAST(ROW_NUMBER()OVER(partition by No_ Order by [Minimum Quantity])AS VARCHAR) PPP
    from cte  )T
    PIVOT (MAX([Minimum Quantity]) FOR P IN  ([1],[2],[3])) AS P
    PIVOT (MAX([Maximum Quantity]) FOR PP IN ([P1],[P2],[P3])) AS P
    PIVOT (MAX([Unit Price]) FOR PPP IN ([PP1],[PP2],[PP3])) AS P
    group by No_,Description
    No_                  Description                                        Minimum Quantity-1                      Maximum Quantity-1                      Unit Price-1                            Minimum Quantity-2                      Maximum Quantity-2                      Unit Price-2                            Minimum Quantity-3                      Maximum Quantity-3                      Unit Price-3
    -------------------- -------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
    .250BP               Test Description for .250BP                        0.001                                   249.999                                 0.51                                    250.000                                 499.999                                 0.47                                    500.000                                 100000000.000                           0.43
    .250BR1WRAP          Test Description for .250BR1WRAP                   0.001                                   1999.999                                0.41                                    2000.000                                100000000.000                           0.39                                    NULL                                    NULL                                    NULL
    */
    
    
    
    

    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, November 18, 2019 7:33 AM
  • 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.

    Tuesday, November 19, 2019 8:48 AM
  • Rachel,

    This worked great, thank you!!!

    Now I am trying to change it to read existing data so the ITEM table already exists.

    So I created a new DB, took out the part that created the ITEM table

       The database name that contains the item file is TEX-TEST and the actual table name is TEX Sys$item so I have this:

    I changed ITEM_TABLE to be [TEX-TEST].[DBO].[TEX_SYS$ITEM]

    But I get invalid object when I try to run it.

    There is very little security on the server it you think its security related.

    Any ideas?



    • Edited by Brian Branco Thursday, November 21, 2019 7:49 PM
    Thursday, November 21, 2019 7:46 PM
  • Hi Brian Branco,

    We are glad to hear that they are helpful to you. If you have solved your issue ,please kindly mark the helpful replies as answers. 

    If you get 'invalid object ' , please check your  table if it is existing.  Or please execute the script which is the result of print(@sql), and find where is incorrect.

    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.

    Friday, November 22, 2019 7:07 AM