Competition: What’s your favorite Redgate tool? Enter now.

Insert Data error.

adrianwadrianw Posts: 5
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 :?

Comments

  • 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
    Daniel Handley
    Red Gate Software Ltd
  • 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 :?
  • 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
    Daniel Handley
    Red Gate Software Ltd
  • 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 :?
  • 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 [email protected].

    Regards
    Dan
    Daniel Handley
    Red Gate Software Ltd
  • 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.
  • 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
    Daniel Handley
    Red Gate Software Ltd
This discussion has been closed.