Mchallinor
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.
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.
ASKER
Not exactly what I want, can you please advise how I would return a new table with the combined data?
Thanks
Thanks
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.
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
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
.
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
.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
UNION ALL command was what I was looking for.
Thank you.
Thank you.
SELECT Product, Reference
FROM t_2008
Update your table names accordingly.