"John Bell" <jbellnewsposts@hotmail.com> wrote in message
news:3f5343ad$0$250$ed9e5944@reading.news.pipex.ne t...
> Hi
>
> The problems is the GO after SET IDENTITY_INSERT.. ON
>
> I suggest you do this in a stored procedure.

I got another table with an Identity column and this works fine. The GO
doesn't seem to be a problem there (I think they're just put in by Profiler
anyway for the purpose of pasting into QA) and it works OK from QA with the
GOs in place..

A Stored procedure would be difficult as the data is coming from an Access
database although in the case of this particular table it comes from another
SQL database but I did try importing that one to Access and then running the
append query from the Access database. I can look at doing a SP for the ones
that do come from another SQL database (these are new ones added to the
application recently in the re-write) but since I'm on like table 3 out of
60 I'm not that confident the rest will go as smoothly as the first 2.

I'm pasting the troublesome table script below, this may provide a clue,
could be the self join FK that doesn't exist on any table I've transferred
thus far.

--- table script
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[zstblApplicationFunction_zstblApplicationFunction_ FK1]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[zstblApplicationFunction] DROP CONSTRAINT
zstblApplicationFunction_zstblApplicationFunction_ FK1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[zstblApplicationFunction_zstblPermission_FK1]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[zstblPermission] DROP CONSTRAINT
zstblApplicationFunction_zstblPermission_FK1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[zstblApplicationFunction]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[zstblApplicationFunction]
GO

CREATE TABLE [dbo].[zstblApplicationFunction] (
[ApplicationFunctionID] [int] IDENTITY (1, 1) NOT NULL ,
[SortOrder] [int] NULL ,
[ParentApplicationFunctionID] [int] NULL ,
[ApplicationFunctionName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DisplayName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Hyperlink] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FormHelpID] [int] NULL ,
[IsMenuItem] [bit] NOT NULL ,
[IsSubMenuItem] [bit] NULL ,
[IsEnforced] [bit] NOT NULL ,
[SysIsInsert] [bit] NOT NULL ,
[SysIsSelect] [bit] NOT NULL ,
[SysIsUpdate] [bit] NOT NULL ,
[SysIsDelete] [bit] NOT NULL ,
[SysIsExecute] [bit] NOT NULL ,
[SysTimeStamp] [timestamp] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[zstblApplicationFunction] WITH NOCHECK ADD
CONSTRAINT [zstblApplicationFunction_PK] PRIMARY KEY CLUSTERED
(
[ApplicationFunctionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[zstblApplicationFunction] ADD
CONSTRAINT [PrimaryKey_UC1] UNIQUE NONCLUSTERED
(
[ApplicationFunctionName]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IDX_ParentApplicationFunctionID] ON
[dbo].[zstblApplicationFunction]([ParentApplicationFunctionID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [zstblApplicationFunction_AK2] ON
[dbo].[zstblApplicationFunction]([DisplayName]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

ALTER TABLE [dbo].[zstblApplicationFunction] ADD
CONSTRAINT [zstblApplicationFunction_zstblApplicationFunction_ FK1] FOREIGN
KEY
(
[ParentApplicationFunctionID]
) REFERENCES [dbo].[zstblApplicationFunction] (
[ApplicationFunctionID]
),
CONSTRAINT [zstblFormHelp_zstblApplicationFunction_FK1] FOREIGN KEY
(
[FormHelpID]
) REFERENCES [dbo].[zstblFormHelp] (
[FormHelpID]
)
GO