INSERT with 'not exists' - clause Problem

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

General SQL Server Forums

New to SQL Server Programming

INSERT with 'not exists' - clause Problem

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2010-06-29 : 06:57:45

CALENDAR is a table with the complete calendar set.
BUILD_CALENDAR is a table that is filled with the CALENDAR dates as SQL INSERT Commands are fired through ASP with 2 skalar parameter to define the period of time for which the records should be created.
To avoid that a date is inserted twice I did the following:

INSERT INTO BUILD_CALENDAR([day])
SELECT DATE from dbo.CALENDAR
WHERE NOT EXISTS (Select BUILD_CALENDAR.day from dbo.CALENDAR
INNER JOIN BUILD_CALENDAR ON dbo.CALENDAR.DATE= BUILD_CALENDAR.day)
AND (DATE <= @ENDPERIOD AND DATE >= @STARTPERIOD)

The result is not correct and I can't figure out why.
The records are created only the very first time the command is launched, with an empty table. As soon as the BUILD_CALENDAR table contains ANY records, the NOT EXISTS() expression always seems to be 'false' eg. EXISTS() is 'true' so that no new records are added.

Any suggestions?
Martin

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-29 : 07:09:20

Why cant you use the same like this -

INSERT INTO BUILD_CALENDAR([day])
SELECT DATE from dbo.CALENDAR
WHERE DATE NOT IN ( Select day from BUILD_CALENDAR )
AND ( DATE <= @ENDPERIOD AND DATE >= @STARTPERIOD )

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-29 : 07:15:15

It is because your NOT EXISTS() has NO relation to the outer query.
You should use aliases for this.

No, you're never too old to Yak'n'Roll if you're too young to die.

barnabeck
Posting Yak Master

236 Posts

Posted - 2010-06-29 : 07:15:51

That did it! Thank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-29 : 07:16:55

quote:
That did it! Thank you

OK that is just another solution but you should go to understand why your query isn't working.

No, you're never too old to Yak'n'Roll if you're too young to die.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-29 : 07:31:17

quote:
Originally posted by webfred

OK that is just another solution but you should go to understand why your query isn't working.

No, you're never too old to Yak'n'Roll if you're too young to die.

Could you please reply with quotes to avoid confusions.

Thanks

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-29 : 07:35:56

quote:
Originally posted by vaibhavktiwari83

quote:
Originally posted by webfred

OK that is just another solution but you should go to understand why your query isn't working.

No, you're never too old to Yak'n'Roll if you're too young to die.

Could you please reply with quotes to avoid confusions.

Thanks

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Better now? Do you have a bad day?

No, you're never too old to Yak'n'Roll if you're too young to die.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-29 : 07:55:13

quote:
Originally posted by webfred

Better now? Do you have a bad day?

what???
I am not getting ?

anyways thats better now...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

barnabeck
Posting Yak Master

236 Posts

Posted - 2010-06-30 : 05:12:11

Webfred,
I had simplified the query with the 'NOT EXISTS()- cause' that wasn't working properly to make that post easier to comprehend. And it actually contained the aliases, as the two tables are running on different servers. Now as I tried to rewrite the query the way I had it yesterday in order to post it again, it suddenly works perfectly with the NOT EXISTS() expression. Must have been some stupid error somewhere.

Thanks to both of you.

PS. I like Vaibhav's quote a lot!

INSERT INTO ESSBP1S9501.DepTec.dbo.PERSONAL ([dia], [horas_sep], [horas_temp], [horas_pres], [horas_torn])
SELECT TRANSDATE, '3000', '2000', '1000', '500'
from dbo.WORKCALENDARDATE
WHERE NOT EXISTS
(Select DepTec_PERSONAL.dia from WIKADAX.AxProd.dbo.WORKCALENDARDATE INNER JOIN ESSBP1S9501.DepTec.dbo.PERSONAL AS DepTec_PERSONAL ON dbo.WORKCALENDARDATE.TRANSDATE=DepTec_PERSONAL.dia)
AND (TRANSDATE <= '2010-08-10' AND TRANSDATE >= '2010-06-16' AND CALENDARID='WIKA')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-30 : 05:45:47

No need to JOIN WorkCalendarDate twice either. See this rewrite

INSERT	ESSBP1S9501.DepTec.dbo.PERSONAL
	(
		[dia],
		[horas_sep],
		[horas_temp],
		[horas_pres],
		[horas_torn]
	) 
SELECT	wcd.TRANSDATE,
	'3000',
	'2000',
	'1000',
	'500' 
FROM	WIKADAX.AxProd.dbo.WORKCALENDARDATE AS wcd
WHERE	NOT EXISTS	(
				SELECT	*
				FROM	ESSBP1S9501.DepTec.dbo.PERSONAL AS p ON p.dia = wcd.TRANSDATE
				WHERE	p.dia BETWEEN '2010-06-16' AND '2010-08-10'
			)
	AND wcd.TRANSDATE BETWEEN '2010-06-16' AND '2010-08-10'
	AND wcd.CALENDARID = 'WIKA'

Or, if you are using SQL Server 2008, try the MERGE command

MERGE	ESSBP1S9501.DepTec.dbo.PERSONAL AS tgt
USING	(
		SELECT	TRANSDATE AS dia,
			3000 AS [horas_sep],
			2000 AS [horas_temp],
			1000 AS [horas_pres],
			500 AS [horas_torn]
		FROM	WIKADAX.AxProd.dbo.WORKCALENDARDATE
		WHERE	TRANSDATE BETWEEN '20100616' AND '20100810'
			AND CALENDARID = 'WIKA'
	) AS src ON src.dia = tgt.dia
WHEN	NOT MATCHED BY TARGET
		INSERT	(
				dia,
				[horas_sep],
				[horas_temp],
				[horas_pres],
				[horas_torn]
			)
		VALUES	(
				src.dia,
				src.[horas_sep],
				src.[horas_temp],
				src.[horas_pres],
				src.[horas_torn]
			);

N 56°04'39.26"
E 12°55'05.63"

barnabeck
Posting Yak Master

236 Posts

Posted - 2010-06-30 : 06:13:54

Cool... but I have to admit that this is making me a bit dizzy. Now I got 3 working solutions.

You guys evidently think in SQL and you got the code deeply interiorized in your neuronal activity... I stick to the basic commands and little by little expand the power of SQL, with the help of guys like you. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources