April 5, 2011 at 2:46 pm
I have something like this:
Delete
From PrimaryDatabase..TableA
Output Deleted.PrimaryKey
Into BackupDatabase..TableA
Where 1=1
Is it true that I cannot do an Identity_Insert into my output table?
April 6, 2011 at 11:37 am
Try it this way:
USE BackupDatabase
GO
SET IDENTITY_INSERT TableA ON
DELETE FROM PrimaryDatabase..TableA
OUTPUT Deleted.PrimaryKey
INTO TableA (PrimaryKey)
WHERE 1 = 1
SET IDENTITY_INSERT TableA OFF
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 7, 2011 at 6:20 am
Yep, even adding the Identity_Insert stuff it still throws the error "cannot insert unless specifying Indentity_isnert....."
April 7, 2011 at 7:04 am
You must supply the (PrimaryKey) column list as well ie
SET IDENTITY_INSERT BackupDatabase..TableA ON
DELETE
FROM PrimaryDatabase..TableA
OUTPUT Deleted.PrimaryKey
INTO BackupDatabase..TableA (PrimaryKey)
WHERE 1=1
SET IDENTITY_INSERT BackupDatabase..TableA OFF
Far away is close at hand in the images of elsewhere.
Anon.
April 7, 2011 at 7:38 am
This is the exact script that I ran:
Set Identity_Insert Archive..CorrespondenceReference On
Delete
FromIARTS..CorrespondenceReference
OutputDeleted.CorrespondenceReferenceID,
Deleted.CorrespondenceLogID,
Deleted.CorrespondenceReferenceTypeID,
Deleted.CorrespondenceReferenceValue,
Deleted.CreatedBy,
Deleted.CreatedDate
IntoArchive..CorrespondenceReference
WhereCorrespondenceReferenceID In
(
Selectcr.CorrespondenceReferenceID
FromIARTS..CorrespondenceReference cr
Inner Join Archive..CorrespondenceLog cl On cl.CorrespondenceLogID = cr.CorrespondenceLogID
Left Outer Join Archive..CorrespondenceReference acr On acr.CorrespondenceReferenceID = cr.CorrespondenceReferenceID
Whereacr.CorrespondenceReferenceID IS NULL
)
Set@ReferenceCount = @@RowCount
Set Identity_Insert Archive..CorrespondenceReference Off
An that still produced the error:
"Msg 8101, Level 16, State 1, Line 22
An explicit value for the identity column in table 'Archive..CorrespondenceLog' can only be specified when a column list is used and IDENTITY_INSERT is ON."
Edit: After re-reading your reply, do I just need to specify the columns that I'm inserting into the archive table then? I'll try that.
April 7, 2011 at 7:43 am
gregory.anderson (4/7/2011)
Edit: After re-reading your reply, do I just need to specify the columns that I'm inserting into the archive table then? I'll try that.
Yes.
Change
Into Archive..CorrespondenceReference
To
Into Archive..CorrespondenceReference (CorrespondenceReferenceID,CorrespondenceLogID,CorrespondenceReferenceTypeID,CorrespondenceReferenceValue,CreatedBy,CreatedDate)
Far away is close at hand in the images of elsewhere.
Anon.
April 7, 2011 at 8:08 am
gregory.anderson (4/7/2011)
Yep, even adding the Identity_Insert stuff it still throws the error "cannot insert unless specifying Indentity_isnert....."
David is going to get you there...but you completely missed that I added a column list to your original query which is what is required to maike it work.
opc.three (4/6/2011)
Try it this way:
USE BackupDatabaseGO
SET IDENTITY_INSERT TableA ON
DELETE FROM PrimaryDatabase..TableA
OUTPUT Deleted.PrimaryKey
INTO TableA (PrimaryKey)
WHERE 1 = 1
SET IDENTITY_INSERT TableA OFF
The error message says it all too (posted for better search engine hits):
An explicit value for the identity column in table '%.*ls' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Note: when a column list is used and IDENTITY_INSERT is ON
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 7, 2011 at 8:10 am
Sorry guys, I flew right past the part about specifying the fields for the insert into part...thought I just needed the Deleted fields.
April 7, 2011 at 2:26 pm
Fantastic! It works. Thanks everyone.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply