Insert rows from multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert rows from multiple tables

Author

Topic

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-07-25 : 15:42:07

I'm trying to insert rows into a table from multiple tables but only those rows that do not already exist in the updating table.


CREATE TABLE #mytable
(
	MyID INT, 
	LName VARCHAR(30),
	StateID INT
)
GO
CREATE TABLE #personid
(
	PersonID INT, 
	FiscalYR CHAR(9)
)
GO
CREATE TABLE #extpersonid
(
	ExtPersonID INT, 
	FiscalYR CHAR(9)
)
GO
CREATE TABLE #personname
(
	NameID INT, 
	LName VARCHAR(30)
)
GO
CREATE TABLE #personstateid
(
	PerStateID INT, 
	StateID INT
)
GO



INSERT INTO #mytable (MyID, LName, StateSID)
SELECT 401771, 'Andrews', 9278354 UNION ALL
SELECT 401882, 'Bonds', 7432833 UNION ALL
SELECT 402353, 'Jones', 5647322 UNION ALL
SELECT 404705, 'Jackson', 3758990 UNION ALL
SELECT 416755, 'Baker', 5485748 UNION ALL
SELECT 428473, 'Smith', 8695372 UNION ALL
SELECT 437564, 'Johnson', 9584733

GO

INSERT INTO #personid (PersonID, FiscalYR)
SELECT 401882, '2009-2010' UNION ALL
SELECT 401882, '2010-2011' UNION ALL
SELECT 401994, '2009-2010' UNION ALL
SELECT 401994, '2010-2011' UNION ALL
SELECT 404705, '2009-2010' UNION ALL
SELECT 404705, '2010-2011' UNION ALL
SELECT 405692, '2009-2010' UNION ALL
SELECT 405692, '2010-2011'

GO

INSERT INTO #extpersonid (ExtPersonID, FiscalYR)
SELECT 400972, '2009-2010' UNION ALL
SELECT 400972, '2010-2011' UNION ALL
SELECT 401994, '2009-2010' UNION ALL
SELECT 401994, '2010-2011' UNION ALL
SELECT 402353, '2009-2010' UNION ALL
SELECT 402353, '2010-2011' UNION ALL
SELECT 404705, '2009-2010' UNION ALL
SELECT 404705, '2010-2011' UNION ALL
SELECT 416755, '2009-2010' UNION ALL
SELECT 416755, '2010-2011'

GO

INSERT INTO #personname (NameID, LName)
SELECT 400972, 'Smith' UNION ALL
SELECT 401771, 'Andrews' UNION ALL
SELECT 401882, 'Bonds' UNION ALL
SELECT 401994, 'James' UNION ALL
SELECT 402353, 'Jones' UNION ALL
SELECT 404705, 'Jackson' UNION ALL
SELECT 405692, 'Lawton' UNION ALL
SELECT 416755, 'Baker' UNION ALL
SELECT 428473, 'Smith' UNION ALL
SELECT 437564, 'Johnson'

GO

INSERT INTO #personstateid (PerStateID, StateID)
SELECT 400972, 9875842 UNION ALL
SELECT 401771, 9278354 UNION ALL
SELECT 401882, 7432833 UNION ALL
SELECT 401994, 2589875 UNION ALL
SELECT 402353, 5647322 UNION ALL
SELECT 404705, 3758990 UNION ALL
SELECT 405692, 6598241 UNION ALL
SELECT 416755, 5485748 UNION ALL
SELECT 428473, 8695372 UNION ALL
SELECT 437564, 9584733

GO

I want to insert those rows that are NOT in #mytable but are in either #personid and/or #extpersonid for FiscalYR = ‘2010-2011’ (NOTE: the same ID can be in BOTH #personid & #extpersonid for the same fiscal year). When exist join to #personname to retrieve LName and join to #personstateid to retrieve StateID.

So my desired results should be:
400972 Smith 9875842
401994 James 2589875
405692 Lawton 6598241
416755 Baker 5485748

Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-25 : 16:28:54

>>416755 Baker 5485748
Isn't this guy in #myTable? So it shouldn't be returned?


select p.nameid, p.lname, s.stateid
from   (
       select PersonID
       from #personid
       where FiscalYr = '2010-2011'
       union all
       select ExtPersonID
       from #extpersonid
       where FiscalYr = '2010-2011'
       except
       select myId
       from   #myTable
       ) d
inner join #personname p
       on p.nameid = d.personid
inner join #personStateid s
       on s.perStateid = d.personid

EDIT:
output:


nameid      lname                          stateid
----------- ------------------------------ -----------
400972      Smith                          9875842
401994      James                          2589875
405692      Lawton                         6598241

Be One with the Optimizer
TG

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-07-25 : 16:52:16

quote:
Originally posted by TG

>>416755 Baker 5485748
Isn't this guy in #myTable? So it shouldn't be returned?

You are correct
>>416755 Baker 5485748
should NOT be returned. My mistake. Thanks for catching that.

Testing your code now.

Thanks.

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-07-25 : 17:38:10

quote:

Originally posted by TG


select p.nameid, p.lname, s.stateid
from   (
       select PersonID
       from #personid
       where FiscalYr = '2010-2011'
       union all
       select ExtPersonID
       from #extpersonid
       where FiscalYr = '2010-2011'
       except
       select myId
       from   #myTable
       ) d
inner join #personname p
       on p.nameid = d.personid
inner join #personStateid s
       on s.perStateid = d.personid

EDIT:
output:


nameid      lname                          stateid
----------- ------------------------------ -----------
400972      Smith                          9875842
401994      James                          2589875
405692      Lawton                         6598241

Be One with the Optimizer
TG

Your code works with the test data I provided but when I run the query against my developement database it appears to be pulling those rows that are in both the #personid and #extpersonid.

I need time to look at BOL & run some more tests. I'll post back tommorrow if I have more questions.

Thanks again for all your help!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-26 : 09:43:51

If you want to distinct the IDs from #person and #extPerson then use UNION instead of UNION ALL

Be One with the Optimizer
TG

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-07-26 : 10:14:33

I figured out what I did wrong.

I needed more columns from #personname & #personstateid to use for my insert into #mytable and I (mistakenly) put them in the select for union all instead of the 'outer' select for d amongst other things.

Anyway once I put the columns I wanted in the correct place in the query it works as needed.

Thanks again for your help, I really appreciate it!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-26 : 10:16:59

You're welcome. Glad you got it.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources