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

Mchallinor
Mchallinor used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

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

Update your table names accordingly.

Author

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

Thanks

Author

Commented:
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Muhammad KashifDevelopment Manager

Commented:
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

.
Top Expert 2010

Commented:
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

Commented:
Here are some samples that might help
-- if you just need the result set 
Select 
   Product
,  Reference 
From tbl_2008
UNION ALL
Select 
   Product
,  Reference 
From tbl_2009
 
-- if you need to do something more with the combined set
Select 
  nested0.Product
, nested0.Refrence
from (
	Select 
	   Product
	,  Reference 
	From tbl_2008
	UNION ALL
	Select 
	   Product
	,  Reference 
	From tbl_2009
) as nested0
 
-- if you want to store the combined data into some new table
 
Select 
  nested0.Product
, nested0.Refrence
into tbl_2008_2009
from (
	Select 
	   Product
	,  Reference 
	From tbl_2008
	UNION ALL
	Select 
	   Product
	,  Reference 
	From tbl_2009
) as nested0

Open in new window

Author

Commented:
UNION ALL command was what I was looking for.

Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial