Problem with query in sql 2005

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)

Problem with query in sql 2005

Author

Topic

Giboosh
Starting Member

2 Posts

Posted - 2007-10-24 : 09:19:48

I am fairly new to sql and have been thrown in at the deep end with transfering our old DTS tasks on sql 2000 into SSIS packages on sql 2005. So far I have been able to get most of them working but am currently stumped with one which runs the following query

insert into fds.locationinfo (rgnCode, locLevel1, loclevel2, loclevel3, loclevel4)
select rgnCode, locLevel1, loclevel2, loclevel3, loclevel4 from fds.location
where rgnCode+locLevel1+loclevel2+loclevel3+loclevel4
in
(select rgnCode+locLevel1+loclevel2+loclevel3+loclevel4 from fds.importHWlocationupdate)
and
rgnCode+locLevel1+loclevel2+loclevel3+loclevel4
not in
(select rgnCode+locLevel1+loclevel2+loclevel3+loclevel4 from fds.locationinfo)

This runs correctly on the old server but appears to hang on the new server. I have defragged and updated the indexes and the database is in '90' compatibility.

select rgnCode, locLevel1, loclevel2, loclevel3, loclevel4 from fds.location
where rgnCode+locLevel1+loclevel2+loclevel3+loclevel4
in
(select rgnCode+locLevel1+loclevel2+loclevel3+loclevel4 from fds.importHWlocationupdate)

runs correctly as does

select rgnCode, locLevel1, loclevel2, loclevel3, loclevel4 from fds.location
where rgnCode+locLevel1+loclevel2+loclevel3+loclevel4
not in
(select rgnCode+locLevel1+loclevel2+loclevel3+loclevel4 from fds.locationinfo)

Any suggestions?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-24 : 09:24:18

Why don't you use INNER JOIN + LEFT JOIN or EXISTS + NOT EXISTS ?

INSERT 	INTO fds.locationinfo (rgnCode, locLevel1, loclevel2, loclevel3, loclevel4)
SELECT 	rgnCode, locLevel1, loclevel2, loclevel3, loclevel4 
FROM   	fds.location l
WHERE 	EXISTS
	(
		SELECT	*
		FROM	fds.importHWlocationupdate x
		WHERE	x.rgnCode	= l.rgnCode
		AND	x.locLevel1	= l.locLevel1
		AND	x.loclevel2	= l.loclevel2
		AND	x.loclevel3	= l.loclevel3
		AND	x.loclevel4	= l.loclevel4
	)
AND	NOT EXISTS
	(
		SELECT	*
		FROM	fds.locationinfo x
		WHERE	x.rgnCode	= l.rgnCode
		AND	x.locLevel1	= l.locLevel1
		AND	x.loclevel2	= l.loclevel2
		AND	x.loclevel3	= l.loclevel3
		AND	x.loclevel4	= l.loclevel4
	)

KH
[spoiler]Time is always against us[/spoiler]

Giboosh
Starting Member

2 Posts

Posted - 2007-10-24 : 09:33:39

Many thanks khtan, i've just tried your code and it works perfectly.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 09:57:58

And with INNER JOIN / LEFT JOIN

INSERT		fds.locationinfo
		(
			rgnCode,
			locLevel1,
			loclevel2,
			loclevel3,
			loclevel4
		)
SELECT 		l.rgnCode,
		l.locLevel1,
		l.loclevel2,
		l.loclevel3,
		l.loclevel4 
FROM   		fds.location AS l
INNER JOIN	fds.importHWlocationupdate AS x ON x.rgnCode = l.rgnCode
			AND x.locLevel1	= l.locLevel1
			AND x.loclevel2	= l.loclevel2
			AND x.loclevel3	= l.loclevel3
			AND x.loclevel4	= l.loclevel4
LEFT JOIN	fds.locationinfo AS y ON y.rgnCode = l.rgnCode
			AND y.locLevel1	= l.locLevel1
			AND y.loclevel2	= l.loclevel2
			AND y.loclevel3	= l.loclevel3
			AND y.loclevel4	= l.loclevel4
WHERE		y.rgnCode IS NULL

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-24 : 10:08:06

do make sure that the JOINs is a one to one relationship

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources