SQL Server Performance Forum – Threads Archive

Insert Bulk records-Constraint Problem

Hello I have 2 database.
DB1 ( with PRIMARY KEY , FOREIGN KEY and Relationships built)
DB2 (No Constraints but contains only Data) Now when i tried to use Bulk Insert from DB2 to DB1 it dont allow me becuase of all the constraints in DB1.
I took a script from DB1 for all Constraints and Run it on DB2 but it gave lot of errors. What is the best and easiest way to do this? Thanks for the help in advance. Regards

Errors means that your constraints in the DB1 are not valid for DB2 data. closely check what are the troubling constraints and drop them —————————————-
http://spaces.msn.com/members/dineshasanka

Hello Dhinesh
thanks for the reply.
What i ideally like is to have the data imported into DB1 with constraints from DB2 without constraints but it is not allowing me to do bulk import becuase of constraints in DB1.So i tried to make DB2 same as DB1 by running the script and errors are interlinked that may take long to fix as it contains 700 tables and error comes in like 200 odd tables.
So i am looking for a simple and easy solution to overcome this problem.

It’s never easy, but it’s a good way to hone your database skills! Create some queries to check what the constraints mean for the data that is in DB2 … #1 This query will tell you whether you need to fix duplicate PK values, before inserting into DB1. - SELECT t1.&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />K_Cols> FROM DB2.dbo.Tbl t1 GROUP BY t1.&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />K_Cols> HAVING COUNT(*) > 1 #2 This query will tell you whether you have any PK values in DB2 that already exist in DB1 – so you cannot insert them as new: SELECT t1.&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />K_Cols> FROM DB2.dbo.Tbl t1 INNER JOIN DB1.dbo.tbl t2 ON T1.&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />K_Cols> = T2.&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />K_Cols> — break the list up if multiple pk_cols #3 If you have an IDENTITY column in DB2, you probably can’t copy the value. If you have other tables that have an FK on this identity column, and you need to insert data from those other tables as well, then you need to lookup the current identity value in the target table in DB1. You need to have a unique (set of) column(s) to do the lookup. #4 This query will tell you whether you have any FK values in DB2 that do not have a matching RK value in DB1. SELECT t2.<FK_Col> FROM DB2.dbo.Tbl t1 LEFT JOIN DB1.dbo.LookupTbl t2 ON T1.<FK_Col> = T2.<RK_Col> WHERE T2.<RK_Col> IS NULL You will have to make an insert statement to add the missing keys, or have a translation table that lists a matching key in DB1 – but in any case you must handle FKs on identity values as described under #3. #5 For any other constraints, you would need to look up the constraint definition, and translate it into a query on your table in DB2 to see if there are any rows that would violate the constraint. #6 Don’t forget the NOT NULL constraints – check whether you have nulls in columns in the source table where the target column is not nullable. #7 When you do an insert, and you specify a column in the target list, then any default constraint on that target column is ignored! So use CASE or ISNULL to handle defaults in your insert queries. #8 I would always check for insert and update triggers on the target tables as well. There is some FK stuff that SQL Server just won’t let you implement in any other way – for instance there is a check for cyclic FK relationships, but it doesn’t look at which columns are actually covered, and so you have to implement the refused FK relationship in triggers. Plus you can raise much friendlier error messages through triggers …

Okay, just saw your reply … With 700 tables to cover, there is no way to make this happen fast. We’ve done this sort of thing for much smaller numbers of tables, and it takes major TIME. You could create some dynamic SQL to loop through the constraints, but I doubt whether you will get very far. You can do the PK and FK constraints that way, tops. All other constraints will have to be done manually. How come the constraints were not implemented in DB2? If it’s a one-time transfer of data from DB2 to DB1, then you either work your way through the mess, or dump it back on the plate of the people who gave you that DB2. If this is supposed to be a recurring transfer of data, then they should really be looking at the way that the data is dumped into DB2, so you don’t need to worry about the constraints (too much). You may find that, with this many tables, the quickest way might be to do it by trial-and-error. But if this is a recurring transfer, and since this is a data-related issue, be prepared to see different errors the next time.

Thanks Adriaan for your wonderful informations.
It is one time Dump and db1 is basically emptied out and going to add all what is in db2.
db2 is without constraints becuase i just exported the data in db1 to db2 so db2 have same table structure but with no constraints and it helped me import data into db2 without considering any constraints problem.everything went ok
and when data and db2 is ready i had a big problem putting those into db1 back.
It is wrong on my part not to consider the constraints before doing this but all too late for me and db1 should be ready by today.
So had no options now but to bulk insert into db1 by some means providing keeping the constraints also intact for it may mess up the front end application it is connected to.

Not sure that I understand what you’re trying to do. (1) DB2 seems to have been created through a script of the whole DB1 database, but without constraints (and perhaps other objects are missing).
(2) You moved all the data out of DB1 into DB2.
(3) Did you delete all data from DB1?
(4) You now want to move the data back from DB2 into DB1. So did you make any changes to the data while it was in DB2?
Did you make any changes to the constraints in DB1 while the data was out? If not, then I can only guess that the constraints had been created in DB1 with the NOCHECK option, or had been disabled when data was entered into the tables. This would suggest that the data was already violating the constraints when it was still in DB1. Why not script out all the missing objects (like the constraints) from DB1, and run the script against DB2? You should see which constraints fail.

DB1 is original with all constraints.
i just used MSSQL DTS to Export all tables from DB1 to DB2.
So when it exports it created tables in DB2 and put all records there in DB2.
Now DB2 have all records same as DB1. Upto this fine. Now DB2 dont had any constraints or foreign or primary key things and it is just a plain tables with data. Now i have bulk inserted other data into DB2 without any hinderance of constraints and that work fine. Now i have DB2 which has all the data.
I removed or emptied all data in DB1 and now want to move DB2 data into that.
So i tried DTS export using Enterprise manager and it failed badly becuase of constraints. So then I realized i have one problem.
Now My plan is to generate a script to have the constraints as script and delete all tables in DB1 and import the data from DB2
and then apply the constraints to new DB1(imported DB2) via Script.But the script poped up errors and i managed to solve
the errors which are possible to solve and some errors i cant really figure it out why it comes.and the no of errors are like 250 odd and it is tough job finding the error for each of them and solving those.
So i wanted a very simple solution to overcome this problem.

Well, this is one of these situations where you learn A LOT about SQL Server. Filter for the data that was added to DB2, and concentrate on that new data. The original data should still be fine (unless you’ve been doing any funny updates on it).

Hello Adriaan
I need one help very badly.
What is the easy way to make a script that will delete all constraints from all tables
ie primary key foreign key relationships etc.(Only to remove the constraints but to keep the data intact in those tables) Thanks and Regards

Don’t. You’re not solving anything by removing the constraints. The problem is in the data that you’ve added new. Concentrate on that.

Thanks Adriaan

]]>