Failed insert query

[postings]

Hi

I'm trying to port some data from one database table to another
database table on the same server.

This is the query I am using:

----->
INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005 00:00',
ISNULL(Notes,'')
from olddatabase.dbo.contactevents
WHERE Exists (SELECT ContactID FROM newdatabase.dbo.contacts)
<-------

This is the error I'm getting:

----->
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_ContactEvents_Contacts'. The conflict occurred in database
'newdatabase', table 'Contacts', column 'ContactID'.
The statement has been terminated.
<-------

There is a relationship between the contacts table (Primary key
ContactID) and the contactsevent (foreign key ContactID) table. I guess
the error being flagged up here is that some contacts don't exist in
the new database, therefore referential intergretory won't allow it
being copied. I thought I could get around this using:
"WHERE Exists (SELECT ContactID FROM newdatabase.dbo.contacts)"
Note I've also tried:
"WHERE Exists (SELECT * FROM newdatabase.dbo.contacts)"

What am I doing wrong?

Many Thanks!

Alex

[Simon Hayes]

Your subquery always evaluates to TRUE, so it's not filtering the data
- you need to link it to the outer table (see "Correlated Subqueries"
in Books Online):

....
from olddatabase.dbo.contactevents o
WHERE Exists (
SELECT *
FROM newdatabase.dbo.contacts n
where o.ContactID = n.ContactID
)

Simon

[Chandra]

You query return all rows from olddatabase.dbo.contactevents, without
checking for the existance in newdatabase.dbo.contacts.

It aslo not advisible to use exists as it is ineffecient.

you can try this query:

INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005
00:00',ISNULL(Notes,'')
from olddatabase.dbo.contactevents
INNER JOIN newdatabase.dbo.contacts
ON newdatabase.dbo.contacts.ContactID =
olddatabase.dbo.contactevents.ContactID

please let me know if u have any questions

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***

[postings]

Simon thanks...!

I checked books online, thanks for the reference.
Looking at the conditional statement you gave me:

WHERE Exists (
SELECT *
FROM newdatabase.dbo.contacts n
where o.ContactID = n.ContactID
)

... could you please clarify what 'o'' and 'n'' are?

I've tried now tried the below statement, which makes for sense to me
after your advice, unfortunately I still get the same error:

WHERE EXISTS
(SELECT ContactID FROM newdatabase.dbo.contacts
WHERE ContactID IN (SELECT ContactID FROM olddatabase.dbo.contacts))

I guess I still haven't got the hang of it!

Cheers!

Alex

[Simon Hayes]

o and n are table aliases - instead of typing out the full table name
every time, it's easier to use an alias, and it often makes the code
more readable (see "Using Table Aliases" in Books Online). As for your
query, try this:

INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005 00:00',
ISNULL(Notes,'')
from olddatabase.dbo.contactevents o
WHERE EXISTS (
SELECT *
FROM newdatabase.dbo.contacts n
WHERE o.ContactID = n.ContactID
)

Or you may find this clearer:

INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005 00:00',
ISNULL(Notes,'')
from olddatabase.dbo.contactevents
WHERE ContactID IN (
SELECT ContactID
FROM newdatabase.dbo.contacts
)

I suspect that your query is mixing these two forms.

Simon

[postings]

Simon and Chandra

Thank you very much for your help!

Alex

[Hugo Kornelis]

On Thu, 04 Aug 2005 12:17:11 GMT, Chandra wrote:

You query return all rows from olddatabase.dbo.contactevents, without
checking for the existance in newdatabase.dbo.contacts.

It aslo not advisible to use exists as it is ineffecient.
Hi Chandra,

EXISTS inefficient? This is the first time that I hear that. In fact, I
always hear the opposite that it is very efficient since it'll stop
searching as soon as the first match is found, whereas other techniques
have to process all the data.

Can you post a repro script (or point me to one somewhere on the web)
that shows how EXISTS is less efficient than any of it's equivalents?

you can try this query:

INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005
00:00',ISNULL(Notes,'')
from olddatabase.dbo.contactevents
INNER JOIN newdatabase.dbo.contacts
ON newdatabase.dbo.contacts.ContactID =
olddatabase.dbo.contactevents.ContactID

It's highly probably that this query will work, but you can't be totally
sure. As the OP didn't post the DDL for the table, you can't be totally
sure that the join to newdatabase.dbo.contacts will never result in more
than one row. And if it ever does, then your query will either insert
duplicates in newdatabase.dbo.contactevents, or (if a key is properly
declared) result in a primary key violation.

I'd definitely use EXISTS in this case. And I'd change the dates to an
unambiguous format ('20050101' or '2005-01-01T00:00:00').

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)