"John Bell" <jb************ @hotmail.com> wrote in message
news:3f******** *************@r eading.news.pip ex.net...
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].[zstblApplicatio nFunction_zstbl ApplicationFunc tion_FK1]')
and OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[zstblApplicatio nFunction] DROP CONSTRAINT
zstblApplicatio nFunction_zstbl ApplicationFunc tion_FK1
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[zstblApplicatio nFunction_zstbl Permission_FK1]') and
OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[zstblPermission] DROP CONSTRAINT
zstblApplicatio nFunction_zstbl Permission_FK1
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[zstblApplicatio nFunction]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[zstblApplicatio nFunction]
GO
CREATE TABLE [dbo].[zstblApplicatio nFunction] (
[ApplicationFunc tionID] [int] IDENTITY (1, 1) NOT NULL ,
[SortOrder] [int] NULL ,
[ParentApplicati onFunctionID] [int] NULL ,
[ApplicationFunc tionName] [varchar] (50) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[DisplayName] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL
,
[Hyperlink] [varchar] (512) COLLATE SQL_Latin1_Gene ral_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].[zstblApplicatio nFunction] WITH NOCHECK ADD
CONSTRAINT [zstblApplicatio nFunction_PK] PRIMARY KEY CLUSTERED
(
[ApplicationFunc tionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[zstblApplicatio nFunction] ADD
CONSTRAINT [PrimaryKey_UC1] UNIQUE NONCLUSTERED
(
[ApplicationFunc tionName]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IDX_ParentAppli cationFunctionI D] ON
[dbo].[zstblApplicatio nFunction]([ParentApplicati onFunctionID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [zstblApplicatio nFunction_AK2] ON
[dbo].[zstblApplicatio nFunction]([DisplayName]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
ALTER TABLE [dbo].[zstblApplicatio nFunction] ADD
CONSTRAINT [zstblApplicatio nFunction_zstbl ApplicationFunc tion_FK1] FOREIGN
KEY
(
[ParentApplicati onFunctionID]
) REFERENCES [dbo].[zstblApplicatio nFunction] (
[ApplicationFunc tionID]
),
CONSTRAINT [zstblFormHelp_z stblApplication Function_FK1] FOREIGN KEY
(
[FormHelpID]
) REFERENCES [dbo].[zstblFormHelp] (
[FormHelpID]
)
GO
