<div>
Also using DTS you can do a Database copy and it has the option of inserting identity values. &nbsp;Behind the scenes it does a SET IDENTITY_INSERT ON. &nbsp;
</div>


Also using DTS you can do a Database copy and it has the option of inserting identity values.  Behind the scenes it does a SET IDENTITY_INSERT ON.  

<div>
When inserting columns into a table with IDENTITY_INSERT ON the column list must be specified for the destination table.<br />
<br />
i.e.<br />
<br />
Insert mytable (col1, col2, col3, ...) &nbsp;SELECT * from old_table<br />

</div>


When inserting columns into a table with IDENTITY_INSERT ON the column list must be specified for the destination table.

i.e.

Insert mytable (col1, col2, col3, ...)  SELECT * from old_table


<div>
<div class="content wysiwyg-content">
Hi,<br />
<br />
Ive got two databases on my server. One is the production and the other is development (althoug that is to be changed around).<br />
<br />
The development database has changed alot from the production, therefore i need to extract the data out of the production one and insert it into the development one, taking care with the SQL to put values in all the new fields that have been created etc...<br />
<br />
The problem is that when i use query analyzer to transfer for example, all the data from one users table to another, in the development database each row is given a new unique value. Although normallly this would be fine, i really need to keep the records original ID as other tables all over the database depend on this.<br />
<br />
Is there a method of doing this??<br />
<br />
Thanks in advance<br />
<br />
Al Higgs
</div>
</div>


Hi,

Ive got two databases on my server. One is the production and the other is development (althoug that is to be changed around).

The development database has changed alot from the production, therefore i need to extract the data out of the production one and insert it into the development one, taking care with the SQL to put values in all the new fields that have been created etc...

The problem is that when i use query analyzer to transfer for example, all the data from one users table to another, in the development database each row is given a new unique value. Although normallly this would be fine, i really need to keep the records original ID as other tables all over the database depend on this.

Is there a method of doing this??

Thanks in advance

Al Higgs

Keeping Int Identity values when transferring data between databases

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.