Puzzling "NOT EXISTS" behavior

CASE CLOSED: removing the FROM clause noted below handles the problem ...
I've used "NOT EXISTS" in inserts before and thought I understood how they work, butI'm puzzled why I get three rows from the following sub-query, which I want to use to prevent errors from duplicate keys. Since it gives me three rows, it will actually try to insert three duplicate records and cause a primary-key fault, which is a twin to the very thing I'm trying to avoid. (of course, there are three records already in the table, none having the key of 20050810)
The sub-query is acting like it's using "WHERE DemoDate <> 20050810" instead of a "NOT EXISTS".
Attempts to use this statement causes a termination with no records inserted.
DemoDate is the primary key and an INT field in SQL Server. Four other int columns for this table have default values.
<code>
INSERT INTO DemoStats (DemoDate)
SELECT 20050810 AS Expr1
FROM DemoStats <-- remove this line so the effective table has only one row, when the NOT EXISTS is TRUE
WHERE (NOT EXISTS
(SELECT *
FROM DemoStats
WHERE DemoDate = 20050810))
</code>I read that as saying...
For every row in DemoStats that != 20050810 please insert 20050810
So surely you'd get loads inserts and therefore duplicate key bangs?
Why not simply
<code>
If not exists(xxxxx) insert <yyyy>
</code>
granted it introduces a condition but it's a lot easier to understand your intentions, plus it works ;)

|||I don't think that will work, since this is a web-based app, so someone else could easily insert the record between checking for exists and doing the insert, thus potentially causing the error anyway. This needs to be handled within a single sql statement.
I found the following (and several other examples that maybe suggest that EXISTS should work a little more like I'm thinking). In this case I wondering if "dual" is a reserved word with a special function, which I haven't seen before.
<code>

If you wanted to insert a single record, you could use the following statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);

The use of thedual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.

</code>|||

Edited:
If you really want to use your approach you could do simply like this :
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
WHERE not exists (select * from clients
where clients.client_id = 10345);
without dual.

|||My theory (I'd love to have someone confirm or deny this): I think it's a bug in SQL Server or maybe even SQL specs. As I understand it, EXISTS is supposed to be a single-valued boolean: TRUE ifone or more rows satisfy the sub-query, and FALSE ifno rows satsify the sub-query. I think the bug depends on the fact that the INSERT, the SELECT, and the sub-query all deal with the same table. I have other UPDATES and INSERTS that use NOT EXISTS, and they're working correctly, but they don't have the same table in the INSERT, SELECT, and sub-query. If I'm wrong I need to go back and re-examine them, but need some confidence of knowing what is right.
When I add DISTINCT below it adds just one record (doesn't try to add three identical-key records), so I'm out of trouble, but I don't know why it otherwise tries to insert three records and triggers an error and termination.
<code>
INSERT INTO DemoStats (DemoDate)
SELECT DISTINCT 20050810 AS Expr1
FROM DemoStats
WHERE (NOT EXISTS
(SELECT *
FROM DemoStats
WHERE DemoDate = 20050810))
</code>
|||

BBradshaw wrote:

I don't think that will work, since this is a web-based app, so someone else could easily insert the record between checking for exists and doing the insert,

That's what isolation levels are for or you'll probably get away with just sticking it a transaction.

Puslishing New Article Fail B/C Relationship Exists in Subscriber

Hello,
I add an article to a live publisher. The table to be added has foreign key
relations. At publisher, under "action if there is already an object": Drop
existing object and create a new one. The subscriber database was restored
from the publisher a while ago.
When I sync them (Replicate Schema Changes=True), I got an error at
subscriber saying that the table can not be dropped because the foreign
relationship.
Is there a way make the replication agent drop the relation of the table and
then create it without changing any other articles? Or anyway to avoid this
failure?
“Just don't drop the table” is an easy solution. However, my problem is that
the table schema has been changed since the database restored. So I still
have to apply the table changes to the subscriber.
Thank you in advance for help.
John
Hi John,
I understand that when you added an article to your live publication under
"action if there is already an object": Drop existing object and create a
new one, the following error came out during the synchronization:
the table can not be dropped because the foreign relationship. The
subscriber was restored from the publisher a while ago and the table schema
has been changed since the database restored.
If I have misunderstood, please let me know.
To let me better understand your issue, I would like to confirm with you
the following questions:
1. What is your replication type, snapshot, merge or transactional?
2. Did you mean that the added table existed in the database originally but
just the schema changed?
3. What is the synchronization type of your replication, web
synchronization or not?
4. Did you know which tables were being dropped and the relationships among
those tables and the added table?
5. Why are there some tables being dropped since the added table was not
replicated before?
I really hope that you could describe your environment more detailed so
that I can perform a test to reproduce your issue at my side. Look forward
to your response.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Paul,
Thank you very much. This is a great. Somehow my pre-publish script doesn't
run.
I think this should apply to merger replication although the link article
applies to SQL Server 2005 transactional and snapshot replication. What am I
missing?
System: SQL Server 2005, Merger replication. Please refer my next replying
post to Charles Wang for detailed infomation.
Thank you for help.
John
"Paul Ibison" wrote:

> You could use 2 sp_addscriptexecs to drop and readd the FK. Also to drop the
> FK you could specify @.pre_creation_cmd. Finally, in SQL Server 2005 there is
> a .pre script which should take care of this automatically:
> http://www.replicationanswers.com/PreSnapshotScripts2005.asp.
> HTH,
> Paul Ibison
>
>
|||Charles,
Thank you for response. Sorry for not offering details.
1.SQL 2005 merge
2.Yes. The table to be added is in both databases, just didn't publish before.
3. Try to use both Windows and web. I haven't pass any of them.
4. Yes. PK of this table is FK of the other's and anothe's PK is FK of this.
5. Business requires to he publish the existing table.
Regards,
John
"Charles Wang[MSFT]" wrote:

> Hi John,
> I understand that when you added an article to your live publication under
> "action if there is already an object": Drop existing object and create a
> new one, the following error came out during the synchronization:
> the table can not be dropped because the foreign relationship. The
> subscriber was restored from the publisher a while ago and the table schema
> has been changed since the database restored.
> If I have misunderstood, please let me know.
> To let me better understand your issue, I would like to confirm with you
> the following questions:
> 1. What is your replication type, snapshot, merge or transactional?
> 2. Did you mean that the added table existed in the database originally but
> just the schema changed?
> 3. What is the synchronization type of your replication, web
> synchronization or not?
> 4. Did you know which tables were being dropped and the relationships among
> those tables and the added table?
> 5. Why are there some tables being dropped since the added table was not
> replicated before?
> I really hope that you could describe your environment more detailed so
> that I can perform a test to reproduce your issue at my side. Look forward
> to your response.
> Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
>
>
>
>
|||OK - then I'd use the sp_addscriptexec method. You'd need to use this to
readd the FK anyway, so using it to drop the FK initialially is only a bit
more work.
HTH,
Paul Ibison
|||Hi John,
I am sorry for delaying this response.
Due to the existed foreign key constraint, when you drop the table whose PK
is FK of another table, it could not be dropped. I recommend that you first
run the following statement to drop the existed foreign key relations on
the table:
ALTER TABLE tablename DROP CONSTRAINT <foreign key index name>
After the synchronization, then run ALTER TABLE tablename ADD CONSTRAINT
(column name) REFERENCES <foreign table>(column name) to add the foreign
key.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||It works. Thanks.
I guess my previous pre-sub query failed was because of not enoguh access
right to the snapshot directory.
"Paul Ibison" wrote:

> OK - then I'd use the sp_addscriptexec method. You'd need to use this to
> readd the FK anyway, so using it to drop the FK initialially is only a bit
> more work.
> HTH,
> Paul Ibison
>
|||Hi Charles,
I thought SQL Server should save the relations, drop them, replicate the
schema chages and then restored the relations back from the saved. Please
refer to this:
http://www.replicationanswers.com/PreSnapshotScripts2005.asp.
Thank for response.
John
"Charles Wang[MSFT]" wrote:

> Hi John,
> I am sorry for delaying this response.
> Due to the existed foreign key constraint, when you drop the table whose PK
> is FK of another table, it could not be dropped. I recommend that you first
> run the following statement to drop the existed foreign key relations on
> the table:
> ALTER TABLE tablename DROP CONSTRAINT <foreign key index name>
> After the synchronization, then run ALTER TABLE tablename ADD CONSTRAINT
> (column name) REFERENCES <foreign table>(column name) to add the foreign
> key.
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Hi John,
Thanks for your feedback.
I understand your concerns. You may submit your suggestions to Microsoft
via the following link:
https://connect.microsoft.com/sql
Your suggestiosn will be routed to SQL product team so that this function
may be included in future.
If you have any other questions or concerns, please feel free to let me
know. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====