Patrick
2/28/2007 9:08:00 PM
Hi Friends,
SQL2005
I am trying to insert into a table with identity column:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BulkMailExportJobLog](
[jobID] [int] IDENTITY(1,1) NOT NULL,
[BulkMailTypeID] [int] NOT NULL,
[startdate] [datetime] NOT NULL,
[enddate] [datetime] NOT NULL,
[insertdate] [datetime] NOT NULL CONSTRAINT
[DF_BulkMailExportJobLog_insertdate] DEFAULT (getdate()),
[status] [int] NOT NULL CONSTRAINT [DF_BulkMailExportJobLog_status]
DEFAULT ((0)),
[filename] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_BulkMailExportJobLog] PRIMARY KEY CLUSTERED
(
[jobID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
using this script :
SET IDENTITY_INSERT dbo.BulkMailExportJobLog ON
go
insert into clsql43.mnfileexchange.dbo.BulkMailExportJobLog
select * from clsql44.mnfileexchange.dbo.BulkMailExportJobLog
where jobid not in (select jobid from
clsql43.mnfileexchange.dbo.BulkMailExportJobLog)
go
but getting this error again :
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table
'clsql43.mnfileexchange.dbo.BulkMailExportJobLog' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
isn't SET IDENTITY_INSERT table ON suposed to take care of this ?
Thanks in advance ,
Pat