March 20, 2014 at 12:58 pm
Here's my script:
SET IDENTITY_INSERT SecuritySettings ON
INSERT INTO SecuritySettings
SELECTSecuritySettingsId,
UserGroup,
SecurityNodesId,
Allowed,
SecurityType,
Created,
CreatedBy,
LastModified,
LastModifiedBy
FROM SecuritySettingsBAK;
And I'm getting...
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'SecuritySettings' can only be specified when a column list is used and IDENTITY_INSERT is ON.
I have to be doing something stupid, right? I'm turning IDENTITY_INSERT ON, and I'm not getting any error there. I'm not using SELECT *, I'm specifying a column list. And I'm running this as sa. So what could I be doing wrong?
thanks,
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
March 20, 2014 at 1:04 pm
Just to pre-emptively answer a couple of likely questions...
1. I've confirmed that the column list in that statement matches the complete list in the SecuritySettings table, and in the correct order.
2. SecuritySettingsBAK was created via a SELECT INTO statement from SecuritySettings. I've confirmed all data types are identical.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
March 20, 2014 at 1:04 pm
Please give column names in the insert statement
Like
SET IDENTITY_INSERT SecuritySettings ON
INSERT INTO SecuritySettings ( col1,col2,col3 etc.,)
SELECTSecuritySettingsId,
UserGroup,
SecurityNodesId,
Allowed,
SecurityType,
Created,
CreatedBy,
LastModified,
LastModifiedBy
FROM SecuritySettingsBAK;
March 20, 2014 at 1:05 pm
You have to create a column list, not sure about the name of the fields you're inserting to but it should look like this,
SET IDENTITY_INSERT SecuritySettings ON
GO
INSERT INTO SecuritySettings (
[SecuritySettingsId]
, [UserGroup]
, [SecurityNodesId]
, [Allowed]
, [SecurityType]
, [Created]
, [CreatedBy]
, [LastModified]
, [LastModifiedBy]
)
SELECT SecuritySettingsId
, UserGroup
, SecurityNodesId
, Allowed
, SecurityType
, Created
, CreatedBy
, LastModified
, LastModifiedBy
FROM SecuritySettingsBAK;
March 20, 2014 at 1:06 pm
That was it. I didn't think to specify a column list in that part of the statement. (So yes, I was doing something stupid.) Thank you so much!!!
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply