Question related to Identity column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Question related to Identity column

Author

Topic

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2014-06-06 : 15:45:10

I have a SOURCE table as below in Prod:

SELECT '-1' as PKey, NULL as ID, 'Unknown' as Name
UNION
SELECT '1' as PKey, 01 as ID, 'ABC' as Name
UNION
SELECT '2' as PKey, 02 as ID, 'XYZ' as Name

I want to replicate (move) it to DEV environment. For this I have a Foreach loop container with following three tasks:

1) Identity_Insert ON: Checks if the table has any identity column or not, if Yes, then set it to ON (I have tested this and it works)

2) Script_task_1: This uses the following code to move the data from PROD to DEV

try{
string connectionString =
@"Data Source=Prod_Server;Initial Catalog=Source_DB;Integrated Security=SSPI;";
// get the source data
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
SqlCommand myCommand =
new SqlCommand("SELECT * FROM " + TableName, sourceConnection);
sourceConnection.Open();
SqlDataReader reader = myCommand.ExecuteReader();

// open the destination data
string connectionString1 = @"Data Source=Dev_Server;Initial Catalog=Dest_DB;Integrated Security=SSPI;";

using (SqlConnection destinationConnection =
new SqlConnection(connectionString1))
{
// open the connection
destinationConnection.Open();

using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection.ConnectionString))
{
bulkCopy.BatchSize = 500;
bulkCopy.NotifyAfter = 1000;
bulkCopy.SqlRowsCopied +=
new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
bulkCopy.DestinationTableName = TableName;
bulkCopy.WriteToServer(reader);
}
}
reader.Close();
//MessageBox.Show("Data copied successfully!!");
}
}
catch(Exception E){
Console.WriteLine(E.Message);
}

3) Identity_Insert OFF: Checks if the table has any identity column or not, if Yes, then set it to OFF

After this process..My destination table is looking like this:

SELECT '1' as PKey, NULL as ID, 'Unknown' as Name
UNION
SELECT '2' as PKey, 01 as ID, 'ABC' as Name
UNION
SELECT '3' as PKey, 02 as ID, 'XYZ' as Name

So, it is copying the data properly, but the Identity field for the root record is not replicating. instead of being -1, it is starting at 1.

Does anyone have an idea what am I doing wrong here?

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2014-06-06 : 19:21:07

Solved it..
No need to set identity_insert ON and OFF before bulk copying.
SQLBulkCopyOptions.KeepIdentity takes care of this.
So, following code worked for me:

using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection.ConnectionString,SqlBulkCopyOptions.KeepIdentity))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources