-3

I am importing data from Table X to Table Y, so I am programming the script, so I want the identity for the ID column in Table Y turned off, then I import the data and then turn it back on.

I did as following

SET IDENTITY_INSERT [project] ON;
// Script for inserting Data Comes here
SET IDENTITY_INSERT [project] OFF;

but I get this error message

An explicit value for the identity column in table 'project' can only be specified when a column list is used and IDENTITY_INSERT is ON.

What I have done wrong?

nnmmss
  • 2,850
  • 7
  • 39
  • 67

1 Answers1

3

You need to explicitly specify the columns like 

SET IDENTITY_INSERT TableY  ON
Insert Into TableY (Id, Column1, Column2) Select Id, Column1, Column2 FROM TableX
SET IDENTITY_INSERT TableY  OFF
Janne Matikainen
  • 5,061
  • 15
  • 21
  • //that was not my point, that part i Enter the script – nnmmss Oct 21 '13 at 14:03
  • the insert part doesn't have any problem, it is a script which could work fine if manually set the identity ID Field to No , SET IDENTITY_INSERT TableY ON is problem – nnmmss Oct 21 '13 at 14:07
  • how you have decided it that SET IDENTITY_INSERT TableY ON is problem ?? – Dhaval Oct 21 '13 at 14:08
  • 1
    @nnmmss - You're wrong. That error is thrown by the `INSERT` not the `SET` – Martin Smith Oct 21 '13 at 14:08
  • @nnmmss And just how do you know SET IDENTITY_INSERT TableY ON is the problem. Post your script. Really if you run just that line you get the posted error? – paparazzo Oct 21 '13 at 14:09
  • Check that you are setting the IDENTITY_INSERT to the insert target table, not the source table (project)? – Janne Matikainen Oct 21 '13 at 14:11
  • I got to the table and set Identity of Id Field to No, and run the insert, it works, but if the identity of Id Field is Yes, with SET IDENTITY_INSERT i can not insert any thing – nnmmss Oct 21 '13 at 14:11
  • 2
    **Post your INSERT statement** – Martin Smith Oct 21 '13 at 14:13
  • SET IDENTITY_INSERT dbo.project On; insert into dbo.project select [PrjSubcode] ,[Prjcode] ,[SubCode] ,[DisLimMin] ,[DisLimMax] ,[CableLen] ,[GTag] ,[EndStr] ,[NTag] ,[FTag] ,[CDwgNum] ,[TDwgNum] ,[Line1] ,[Line2] ,dbo.Trans_ChangeRevision(CableRev) ,dbo.Trans_ChangeRevision(TermRev) ,dbo.Trans_ChangeRevision(SubRev) ,[KKS] ,null,null,null,null,null,null,null FROM [sql].[CableTest].[dbo].[tblproject] where [PrjSubcode]=290 SET IDENTITY_INSERT dbo.project OFF; – nnmmss Oct 22 '13 at 04:45
  • Add the columns you want to insert those values and the id column too. Like so "Insert Into dbo.Project (*id column here*, *other columns here*) select (*id column here*, *other columns here*) from..." – Janne Matikainen Oct 22 '13 at 06:17
  • the insert Statement works fine if i go to management Studio and design the Table Project and set the Identity attribute to No. do i think it is not about the insert statement – nnmmss Oct 22 '13 at 07:38
  • If your insert works with the identity set to No, then you obviously have Nullable identity column definition, because you are not inserting values to that column. IF you have the identity set to Yes then you need to specify and insert the value when inserting rows with IDENTITY_INSERT on. Please, go read from MSDN, nothing more we can say to you. – Janne Matikainen Oct 22 '13 at 08:04