Results 1 to 3 of 3
  1. #1
    Join Date
    May 2014
    Posts
    1

    Question Unanswered: Insert rows based on number of distinct values in another table in SQL

    Hello,

    I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
    Example:
    Temp table: (Contains only one field with all distinct values in table 1)
    Days_in_warehouse
    20
    30
    40

    Table 1 :
    PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
    1 10 20
    2 5 30
    3 7 40

    Updated Table 1:
    PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
    1 10 20 20
    1 10 20 30
    1 10 20 40
    2 5 30 20
    2 5 30 30
    2 5 30 40
    3 7 40 20
    3 7 40 30
    3 7 40 40

    Any ideas as to how can I update Table 1 to get desired results?

    Regards,

    moroformat

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @t TABLE (
       [PO#]                        INT         NOT NULL
    ,  [Days_to_travel]             INT         NOT NULL
    ,  [Days_in_warehouse]          INT         NOT NULL
    ,  [Days_in_warehouse_batch]    INT             NULL
    )
    
    INSERT INTO @t ([PO#], [Days_to_travel], [Days_in_warehouse]
    ,  [Days_in_warehouse_batch]) VALUES
       (1, 10, 20, NULL) 
    ,  (2,  5, 30, NULL)
    ,  (3,  7, 40, NULL)
    
    SELECT * FROM @t
    
    ; WITH cte AS
    (
    SELECT DISTINCT Days_in_warehouse
       FROM @t
    )
    INSERT INTO @t ([PO#], [Days_to_travel], [Days_in_warehouse]
    ,  [Days_in_warehouse_batch])
       SELECT [PO#], [Days_to_travel], a.[Days_in_warehouse]
    ,     cte.[Days_in_warehouse]
          FROM @t AS a
    	  CROSS JOIN cte
    
    
    DELETE FROM @t
       WHERE [Days_in_warehouse_batch] IS NULL
    
    SELECT *
       FROM @t
       ORDER BY [PO#], [Days_to_travel], [Days_in_warehouse]
    ,     [Days_in_warehouse_batch]
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums. Why do you hold use in such contempt? Why are you ignorant of SQL and RDBMS basics?

    Please, please learn by rows are nothing like records, that fields are not columns. DO NOT EVER USE # in a data element name!! This screws up ISO standards and makes code non-portable.

    >> have a table with purchase_order_nbr, Days_to_travel, and Days_warehouse fields [sic]. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. <<

    Why do you want mimic a 1950's scratch tape?? You truly have no idea how RDBMS works, do you? You even name the tables with tape drive numbers! Did you ever see “MT01” in the old days? Those are tape drive names, just like your temp tables!

    >> I want a script that will insert all of the values in the days_in_warehouse field [sic] from the temp table into the Days_in_warehouse_batch row in table 1 [useless name!!] by purchase_order_nbr duplicating the purchase order records [sic] until all of the Purchase orders have a record [sic] per distinct value.

    >> Any ideas as to how can I update Table 1 to get desired results? <<

    I can give you a kludge, but you are doing everything wrong. Everything. Really.

    Can you fix this? Do you want to try again?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •