duplicating tables, one with foreign key

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

duplicating tables, one with foreign key

Author

Topic

bbxrider
Starting Member

37 Posts

Posted - 2011-08-11 : 14:53:56

I have 2 tables I'm duplicating, a + b to a1 and b1. b has a foreign key constraint for 4 key fields of 5 in a.
I recreated both table structures from the sql generated scripts which included the foreign key for b. I checked the properties of a1 and b1 for the tables and b1's foreign key, they are exactly same as a and b.
I copied contents of a into a1, all 20k+ rows copied ok
then to copy b into b1 I used: (same as the copy from a to a1)
insert into [dbo].[b1] select * from [dbo].[b]
but getting error:
Msg 547, Level 16, State 0, Line 1
INSERT statement conflicted with TABLE FOREIGN KEY constraint 'FK__b1__01'. The conflict occurred in database 'myDB', table 'a1'.
since a and b are already in sync, and I have fully duplicated a into a1, there should be some way to duplicate b into b1 using the 'corresponding' newly built a1???
I need a different insert statement to let sql build the foreign key itself?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-11 : 15:38:27

Possibly the constraint was disabled, data inserted, then enabled.

You can get the data in by

ALTER TABLE b NOCHECK CONSTRAINT [FK_NAME_HERE];

-- insert the data --

ALTER TABLE b CHECK CONSTRAINT [FK_NAME_HERE];

bbxrider
Starting Member

37 Posts

Posted - 2011-08-11 : 17:57:36

ok thanks that worked, however I don't understand why it was necessary to remove the constraint (other than to make it work)
My understand of the FK check restraint is that it has 2 components: ()
1. don't allow a rec to be added to b1 unless those key fields are all there for a rec in a1
2. don't allow a1 recs to be deleted if they are tied to a rec in b1

so for this case all the recs being added to b1 have a valid rec in a1, so why could not they be added when the check constraint was in force? they all should have met the constraint condition. if I was a adding a single new rec to b1 and all the fields matched in a1 that rec should be allowed to be added

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-11 : 19:45:48

Seems at least one record in Table B doesn't have a corresponding record in Table A

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-11 : 19:47:55

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

The quick answer is look for the FK constriant and turn it of untilyou can scrub the data.

The right answer is: Columns are not fields. Your vague narrative sounds like a mag tape processing. Why are you creatign redundancy when the whole idea of RDBMS was to get rid of it?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

bbxrider
Starting Member

37 Posts

Posted - 2011-08-11 : 20:09:58

thanks for the 2nd reply russell, I will have to check all recs, sorry I meant the rows, tomorrow. I did that terrible assume thing, thinking the original tables were built with the constraint in effect so that there would be perfect match for rebuilding. the error message did include:
"The statement has been terminated.
(0 row(s) a"
so it appeared it didn't like anything about the insert statement rather than a specific row
I will post what I find tomorrow

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-12 : 00:04:46

Thanks for the update, let us know what u see bbxrider.

And I apologize for that ridiculous response from Celko...You can safely ignore his bs. He's only here to spam and be an irritation. Honestly, I'm not sure why he hasn't been banned yet.

bbxrider
Starting Member

37 Posts

Posted - 2011-08-12 : 20:36:22

I ran a quick join to test the keys of my original source tables a and b to check if the constraint cols are matched and there was 100% matching
I tried creating a 'flat' version of the table b to use as the source for cols inserted into my new table version b1 but resulted in same error and I duplicated the sp statement that is used by programs to insert new rows into table b but also received the same error, it just doesn't like anything about insert from the query window see below for full text of error.
so I'm baffled for now, will research msg 547 for any further clues
thanks for the heads up on the other post

Msg 547, Level 16, State 0, Line 1
INSERT statement conflicted with TABLE FOREIGN KEY constraint 'FK__table_b1__01'. The conflict occurred in database 'myDB', table 'table_b1'.
The statement has been terminated.
(0 row(s) affected)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-12 : 22:32:21

Did you do the inserts in the correct order?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources