• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 614
  • Last Modified:

Export data Identity Column Problem


I want to export data from my remote server to my local machine.  I want an exact copy.  The problem is that I can export data from remote dbQIIHL to local dbQIIHL but since I have identity colums the export doesn't override the identity column constraint on my local machine, hence my sql queries do not work on the local machine after the export process.

Is there a way to turn off the identity on a database, do the export, and then turn it on again so that the db behaves normally?
1 Solution
... export ... So I assume you are doing something with insert statements to fill the local copy of your database ?

In that case, use this function (from BOL):
SET IDENTITY_INSERT [database_name.[schema_name].] table {ON|OFF}

Turn the setting on, do the import, and turn it off again. It is off by default ... as you experienced :-)

for more info, look here:

Hope this helps ...
Ted BouskillSenior Software DeveloperCommented:
I use SQL Delta from the www.sqldelta.com which has a database difference and copying tool.  It automates the process.
vstackAuthor Commented:
I am using the export wizard in SQL Server 2005
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

... the export wizard creates an SSIS package for you.
Either add two SQL Tasks in the SSIS package, or manually run the SET IDENTITY statement prior to the SSIS package and afterwards.

Hope this helps ...
vstackAuthor Commented:
Yveau, with your help, I am close to the solution.  I managed tio export all data but I had to manually click the allow Identity insert for EVERY table.  Even this did not work until I deselected the Optimize for many tables.

Is their a way to do this so that I don't have to check the 'Allow Identity Insert' for every table?
Sure, you can loop through every table with the cursor included in the code snippet ...

Hope this helps ...

declare @cTbl varchar(50)
declare @cSQL varchar(max)
declare Yveau cursor
select  name
from    dbo.sysobjects
where   type = 'U'
-- make sure to add enough clauses here to ONLY list the tables with IDENTITY columns !!!
open    Yveau
fetch   Yveau
into    @cTbl
while   (@@Error = 0)
and     (@@Fetch_Status = 0)
        set     @cSQL = 'SET IDENTITY_INSERT '+@cTbl+' ON'
        exec    (@cSQL)
        fetch   Yveau
        into    @cTbl
close   Yveau
deallocate Yveau

Open in new window

vstackAuthor Commented:
This works with some extra variation for my problem
This is clearly a bug, yet I don't see it on Microsoft's KnowledgeBase as yet.  Does someone from Microsoft monitor this forum?  How do we escalate this issue to them?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now