Insert Data error.

adrianw

Hi,

I have created a new SQL Package and after figuring out the options
to enable/disable I have been able to create a new database using SQL Packager.

My issue now is when I attempt to insert the data, the data is not inserted
in the correct order, with tables that require child tables to be populated
first are not being populate before the parent table.

Any idea what is causing this issue is appreciated as I have to manually
troll through the SQL Data script and process.

Cheers
Adrian :?

Daniel Handley

Hi Adrian

SQL Packager does not allow you to select what order the data is inserted into the new database.

However if you have Triggers you can disable them using the data options. Also check that the foreign keys are also disabled.

Regards
Dan

adrianw

Hi Dan,

Thanks for the reply, however I need to have the referential integrity
maintained for the database as this is a key requirement for the
use of SQL Packager for retaining versions of the databases
between different versions.

Is there any way I can keep the referential integrity and have the data
populate in the correct order without having to modify the data load
order in the script and use the C# project?

Cheers
Adrian :?

Daniel Handley

Hi Adrian

Are you copying the data from a database where the integrity is already maintained? If so you shouldn't have a problem as you are performing a bulk insert of data with the integrity from the original included.

If you have new id fields in the target database that ensure integrity, you will have a problem.

Regards
Dan

adrianw

Hi Dan,

Yes I am packaging the database and data from a database that has the
referential integrity already in place and no there are no new ID fields
used when creating the new database (using the script created when
packaging the database).

My problem is the data script does not insert the data in the child tables
before inserting into the parent tables, generating foreigh key constraint
errors when I process the data using either the C# project or the sql script
using SQL Query Analyser.

If you could let me know what (apart from removing the referential integrity) I need to do have the data insert correctly without manual
intervention is appreciated.

Thanks in advance.

Cheers
Adrian :?

Daniel Handley

Hi Adrian

Unfortunately there is no way to select which tables the data is inserted in first. However because you already have integrity from the source database and any triggers and constraints have been turned off the order will not matter.

Can you check that the options are checked to turn off foreign keys, and triggers.

If you are still having problems can you email you package and scripts to support@red-gate.com.

Regards
Dan

adrianw

Hi Dan,

Thanks for the response, however...

I need to keep the triggers, foreign keys and referential integrity
as the package is being used to capture a snap shot of the
database and data for each of the upgrades for the database
and associated application(s).

The intention is to use the package (including schema and data) to
re-create the database in the event we need to roll back to a previous
version without having to the remember all the changes that need
to be reversed.

SQL Server contains the sp_MSdependencies stored procedure which
returns the tables in child to parent order. The following call will return
all the tables in the database in child to parent order.
USE Northwind
GO
EXEC sp_MSdependencies NULL, NULL, 0x008.

Surely this could be used by SQL Packager to determine the order
in which the table population should execute.

Thanks in advance.

Adrian.

Daniel Handley

Hi Adrian

When you use the options to disable the foreign keys and triggers, it will enable them again after the operation.

This means that all of the rules used to keep the integrity in the database will remain intact.

If you wish to have a "snapshot" of the have you thought about using a backup instead. (shameless plug) We have a product called SQL Backup that you could use, and if you need to roll back after changes there is a new product called SQL Log Rescue.

Regards
Dan