Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: Copying Data Problem with Export data tool

    I Have a problem when copying data from one server to another in Management studio, I need to create and exact copy of the original because of primary key relationships,

    Currently when I export the data the data will run through an insert type statement, which means that all PKs are reissued, rather than being duplicated from the original, How can I be sure that the data will be copied exactly how it is on one server to the other.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    It sounds as though the table that you want to copy the data into, has an identity column assigned to it. For the situation that you have described, there are two solutions available, each depends on how this secondary table will be used.

    If the new table will be used in a transactional environment and it must maintain correct relationships with other tables, then you will need to maintain the same list of primary key values. To do this, we need to understand how your values for the primary key column is generated. I'm assuming a non-composite (single column) key for your system.

    If it's an identity column, then you will want to maintain this property for future inserts directly into the new table, but you will also need to insert existing rows from the current table with the correct ID values. This situation is common and can be solved elegantly using the identity_insert option.

    Using identity_insert, you can override the SQL Server automatic generation process of the column value for the identity column of table, and explicitly supply your own values. Once you have finished inserted these values, you can turn the identity_insert column off, to allow the column to behave normally and generate sequential identity values. You will have to research this particular aspect of behaviour to understand exactly how the identity column will respond after you turn identity_insert back off, and having inserted a random series of values. From experience a few years ago mind you, I don't believe there is any problem here and that SQL Server just resume the identity column counter by adding one to the maximum integer value in the column.

    If on the other hand your table will be used in a more static context, for example bespoke data analysis, then I would suggest creating a table without the identity column. The corresponding column in the new table will have the same data type as the source table and will maintain a foreign key relationship back to the source column to ensure integrity throughout the lifetime of the table use in analysis.

    Using this approach, without the identity column, you can copy the data using a simple multiple row insert operation. A example of this is below:

    Code:
    insert into destinationTable
      columnA,
      columnB,
      columnC
    select
      columnA,
      columnB,
      columnC
    from
      sourceTable
    It's important to remember that there are no restrictions, or very very few, that apply to a select statement when used as the source for a multiple row insert. Therefore, you should not hesitate to use any conditional constructs any other elements of the SQL language to ensure you insert only the data that you want. Often this feature is overlooked and people forget that the select statement need not be a simple one set query.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    I think it is the identity insert option I need to use, I will give it a go in three days when I do the test transfer, then post on the outcome,

    There is the Identity insert option on the Export data tool, Is this the option your talking about? or is there an option in the table properties?

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    There is only one way to apply identity_insert, which is as a table option applicable only to the current session and for the duration of that session or until the option is explicitly turned off.

    I would say that the option to enable identity_insert from within an ETL package is accomplished by the tool transparently issuing the option directly to SQL Server on your session's behalf. In this way, the ETL tool serves as just a GUI to execute SQL DDL and DML.

    Nonetheless, you are correct in your thinking to investigate the identity_insert option. Just remember that column names must be specified when using this option, a requirement that often many people overlook and which can cause unnecessary frustration. This is one area where a tool similar to the one you describe can be helpful, in ensuring little compliance issues like this.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Got it worked a Treat, Use The Export Data tool in Management Studio, Not sure what I did differently to before, but I set the

    Delete Rows in Destination table to true (even though it was empty)
    And Enable Identity insert to true

    Copied all the data as it was, missing all the PKs It had been including, and the PK count after the insert of the data starts at the last record it doesn't fill in the gaps, thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •