asked on

Very simple task required to append data from 2 tables and form 1 table

I have two tables with Identical Fields in each.
The first table has old data from 2008 and the second table has data from 2009
All I wish to do is append the data together and returning one table of data.

Simply there are 2 Fields in each Table ( Product and Reference )
So I want to add the Products and References from the 2008 table to the 2009 table. Returning a combined data set.

I assume this is easy, but I don't write SQL very often.

Patrick Matthews

INSERT INTO t_2009 (Product, Reference)
SELECT Product, Reference
FROM t_2008

Update your table names accordingly.

Mchallinor

ASKER

Not exactly what I want, can you please advise how I would return a new table with the combined data?

Thanks

Mchallinor

ASKER

This is my SQL at the moment. Obviously not returning what I need.

I need to use the INSERT INTO command but also output a new table with the combined results.

SELECT BLUE2008.PRODUCTCODE, BLUE2008.REFERENCE_CODE, BLUE2009.PRODUCTCODE, BLUE2009.REFERENCE_CODE
FROM PDLive.MPS_USER.BLUE2008 BLUE2008, PDLive.MPS_USER.BLUE2009 BLUE2009

Open in new window

Muhammad Kashif

Try this

CREATE TABLE #TEMP(PRODUCTCODE2008 INT, REFERENCE_CODE2008 INT, PRODUCTCODE2009 INT, REFERENCE_CODE2009 INT)

INSERT INTO #TEMP
SELECT BLUE2008.PRODUCTCODE, BLUE2008.REFERENCE_CODE, BLUE2009.PRODUCTCODE, BLUE2009.REFERENCE_CODE
FROM PDLive.MPS_USER.BLUE2008 BLUE2008, PDLive.MPS_USER.BLUE2009 BLUE2009

SELECT * FROM #TEMP

.

Patrick Matthews

This script creates a new table that combines the two old ones.

1) If no columns are null, then the records were matched
2) Records in 2008 not matched in 2009 have nulls for the 2009 columns
3) Vice versa for unmatched 2009 records

SELECT b8.PRODUCTCODE AS PRODUCTCODE_2008, b8.REFERENCE_CODE AS REFERENCE_CODE_2008,
      b9.PRODUCTCODE AS PRODUCTCODE_2009, b9.REFERENCE_CODE AS REFERENCE_CODE_2009
INTO CombinedTable
FROM PDLive.MPS_USER.BLUE2008 b8 INNER JOIN
      PDLive.MPS_USER.BLUE2009 b9 ON b8.PRODUCTCODE = b9.PRODUCTCODE AND
            b8.REFERENCE_CODE = b9.REFERENCE_CODE

INSERT INTO CombinedTable (PRODUCTCODE_2008, REFERENCE_CODE_2008, PRODUCTCODE_2009,
      REFERENCE_CODE_2009)
SELECT b8.PRODUCTCODE AS PRODUCTCODE_2008, b8.REFERENCE_CODE AS REFERENCE_CODE_2008,
      NULL AS PRODUCTCODE_2009, NULL AS REFERENCE_CODE_2009
INTO CombinedTable
FROM PDLive.MPS_USER.BLUE2008 b8 LEFT JOIN
      PDLive.MPS_USER.BLUE2009 b9 ON b8.PRODUCTCODE = b9.PRODUCTCODE AND
            b8.REFERENCE_CODE = b9.REFERENCE_CODE
WHERE b9.PRODUCTCODE IS NULL AND b9.REFERENCE_CODE IS NULL

INSERT INTO CombinedTable (PRODUCTCODE_2008, REFERENCE_CODE_2008, PRODUCTCODE_2009,
      REFERENCE_CODE_2009)
SELECT NULL AS PRODUCTCODE_2008, NULL AS REFERENCE_CODE_2008,
      b9.PRODUCTCODE AS PRODUCTCODE_2009, b9.REFERENCE_CODE AS REFERENCE_CODE_2009
INTO CombinedTable
FROM PDLive.MPS_USER.BLUE2008 b8 RIGHT JOIN
      PDLive.MPS_USER.BLUE2009 b9 ON b8.PRODUCTCODE = b9.PRODUCTCODE AND
            b8.REFERENCE_CODE = b9.REFERENCE_CODE
WHERE b8.PRODUCTCODE IS NULL AND b8.REFERENCE_CODE IS NULL

ASKER CERTIFIED SOLUTION

RCM01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mchallinor

ASKER

UNION ALL command was what I was looking for.

Thank you.