September 5, 2012 at 5:57 pm
Hello,
I know this has an easy answer, but I can't find it anywhere or figure it out.
I have TableA with a column called ContactID. Some records have values for ContactID and some don't. I want a new table that preserves the existing ContactID values, but assigns new values for the null ContactIDs. Sounds like a job for Identity.
I was able to create TableB with an identity column ContactID and preserve the old values. However, when I try to select out the null ContactIDs and reseed the identity, I get the old "Explicit value must be specified for identity column in table 'TableB' when Identity_insert is set to On or replication user is inserting...." I thought my reseed was assigning an explicit value. Can I even do this hodgepodge way of assigning values? Worse yet, I'd like ContactID to be my primary key on TableB. If you can help, let me know.
SET IDENTITY_INSERT TableB ON
DBCC CHECKIDENT (TableB, RESEED,7563)
INSERT INTO TableB (LastName, FirstName)
SELECT LastName, FirstName FROM TableA
WHERE ContactID IS NULL
SET IDENTITY_INSERT TableB OFF
September 5, 2012 at 6:29 pm
saschup (9/5/2012)
Hello,I know this has an easy answer, but I can't find it anywhere or figure it out.
I have TableA with a column called ContactID. Some records have values for ContactID and some don't. I want a new table that preserves the existing ContactID values, but assigns new values for the null ContactIDs. Sounds like a job for Identity.
I was able to create TableB with an identity column ContactID and preserve the old values. However, when I try to select out the null ContactIDs and reseed the identity, I get the old "Explicit value must be specified for identity column in table 'TableB' when Identity_insert is set to On or replication user is inserting...." I thought my reseed was assigning an explicit value. Can I even do this hodgepodge way of assigning values? Worse yet, I'd like ContactID to be my primary key on TableB. If you can help, let me know.
SET IDENTITY_INSERT TableB ONDBCC CHECKIDENT (TableB, RESEED,7563)
INSERT INTO TableB (LastName, FirstName)
SELECT LastName, FirstName FROM TableA
WHERE ContactID IS NULL
SET IDENTITY_INSERT TableB OFF
First, why are you turning on IDENTITY_INSERT, you aren't inserting any data in the identity column.
I would just do the following:
DBCC CHECKIDENT (TableB, RESEED,7563);
INSERT INTO TableB (LastName, FirstName)
SELECT LastName, FirstName FROM TableA
WHERE ContactID IS NULL;
September 6, 2012 at 8:35 am
Why was I doing an identity_insert? Plain old ignorance! I thought I had to use that in order for the identity column to increment. Thanks for enlightening me -- your idea worked.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply