New Insert Query

Neal · May 8th, 2007, 12:54 PM

Hi,

I have two tables (Table1, Table2) containing a 'Year' field (i.e. Values of '1998', '1999', '2000' ..... '2007').
Both are data Source tables.

I have a third (Table3) which I need to update depending on a SELECT DISTINCT YEAR statement form the other two tables. Once I populate Table3 with the Years from Table1, how do I then only add records from Table2 which aren't already contained in Table3.

EXAMPLE

TABLE1
Year
1992
1994
1995
1996

TABLE2
Year
1992
1993
1994
1995
1997

So TABLE3 should contain:
Year
1992
1994
1995
1996
1993
1997

I know the first query is:

Code:

INSERT INTO dbo.TABLE3
(
[Year]
)
SELECT DISTINCT dbo.TABLE1.[YEAR] 
FROM dbo.TABLE1
GROUP BY dbo.TABLE1.[YEAR]

But how do I add the values from Table2?

Thanks in advance,

Neal

A Northern Soul

happygv · May 8th, 2007, 12:59 PM

Is there any reason why you use DISTINCT and still do a GROUP BY

Code:

INSERT INTO dbo.TABLE3
(
[Year]
)
SELECT DISTINCT dbo.TABLE1.[YEAR] 
FROM dbo.TABLE1
GROUP BY dbo.TABLE1.[YEAR]
UNION
SELECT DISTINCT dbo.TABLE2.[YEAR] 
FROM dbo.TABLE2
GROUP BY dbo.TABLE2.[YEAR]

Cheers

_________________________
- Vijay G
Strive for Perfection

jemacc · May 8th, 2007, 01:06 PM

Try using a sub query similar to this one
SELECT [YEAR]
FROM Table3
WHERE NOT EXISTS
   (SELECT *
   FROM table2
   WHERE table2.pkID = table3.pkid)

Jaime E. Maccou

Neal · May 8th, 2007, 01:22 PM

Thanks for the prompt responses, Guys.

Neal

A Northern Soul