February 19, 2009 at 2:16 am
Hi all,
We have two sqlserver databases one is source and one is target. From source we need to pull data from some of the table and truncate the target table and populate new data. Traget and source table have same structure and has foreign constraint. we need to create dynamic package, which truncate and populate data in relational table in sequential. We tried using export and import with creating package but failing in deleting data from table as relation exists. What is best way to achieve this.
Thanks
February 19, 2009 at 2:30 am
If you're doing this regularly and all of the data comes from source db, why not just remove the constraints on the target db?
Or ensure that you truncate in the 'right' order - child tables first.
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 20, 2009 at 7:14 am
What I usually do is remove the constraints, truncate the tables, then add the constraints back on before I load the data (to ensure referential integrity for the new load).
I find it convenient to create three stored procs for this, one to remove the constraints, one to truncate and one to add the constraints back.
These can be useful tools during development if I need to repeatedly wipe out data and reload while I'm testing.
You can use the "script database" task in SSMS to help you build them pretty quickly. Just script the database, select the tables you need, choose to include foreign keys in the scripting action and then copy / paste the portions you need from the resulting code.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply