Combining multiple query result sets that share common columns into one specified temp table

Question

Currently I am trying to combine three views into one stored procedure for a report. At first I started off using joins for the common data fields but found one of the tables, XFER, had data that was causing duplicate data. So what I thought may be the best practice was to take all three views and insert only the data I need into one temp table. The reasoning for this is each view references a common column order_no and with all three views in one result set I want to distinctly define a result set by the order and not have duplicate values with using joins.

My question: Is it possible to define a temp table with the selected columns from all three result sets and combine them into one table though they share order_no? I have tried two methods:

Create a temp table first hand with all columns I want presented from all three query sets and then INSERT INTO from each query to have a combined result
Do not define a temp table first hand and have the first SELECT INTO the temp table and then the remaining two queries use INSERT INTO

So far after trying both methods I receive the message:

Insert Error: Column name or number of supplied values does not match table definition.

    IF (object_id('tempdb..#TruckSummary') IS NOT NULL)
    BEGIN
        DROP TABLE #TruckSummary
    END

    CREATE TABLE #TruckSummary
    (
        order_no int,
        freight_allow_type varchar(10),
        shipped decimal(20, 8),
        carton_no int,
        number_of_boxes int,
        skid_height_inches int,
        dim_ext_x float,
        dim_ext_y float,
        product_weight float,
        pallet_weight float,
        total_pallet_weight float,
        cubic_feet decimal(10, 4),
        PCF decimal(10, 4),
        routing varchar(20),
         bol_no int,
         ext int,
         ship_to_name varchar(40),
         ship_to_add_3 varchar(40),
         [location] varchar(10),
         cust_code varchar(10),
         date_shipped varchar(20),
         skids int,
         [weight] float,
         BOL_est_freight decimal(10, 2) 
    )




INSERT   INTO #TruckSummary


SELECT 
      O.order_no
    , O.freight_allow_type
    , L.shipped

FROM orders O (NOLOCK)
INNER JOIN ord_list L (NOLOCK)
    ON O.order_no = L.order_no 
       AND O.ext = L.order_ext AND ISNULL(L.void,'') <> 'Y'
INNER JOIN arcust C (NOLOCK)
    ON O.cust_code = C.customer_code
LEFT OUTER JOIN armaster A (NOLOCK)
    ON A.customer_code = O.cust_code
       AND A.ship_to_code = O.ship_to
WHERE O.status < 'S'
       AND ISNULL(O.void,'') <> 'V'
       AND O.date_shipped IS NULL

GROUP BY O.order_no, O.freight_allow_type, L.shipped 




INSERT INTO #TruckSummary

SELECT 
   TCT.order_no,
   TCT.carton_no, 
   TRCB.number_of_boxes, 
   TRCB.skid_height_inches, 
   TPM.dim_ext_x, 
   TPM.dim_ext_y, 
   TCT.weight AS 'product_weight',
   TPM.weight AS 'pallet_weight', 
   TCT.weight + TPM.weight AS 'total_pallet_weight',

   CASE WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND TPM.dim_ext_y > 0
        THEN CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728)
   ELSE 0 END AS 'cubic_feet',

   CASE WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND TPM.dim_ext_y > 0 
     ((TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728))
        THEN CONVERT(DECIMAL(10,4), (TCT.weight + TPM.weight) / ((TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728))
   ELSE 0 END AS 'PCF',
   TCT.last_modified_date,
   TCT.modified_by,
   TCT.order_type
FROM   pltjones.dbo.tdc_carton_tx TCT (NOLOCK)
LEFT OUTER JOIN pltjones.dbo.tdc_revshelf_carton_box TRCB 
   ON TCT.order_no=TRCB.order_no 
      AND TCT.order_ext=TRCB.order_ext 
      AND TCT.carton_no=TRCB.carton_no 
LEFT OUTER JOIN pltjones.dbo.tdc_pkg_master TPM 
   ON TCT.carton_type=TPM.pkg_code
WHERE ISNULL(TCT.date_shipped,GETDATE()) > DATEADD(mm, -6, getdate())




INSERT INTO #TruckSummary

SELECT 
     MAX(O.routing) AS 'routing'
    , MAX(O.user_def_fld9) AS 'bol_no'
    , O.order_no 
    , O.ext 
    , MAX(ship_to_name) AS ship_to_name
    , MAX(ship_to_add_3) AS ship_to_add_3
    , MAX(OL.location) AS 'location'
    , MAX(O.cust_code ) AS 'cust_code'
    , CONVERT(DATETIME, CONVERT(varchar(20) , MAX(O.date_shipped), 23)) AS 'date_shipped'
    , MAX(O.user_def_fld10) AS 'skids'
    , MAX(O.user_def_fld12) AS 'weight'
    , ISNULL(MAX(BH.est_freight_cost),0) AS 'BOL_est_freight'
FROM orders O (NOLOCK)
     INNER JOIN ord_list OL (NOLOCK)
        ON O.order_no = OL.order_no 
             AND O.ext = OL.order_ext
    INNER JOIN ras_bol_details BD (NOLOCK)
        ON O.order_no = BD.bl_src_no 
             AND O.ext = BD.bl_src_ext 
             AND BD.order_type = 'S'
    LEFT OUTER JOIN ras_bol_header BH (NOLOCK)
        ON BH.bl_no = BD.bl_no
WHERE O.date_shipped >= dateadd(dd, -30, getdate())
             AND O.routing NOT LIKE 'FEDX%'
             AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3')
GROUP BY O.order_no, O.ext

UNION

SELECT TOP 100 PERCENT
    BH.routing
    , BD.bl_no
    , X.xfer_no
    , BD.bl_src_ext AS ext
    , X.to_loc_name
    , X.to_loc_addr3
    , X.from_loc
    , 'cust_no'
    , CONVERT(varchar(20) , X.date_shipped, 23)
    , BD.skids
    , BD.tare_wt
    , BH.est_freight_cost
FROM xfers X (NOLOCK)
JOIN xfer_list XL (NOLOCK)
    ON X.xfer_no = XL.xfer_no
JOIN ras_bol_details BD (NOLOCK)
    ON X.xfer_no = BD.bl_src_no 
       AND BD.order_type = 'T'
JOIN ras_bol_header BH (NOLOCK)
    ON BD.bl_no = BH.bl_no
WHERE X.to_loc IN ('KM', 'AWNC', 'KMUT', 'AWAZ', 'SM')
  AND X.date_shipped >  dateadd(dd, -30, getdate())

Andriy M · Accepted Answer · 2017-05-12 22:23:11Z

Your problem is your temp table #TruckSummary has more column than your select statement. You will need to mention the column names (one's in your select statement) in that case.

Following code will give exact error you are getting.

CREATE TABLE [Sales].[Orders](
    [OrderID] [int] NOT NULL,
    [CustomerID] [int] NOT NULL,
    [SalespersonPersonID] [int] NOT NULL,
    [PickedByPersonID] [int] NULL
)

IF (object_id('tempdb..#TruckSummary') IS NOT NULL)
        BEGIN
            DROP TABLE #TruckSummary
        END

        CREATE TABLE #TruckSummary
        (

        [OrderID] [int] NOT NULL,
        [CustomerID] [int] NOT NULL,
        [SalespersonPersonID] [int] NOT NULL,
        [PickedByPersonID] [INT]
        )

    INSERT   INTO #TruckSummary
    SELECT 
          [OrderID] ,
        [CustomerID], 
        [SalespersonPersonID]
    FROM sales.orders O (NOLOCK)
    Where orderid =1

    INSERT INTO #TruckSummary
    SELECT 
          [OrderID] ,
        [CustomerID], 
        [SalespersonPersonID]
    FROM sales.orders O (NOLOCK)
    WHERE orderid =2

Following code will fix the error:

IF (object_id('tempdb..#TruckSummary') IS NOT NULL)
    BEGIN
        DROP TABLE #TruckSummary
    END

    CREATE TABLE #TruckSummary
    (

    [OrderID] [int] NOT NULL,
    [CustomerID] [int] NOT NULL,
    [SalespersonPersonID] [int] NOT NULL,
    [PickedByPersonID] [INT]
    )

INSERT   INTO #TruckSummary
( [OrderID] ,
  [CustomerID], 
  [SalespersonPersonID]
    )
(
SELECT 
    [OrderID] ,
    [CustomerID], 
    [SalespersonPersonID]
FROM sales.orders O (NOLOCK)
Where orderid =1
)
INSERT   INTO #TruckSummary
( [OrderID] ,
  [CustomerID], 
  [SalespersonPersonID]
    )
(
SELECT 
    [OrderID] ,
    [CustomerID], 
    [SalespersonPersonID]
FROM sales.orders O (NOLOCK)
Where orderid =2
)

BradC · Accepted Answer · 2017-05-12 14:13:38Z

Currently I am trying to combine three views into one stored procedure for a report. At first I started off using joins for the common data fields but found one of the tables "XFER" had data that was was causing duplicate data.

The problem, then, is that the XFER table must have multiple rows for each order_no. That's the issue, not whatever method you are using to combine them (JOINs vs VIEWS vs whatever else).

Only you can tell why that's the case, I don't know your data like you do, even though you've described some of the schema.

So depending on why each order_no has multiple corresponding XFER rows, you can try one of the following approaches:

1) Don't join to XFER directly, instead join to a subquery using GROUP BY guaranteed to only return a single row per order, something like (just guessing at some other possible columns):

SELECT order_no, COUNT(*) as xfer_count, MAX(xfer_date) AS last_xfer
FROM XFER
GROUP BY order_no

2) Use a WHERE clause to exclude rows from XFER that aren't what you want. Again, not sure about the schema, but if there is some column that identifies a single "active" row, as opposed to other obsolete rows for the same order_no, then filter on that:

SELECT order_no, ...
FROM XFER
WHERE status = 'Active'

tfenwick11 · Accepted Answer · 2017-05-12 15:00:15Z

Marking my code as the answer in case anyone needs to see the syntax from the previous code of how it should look. @SqlWorldWide did answer the question in the form of was it possible to take the results from three selects into one temp table and his example did work for me. But as a better practice and what I originally wanted to do was defined by @BradC. His answer is a better practice and less cumbersome than utilizing the existing three views and instead utilizing a join method. Thank you for your answers they where both very helpful!

        IF (object_id('tempdb..#TruckSummary') IS NOT NULL)
        BEGIN
            DROP TABLE #TruckSummary
        END

        CREATE TABLE #TruckSummary
        (
            order_no int,
            freight_allow_type varchar(10),
            shipped decimal(20, 8),
            carton_no int,
            number_of_boxes int,
            skid_height_inches int,
            dim_ext_x float,
            dim_ext_y float,
            product_weight float,
            pallet_weight float,
            total_pallet_weight float,
            cubic_feet decimal(10, 4),
            PCF decimal(10, 4),
            routing varchar(20),
            bol_no int,
            ext int,
            ship_to_name varchar(40),
            ship_to_add_3 varchar(40),
            [location] varchar(10),
            cust_code varchar(10),
            Date_shipped varchar(20),
            skids int,
            [weight] float,
            BOL_est_freight decimal(10, 2)  
        )




    INSERT INTO #TruckSummary
    (
      order_no
    , freight_allow_type
    , shipped 
    )
        (
           SELECT 
              O.order_no
            , O.freight_allow_type
            , L.shipped

           FROM orders O (NOLOCK)
           INNER JOIN ord_list L (NOLOCK)
            ON O.order_no = L.order_no 
               AND O.ext = L.order_ext AND ISNULL(L.void,'') <> 'Y'
           INNER JOIN arcust C (NOLOCK)
            ON O.cust_code = C.customer_code
           LEFT OUTER JOIN armaster A (NOLOCK)
            ON A.customer_code = O.cust_code
               AND A.ship_to_code = O.ship_to
           WHERE O.status < 'S'
               AND ISNULL(O.void,'') <> 'V'
               AND O.date_shipped IS NULL

           GROUP BY O.order_no, O.freight_allow_type, L.shipped 
         )   



        INSERT INTO #TruckSummary
    (
                 order_no 
               , carton_no 
               , number_of_boxes 
               , skid_height_inches 
               , dim_ext_x 
               , dim_ext_y 
               , product_weight 
               , pallet_weight 
               , total_pallet_weight
               , cubic_feet 
               , PCF 
    )




          (
            SELECT 
               TCT.order_no,
               TCT.carton_no, 
               TRCB.number_of_boxes, 
               TRCB.skid_height_inches, 
               TPM.dim_ext_x, 
               TPM.dim_ext_y, 
               TCT.weight AS 'product_weight',            
               TPM.weight AS 'pallet_weight', 
               TCT.weight + TPM.weight AS 'total_pallet_weight',

               CASE WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND TPM.dim_ext_y > 0
                    THEN CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728)
               ELSE 0 END AS 'cubic_feet',

               CASE WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND TPM.dim_ext_y > 0 
                    --THEN CONVERT(DECIMAL(10,4), (TCT.weight + 30) / ((TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728))
                    THEN CONVERT(DECIMAL(10,4), (TCT.weight + TPM.weight) / ((TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728))
               ELSE 0 END AS 'PCF'
            FROM   pltjones.dbo.tdc_carton_tx TCT (NOLOCK)
            LEFT OUTER JOIN pltjones.dbo.tdc_revshelf_carton_box TRCB 
               ON TCT.order_no=TRCB.order_no 
                  AND TCT.order_ext=TRCB.order_ext 
                  AND TCT.carton_no=TRCB.carton_no 
            LEFT OUTER JOIN pltjones.dbo.tdc_pkg_master TPM 
               ON TCT.carton_type=TPM.pkg_code
            WHERE ISNULL(TCT.date_shipped,GETDATE()) > DATEADD(mm, -6, getdate())

        )







    INSERT INTO #TruckSummary
    (
              routing 
             , bol_no 
             , order_no
             , ext 
             , ship_to_name 
             , ship_to_add_3 
             , [location] 
             , cust_code 
             --, date_shipped 
             , skids 
             , [weight] 
             , BOL_est_freight 
    )

         (
               SELECT 
                  MAX(O.routing) AS 'routing'
                , MAX(O.user_def_fld9) AS 'bol_no'
                , O.order_no 
                , O.ext 
                , MAX(ship_to_name) AS ship_to_name
                , MAX(ship_to_add_3) AS ship_to_add_3
                , MAX(OL.location) AS 'location'
                , MAX(O.cust_code ) AS 'cust_code'
                    --, CONVERT(DATETIME, CONVERT(varchar(20) , MAX(O.date_shipped), 23)) AS 'date_shipped'
                , MAX(O.user_def_fld10) AS 'skids'
                , MAX(O.user_def_fld12) AS 'weight'
                , ISNULL(MAX(BH.est_freight_cost),0) AS 'BOL_est_freight'
               FROM orders O (NOLOCK)
                    INNER JOIN ord_list OL (NOLOCK)
                    ON O.order_no = OL.order_no 
                         AND O.ext = OL.order_ext
                INNER JOIN ras_bol_details BD (NOLOCK)
                    ON O.order_no = BD.bl_src_no 
                         AND O.ext = BD.bl_src_ext 
                         AND BD.order_type = 'S'
                LEFT OUTER JOIN ras_bol_header BH (NOLOCK)
                    ON BH.bl_no = BD.bl_no
               WHERE O.date_shipped >= dateadd(dd, -30, getdate())
                         AND O.routing NOT LIKE 'FEDX%'
                         AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3')
               GROUP BY O.order_no, O.ext

               UNION

               SELECT TOP 100 PERCENT
                BH.routing
                , BD.bl_no
                , X.xfer_no
                , BD.bl_src_ext AS ext
                , X.to_loc_name
                , X.to_loc_addr3
                , X.from_loc
                , 'cust_no'
                --, CONVERT(varchar(20) , X.date_shipped, 23)
                , BD.skids
                , BD.tare_wt
                , BH.est_freight_cost
               FROM xfers X (NOLOCK)
               JOIN xfer_list XL (NOLOCK)
                ON X.xfer_no = XL.xfer_no
               JOIN ras_bol_details BD (NOLOCK)
                ON X.xfer_no = BD.bl_src_no 
                   AND BD.order_type = 'T'
               JOIN ras_bol_header BH (NOLOCK)
                ON BD.bl_no = BH.bl_no
               WHERE X.to_loc IN ('KM', 'AWNC', 'KMUT', 'AWAZ', 'SM')
                 AND X.date_shipped >  dateadd(dd, -30, getdate())
    )

Stack Exchange Network

Combining multiple query result sets that share common columns into one specified temp table

3 Answers 3

Your Answer

Not the answer you're looking for? Browse other questions tagged
t-sql
sql-server-2000
temporary-tables
or ask your own question.

Hot Network Questions

Combining multiple query result sets that share common columns into one specified temp table

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged t-sqlsql-server-2000temporary-tables or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
t-sql
sql-server-2000
temporary-tables
or ask your own question.