Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Question related to Identity column Expand / Collapse
Author
Message
Posted Friday, June 6, 2014 1:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 3:19 PM
Points: 115, Visits: 546
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?
Post #1578484
Posted Friday, June 6, 2014 5:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 3:19 PM
Points: 115, Visits: 546
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))
Post #1578550
Posted Saturday, June 7, 2014 5:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:25 PM
Points: 5,238, Visits: 12,150
...Or you could just remove the IDENTITY definition from the target table


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1578573
Posted Saturday, June 7, 2014 2:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 3:19 PM
Points: 115, Visits: 546
Not possible.. You know the importance of an identity column right :)
Post #1578602
Posted Saturday, June 7, 2014 4:06 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:02 PM
Points: 3,072, Visits: 3,184
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,
SQL Server developer at Seavus
www.seavus.com
Post #1578612
Posted Saturday, June 7, 2014 8:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 3:19 PM
Points: 115, Visits: 546
If we reseed the identity to -1 then the 2nd row in the table will have id 0..which is wrong
Post #1578622
Posted Saturday, June 7, 2014 9:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:02 PM
Points: 3,072, Visits: 3,184
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,
SQL Server developer at Seavus
www.seavus.com
Post #1578627
Posted Sunday, June 8, 2014 1:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:25 PM
Points: 5,238, Visits: 12,150
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1578632
Posted Sunday, June 8, 2014 3:15 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:53 AM
Points: 569, Visits: 902
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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1578638
Posted Sunday, June 8, 2014 3:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:27 AM
Points: 2,391, Visits: 6,596
Phil Parkin (6/8/2014)
[quote]The obvious (at least, to me) solution is to not use an identity column as the target.


+1
Post #1578640
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse