Insert into table A data from table B

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 2005 Forums

Transact-SQL (2005)

Insert into table A data from table B

Author

Topic

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2009-09-22 : 13:54:28

I have the following table of locations:
Create table #locations (locId varchar(10), locName varchar(20))
Insert into #locations
values ('1', 'USA')
values ('2', 'Canada')
values ('3', 'UK')

I want to compare this table to my Inflation Rates table:
Create table #inflationRates (locId varchar(10), [year] varchar(4), locName varchar(20), rate decimal)

Insert into #inflationRates
values ('1', '2009','USA', '4')
values ('2', '2009','Canada', '6.3')
values ('1', '2010','USA', '4.2')
values ('2', '2010','Canada', '6.6')

I would like to add the UK to this rates table, but want to add it twice, for each year already listed.

I've started with the following:

INSERT INTO #inflationRates
(locId, [year], locName, locAbbrev, localRate)
SELECT l.locId,'2009', l.locName, l.locAbbrev, 0
FROM #Locations l
WHERE not exists (select * from #inflationRates
where #inflationRates.locId = l.locId);

This will identify the UK as something to add, and I append it for the 2009 year. But then I need to add it for 2010.

The way I see it now, I would have to put each field of the new record into a variable, and then put the variables into the #inflationsRates table for each year that I want to add.

Is there a way to dynamically look at the list of the years that are in the #inflationsRates table, and loop through them to add the new location?

Any help is appreciated!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-09-22 : 16:59:58

Notice that this is a "set based" solution; NO LOOPING!!!


insert into #inflationRates(
	locID,
	[year],
	locName,
	rate
	)
select
	ml.locID,
	y.[year],
	ml.locName,
	0.0		-- This could be NULL or have a DEFAULT constraint on the column
from
	(
	-- Set of missing locations
	select l.locID, l.locName
	from #locations l
	left outer join #inflationRates r
	on r.locname = l.locName
	where r.locname is Null
	) ml
cross join
	(
	-- Set of years
	select distinct [year]
	from #inflationrates
	) y

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2009-09-22 : 17:37:51

I can never get my head around a "set based" solution - I immediately think of loops and then get lost...

Thanks so much for the help - it has made my day :)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-09-22 : 19:10:42

As far as the set based thinking, I find it useful to try to formulate the definition of the set in English (or your own native tongue) and then try to turn that into SQL. If you look at my simple comments in the code, i wanted to find "all of the locations that were missing" (one set) and "all of the years to be entered" (another set) and then defined how they related to each other (all of these with all of those = cross join).

If you find that useful, great; otherwise, party on, Garth - party on, Wayne.

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources