Insert Failing - SQL Server Forums

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)

Insert Failing

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-08-29 : 14:32:26

Hi all I I have a table that Imported from access to sql and I'm trying to insert it into the exsisting SQL table which has the same data as the Access table only the access table has been updated so it has more records in it. When I run my Insert statement it fails. Can anyone help pls

Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TanfActivity_tbl_People_tbls". The conflict occurred in database "SCAIR", table "dbo.People_tbls", column 'Parent ID'.
The statement has been terminated.

SET IDENTITY_INSERT TanfActivity_tbl ON


INSERT INTO TanfActivity_tbl
                      (ID, [Contact Date], [Parent ID], [Type of Contact], [Purpose of Contact], [Referral Date], [Earned hours], [Catagory for hours], [Services Covered], 
                      [State Catagory], [State Services Covered])
SELECT     ID, [Contact Date], [Parent ID], [Type of Contact], [Purpose of Contact], [Referral Date], [Earned hours], [Catagory for hours], [Services Covered], 
                      [State Catagory], [State Services Covered]
FROM         Contact_New
WHERE     (NOT EXISTS
                          (SELECT  ID, [Contact Date], [Parent ID], [Type of Contact], [Purpose of Contact], [Referral Date], [Earned hours], [Catagory for hours], [Services Covered], 
                      [State Catagory], [State Services Covered]
                            FROM        TanfActivity_tbl
                            WHERE      (TanfActivity_tbl.[ID] = Contact_New.[ID])))


SET IDENTITY_INSERT TanfActivity_tbl OFF

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-30 : 08:45:07

It means that there is a value in ParentID in the Contact_New table that is not in the "dbo.People_tbls" table.
SELECT [Parent ID]
FROM Contact_New
WHERE [Parent ID] NOT IN (SELECT [Parent ID] FROM dbo.TanfActivity_tbl)

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-08-30 : 11:37:34

I ran the statement and nothing came up

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-08-30 : 11:46:19

See the Contact_New Table is the same data as the TanfActivity_tbl only the Contact_New Table has been updated so there is more data in it. the TanfActivity_tbl has 7132 Records where the Contact_New has 7685

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-31 : 00:53:35

i think there is foreign key constraint on the table TanfActivity_tbl with references with People_tbls
so first insert the values in table People_tbls( SCAIR) and then insert respective values in the table
TanfActivity_tbl

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-09-01 : 14:34:23

Thank you so much bklr you were right, thank you. that did the trick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources