|
Home > Archive > Microsoft SQL Server forum > August 2005 > Failed insert query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Failed insert query
|
|
| postings@alexshirley.com 2005-08-04, 7:24 am |
| 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_Co
ntacts'. 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 2005-08-04, 7:24 am |
| 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 2005-08-04, 7:24 am |
|
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.droptable.com ***
| |
| postings@alexshirley.com 2005-08-04, 7:24 am |
| 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 2005-08-04, 9:24 am |
| 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@alexshirley.com 2005-08-04, 9:24 am |
| Simon and Chandra
Thank you very much for your help!
Alex
|
|
|
|
|