Identity column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Identity column

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-01-21 : 10:50:59

I ran this statement


Truncate Table T2pendingClms
Insert into T2pendingClms

select distinct(clms)as clms, area, doc, dist, reg, regionacronym,  [type],  app_rcpdt, max(dib_mvt_seq_num) as maxseqnum

from #T6Test
group by area, doc, reg, regionacronym, dist, [type], clms, app_rcpdt

and got this error message:

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

I added this:


Truncate Table T2pendingClms
Insert into T2pendingClms
SET IDENTITY_INSERT T2pendingClms on

select distinct(clms)as clms, area, doc, dist, reg, regionacronym,  [type],  app_rcpdt, max(dib_mvt_seq_num) as maxseqnum

from #T6Test
group by area, doc, reg, regionacronym, dist, [type], clms, app_rcpdt

now I'm getting

Incorrect syntax near the keyword 'SET'.

What am I doing wrong? Here's my table:


CREATE TABLE [dbo].[T2PendingClms](
	[T2ID] [int] IDENTITY(1,1) NOT NULL,
	[area] [varchar](2) NULL,
	[dist] [char](3) NULL,
	[reg] [char](1) NULL,
	[regionacronym] [char](3) NULL,
	[type] [varchar](1) NOT NULL,
	[clms] [char](6) NOT NULL,
	[app_rcpdt] [smalldatetime] NULL,
	[maxseqnum] [smallint] NULL,
 CONSTRAINT [PK_T2PendingClms] PRIMARY KEY CLUSTERED 
(
	[T2ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-21 : 10:57:35

Truncate Table T2pendingClms
SET IDENTITY_INSERT T2pendingClms on

Insert into T2pendingClms
select distinct(clms)as clms, area, doc, dist, reg, regionacronym, [type], app_rcpdt, max(dib_mvt_seq_num) as maxseqnum
from #T6Test
group by area, doc, reg, regionacronym, dist, [type], clms, app_rcpdt

SET IDENTITY_INSERT T2pendingClms off

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-01-21 : 11:02:30

Thanks I tried that and still getting

Msg 8101, Level 16, State 1, Line 4
An explicit value for the identity column in table 'T2pendingClms' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I don't have an identity column in the temp table #T6Test could that be the problem?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-01-21 : 11:07:19

Okay I realized I had an field listed (doc) in the select statement but I didn't have the column in the T2PendingClms table. So I removed doc from the select list and now I'm getting this:

Explicit value must be specified for identity column in table 'T2PendingClms' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-21 : 11:23:38

Sorry, missed that the first time. Remove the SET IDENTITY_INSERT statements and it should work.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-01-21 : 11:29:20

It's weird I did and still got that error. I got it to work with this:


Truncate Table T2pendingClms

DBCC CHECKIDENT (T2pendingClms, reseed, New_reseed_Value) 

Insert into T2pendingClms
select distinct(clms)as clms, area, doc, dist, reg, regionacronym, [type], app_rcpdt, max(dib_mvt_seq_num) as maxseqnum
from #T6Test
group by area, doc, reg, regionacronym, dist, [type], clms, app_rcpdt

SET IDENTITY_INSERT T2pendingClms off

I got it to go into the table but got this error message:

Parameter 3 is incorrect for this DBCC statement.

When I changed the line to this
DBCC CHECKIDENT (T2pendingClms, reseed, 1)
and reran I got this:

Checking identity information: current identity value 'NULL', current column value '1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(59308 row(s) affected)

The data did go in is this okay?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-21 : 11:40:17

The rowcount suggests it did. You should specify an explicit column list in the INSERT clause INSERT T2PendingClms(col1, col2,...) in any case. Leave the identity column out of that list if you're not using IDENITY_INSERT.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-01-21 : 11:48:30

Okay thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources