Question related to Identity column
-
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?
-
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))
-
...Or you could just remove the IDENTITY definition from the target table 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Not possible.. You know the importance of an identity column right 🙂
-
Hi
You reseed the identity columns with the following command
dbcc checkident('YourTable',reseed,reseed_value)
you can set the reseed_value = -1 and the identity starts from -1, otherwise it by default starts from 1.
Reference http://technet.microsoft.com/en-us/library/aa933196(v=sql.80).aspx
Igor Micev,My blog: www.igormicev.com
-
If we reseed the identity to -1 then the 2nd row in the table will have id 0..which is wrong
-
rockstar283 (6/7/2014)
If we reseed the identity to -1 then the 2nd row in the table will have id 0..which is wrong..and what is your desire for the second row?
Igor Micev,My blog: www.igormicev.com
-
rockstar283 (6/7/2014)
Not possible.. You know the importance of an identity column right 🙂I know what an IDENTITY column is, of course.
It seems that you are doing ETL from a source table to a target table and wish to preserve the values of source columns which are identities.
The obvious (at least, to me) solution is to not use an identity column as the target. That is based on the assumption that you are feeding transactional (source) data to a non-transactional data warehouse table of some sort (target).
If your target table supports both ETL and transactional processes, my assumption is wrong - and I'm guessing that that is the case here.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Just curious to know why did you design your package like this ?
I meant you could have used a DFT and then an OLEDB source and destination with bulk insert.There also, you have option to keep identity of source.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂 -
Phil Parkin (6/8/2014)
The obvious (at least, to me) solution is to not use an identity column as the target.
+1
😎
-
My objective was to refresh the Test server with the live data, so that we will have some realistic data to test with and it needs to be done with a single button click..to achieve this I made an SSIS package..which has the following components:
1) SMO job: Which wipes out the Dev environment and replicates all the objects(including table,SPs etc) from Prod to Dev
2) Foreach loop container: Using object enumerator, I go through all the tables of Prod one by one
Now in our environment, in the dimension tables, we use a root record(first record) as the record with null values, to handle nulls in fact tables. We have assigned a value of -1 to the ID of this roor record.
2.a) Now, to replicate the same table in Dev, we have kept the ID of Dev table as Identity, but if we reseed it to -1 then the 2nd row will have an ID of 0, which is wrong (2nd row should have an ID of 1). So, to replicate the same table and sync two environments, I went with the solution provided above.
Hope this helps to understand my situation. There can be other ways to achieve this as well, so please share whatever you can come up with.
-
rockstar283 (6/9/2014)
My objective was to refresh the Test server with the live data, so that we will have some realistic data to test with and it needs to be done with a single button click..to achieve this I made an SSIS package..which has the following components:1) SMO job: Which wipes out the Dev environment and replicates all the objects(including table,SPs etc) from Prod to Dev
2) Foreach loop container: Using object enumerator, I go through all the tables of Prod one by one
Now in our environment, in the dimension tables, we use a root record(first record) as the record with null values, to handle nulls in fact tables. We have assigned a value of -1 to the ID of this roor record.
2.a) Now, to replicate the same table in Dev, we have kept the ID of Dev table as Identity, but if we reseed it to -1 then the 2nd row will have an ID of 0, which is wrong (2nd row should have an ID of 1). So, to replicate the same table and sync two environments, I went with the solution provided above.
Hope this helps to understand my situation. There can be other ways to achieve this as well, so please share whatever you can come up with.
IMHO, the simplest way is to restore a backup of production over the dev with replace unless the size is an issue. The second bird you hit with this stone is testing the restore:w00t: In that case, use views to filter the set. Goes somewhat like this, drop the dev db, recreate dev db, create all tables, pull in the data (with identity insert where needed), build constraints and indexes, add users and permissions. Nothing complex and certainly single-button-click doable.
😎
-
IMHO, the simplest way is to restore a backup of production over the dev with replace unless the size is an issue. The second bird you hit with this stone is testing the restore:w00t: In that case, use views to filter the set. Goes somewhat like this, drop the dev db, recreate dev db, create all tables, pull in the data (with identity insert where needed), build constraints and indexes, add users and permissions. Nothing complex and certainly single-button-click doable.
😎
Thats one way of doing it as well..:-)
-
rockstar283 (6/9/2014)
IMHO, the simplest way is to restore a backup of production over the dev with replace unless the size is an issue. The second bird you hit with this stone is testing the restore:w00t: In that case, use views to filter the set. Goes somewhat like this, drop the dev db, recreate dev db, create all tables, pull in the data (with identity insert where needed), build constraints and indexes, add users and permissions. Nothing complex and certainly single-button-click doable.
😎
Thats one way of doing it as well..:-)
Have you looked into using a source control system for this?
😎
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply