I want to populate ULF with all the records from ULF_Backup in my License
database.

I can't get around this error, "OPTIMIZER LOCK HINTS"

Here is the SQL code I'm running from Query Analyzer.

sp_dbcmptlevel 'license', 70
GO
SET IDENTITY_INSERT ULF ON
INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
STATUS_DATE)
SELECT * FROM ULF_Backup (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
LICENSE_STATUS, STATUS_DATE)
WITH (NO LOCK)
SET IDENTITY_INSERT ULF OFF

Pull out the select statement by itself and try to execute it.  Before you
do that, do you see anything wrong with the sytax you are using?  Hint -
there is something very wrong.

The SELECT statement column was misplaced.  Also, there was an extraneous
space in the NOLOCK hint.  Try:

SET IDENTITY_INSERT ULF ON
GO
INSERT INTO ULF (
    SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
    ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
    HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
    HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
    DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE)
SELECT
    SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
    ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
    HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
    HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
    DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE
FROM ULF_Backup WITH (NOLOCK)
GO
SET IDENTITY_INSERT ULF OFF
GO
Show quoteHide quote

I get the following error message when I run your code.  Why and how do I
correct this error?

Server: Msg 545, Level 16, State 1, Line 1
Explicit value must be specified for identity column in table 'ULF' when
IDENTITY_INSERT is set to ON.

Show quoteHide quote

I don't get an error.  Can you post your CREATE TABLE statements?

Show quoteHide quote

The code is long because I created a script of the table.  Here is the script.


CREATE TABLE [dbo].[ULF] (
    [ULF_KEY] [Surrogate_Key] IDENTITY (1, 1) NOT NULL ,
    [SPORTSMAN_KEY] [int] NULL ,
    [LICENSE_YEAR] [smallint] NULL ,
    [LICENSE_NUM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CONFIRMATION] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ULF_FORM_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [BOOK_FORM_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BOOK_NUM] [int] NULL ,
    [DEALER_BATCH_KEY] [int] NULL ,
    [HARVEST_FLAG] [tinyint] NULL ,
    [AMOUNT] [money] NULL ,
    [COUNTY_OF_DEALER] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HARVEST_DATE] [datetime] NULL ,
    [HARVEST_TIME] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TRANSACTION_DATE] [datetime] NULL ,
    [NO_PENALTY_FLAG] [tinyint] NULL ,
    [BP_CONFIRM_NUM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DUPLICATION_DATE] [datetime] NULL ,
    [LICENSE_STATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [STATUS_DATE] [datetime] NOT NULL ,
    [Lchg_Userid] [Last_Chg_Userid] NOT NULL ,
    [Lchg_Date] [Last_Chg_Date] NOT NULL
) ON [PRIMARY]
GO

CREATE  CLUSTERED  INDEX [XIF418ULF] ON [dbo].[ULF]([BOOK_FORM_TYPE],
[BOOK_NUM]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[ULF] ADD
    CONSTRAINT [DF__ULF__Lchg_Userid__789EE131] DEFAULT (user_name()) FOR
[Lchg_Userid],
    CONSTRAINT [DF__ULF__Lchg_Date__7993056A] DEFAULT (getdate()) FOR
[Lchg_Date],
    CONSTRAINT [PK__ULF__7A8729A3] PRIMARY KEY  NONCLUSTERED
    (
        [ULF_KEY]
    ) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO

CREATE  UNIQUE  INDEX [Num_Form_Type_Idx] ON [dbo].[ULF]([LICENSE_NUM],
[ULF_FORM_TYPE], [LICENSE_STATUS]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE  INDEX [XIF332ULF] ON [dbo].[ULF]([SPORTSMAN_KEY]) WITH  FILLFACTOR
= 90 ON [PRIMARY]
GO

CREATE  INDEX [XIF349ULF] ON [dbo].[ULF]([LICENSE_STATUS], [AMOUNT]) WITH 
FILLFACTOR = 90,  PAD_INDEX  ON [PRIMARY]
GO

CREATE  INDEX [XIF455ULF] ON [dbo].[ULF]([DEALER_BATCH_KEY]) WITH 
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE  INDEX [XIF421ULF] ON [dbo].[ULF]([COUNTY_OF_DEALER]) WITH 
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE  INDEX [XIF432ULF] ON [dbo].[ULF]([LICENSE_YEAR]) WITH  FILLFACTOR =
90 ON [PRIMARY]
GO

CREATE  INDEX [XIF440ULF] ON [dbo].[ULF]([ULF_FORM_TYPE]) WITH  FILLFACTOR
= 90,  PAD_INDEX  ON [PRIMARY]
GO

CREATE  INDEX [ULF_Num_Idx] ON [dbo].[ULF]([LICENSE_NUM]) WITH  FILLFACTOR
= 90 ON [PRIMARY]
GO

CREATE  INDEX [IX_ULF] ON [dbo].[ULF]([TRANSACTION_DATE]) WITH  FILLFACTOR
= 90 ON [PRIMARY]
GO

CREATE  INDEX [Index_BookNum] ON [dbo].[ULF]([BOOK_NUM]) WITH  FILLFACTOR =
90 ON [PRIMARY]
GO

setuser
GO

EXEC sp_bindrule N'[dbo].[ZERO_ONE]', N'[ULF].[HARVEST_FLAG]'
GO

EXEC sp_bindrule N'[dbo].[ZERO_ONE]', N'[ULF].[NO_PENALTY_FLAG]'
GO

setuser
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


create trigger tD_ULF on dbo.ULF for DELETE as
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* DELETE trigger on ULF */
begin
  declare  @errno   int,
           @errmsg  varchar(255)
    /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
    /* ULF R/461 DEALER_TRAN ON PARENT DELETE RESTRICT */
    if exists (
      select * from deleted,DEALER_TRAN
      where
        /*  %JoinFKPK(DEALER_TRAN,deleted," = "," and") */
        DEALER_TRAN.LOST_ULF_KEY = deleted.ULF_KEY
    )
    begin
      select @errno  = 30001,
             @errmsg = 'Cannot DELETE ULF because DEALER_TRAN exists.'
      goto error
    end

    /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
    /* ULF R/451 REVENUE_DETAIL ON PARENT DELETE RESTRICT */
    if exists (
      select * from deleted,REVENUE_DETAIL
      where
        /*  %JoinFKPK(REVENUE_DETAIL,deleted," = "," and") */
        REVENUE_DETAIL.ULF_KEY = deleted.ULF_KEY
    )
    begin
      select @errno  = 30001,
             @errmsg = 'Cannot DELETE ULF because REVENUE_DETAIL exists.'
      goto error
    end

    /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
    /* ULF R/333 ULF_QUESTIONS ON PARENT DELETE RESTRICT */
    if exists (
      select * from deleted,ULF_QUESTIONS
      where
        /*  %JoinFKPK(ULF_QUESTIONS,deleted," = "," and") */
        ULF_QUESTIONS.ULF_KEY = deleted.ULF_KEY
    )
    begin
      select @errno  = 30001,
             @errmsg = 'Cannot DELETE ULF because ULF_QUESTIONS exists.'
      goto error
    end

    /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
    /* ULF R/4 ULF_PRIVILEGES ON PARENT DELETE RESTRICT */
    if exists (
      select * from deleted,ULF_PRIVILEGES
      where
        /*  %JoinFKPK(ULF_PRIVILEGES,deleted," = "," and") */
        ULF_PRIVILEGES.ULF_KEY = deleted.ULF_KEY
    )
    begin
      select @errno  = 30001,
             @errmsg = 'Cannot DELETE ULF because ULF_PRIVILEGES exists.'
      goto error
    end

    /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
    /* ULF R/335 HARVEST_TOTALS ON PARENT DELETE RESTRICT */
    if exists (
      select * from deleted,HARVEST_TOTALS
      where
        /*  %JoinFKPK(HARVEST_TOTALS,deleted," = "," and") */
        HARVEST_TOTALS.ULF_KEY = deleted.ULF_KEY
    )
    begin
      select @errno  = 30001,
             @errmsg = 'Cannot DELETE ULF because HARVEST_TOTALS exists.'
      goto error
    end


    /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
    return
error:
    raiserror @errno @errmsg
    rollback transaction
end


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


create trigger tI_ULF on dbo.ULF for INSERT as
/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* INSERT trigger on ULF */
begin
  declare  @numrows int,
           @nullcnt int,
           @validcnt int,
           @errno   int,
           @errmsg  varchar(255)

/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* Update Last Change UserID and Last Change Date */
UPDATE ULF
     SET lchg_date = GETDATE(),
    lchg_userid = CURRENT_USER
    FROM ULF, inserted
    WHERE ULF.ULF_KEY = inserted.ULF_KEY

  select @numrows = @@rowcount
  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* ULF_FORM_TYPE R/440 ULF ON CHILD INSERT RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(ULF_FORM_TYPE)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,ULF_FORM_TYPE
        where
          /* %JoinFKPK(inserted,ULF_FORM_TYPE) */
          inserted.ULF_FORM_TYPE = ULF_FORM_TYPE.ULF_FORM_TYPE
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from
inserted where"," and") */

    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30002,
             @errmsg = 'Cannot INSERT ULF because ULF_FORM_TYPE does not
exist.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* LICENSE_YEAR R/432 ULF ON CHILD INSERT RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(LICENSE_YEAR)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,LICENSE_YEAR
        where
          /* %JoinFKPK(inserted,LICENSE_YEAR) */
          inserted.LICENSE_YEAR = LICENSE_YEAR.LICENSE_YEAR
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from
inserted where"," and") */
    select @nullcnt = count(*) from inserted where
      inserted.LICENSE_YEAR is null
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30002,
             @errmsg = 'Cannot INSERT ULF because LICENSE_YEAR does not
exist.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* COUNTY R/421 ULF ON CHILD INSERT RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(COUNTY_OF_DEALER)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,COUNTY
        where
          /* %JoinFKPK(inserted,COUNTY) */
          inserted.COUNTY_OF_DEALER = COUNTY.COUNTY_CODE
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from
inserted where"," and") */
    select @nullcnt = count(*) from inserted where
      inserted.COUNTY_OF_DEALER is null
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30002,
             @errmsg = 'Cannot INSERT ULF because COUNTY does not exist.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* DEALER_BATCH R/455 ULF ON CHILD INSERT RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(DEALER_BATCH_KEY)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,DEALER_BATCH
        where
          /* %JoinFKPK(inserted,DEALER_BATCH) */
          inserted.DEALER_BATCH_KEY = DEALER_BATCH.DEALER_BATCH_KEY
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from
inserted where"," and") */
    select @nullcnt = count(*) from inserted where
      inserted.DEALER_BATCH_KEY is null
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30002,
             @errmsg = 'Cannot INSERT ULF because DEALER_BATCH does not
exist.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* LICENSE_BOOK R/418 ULF ON CHILD INSERT RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(BOOK_FORM_TYPE) or
    update(BOOK_NUM)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,LICENSE_BOOK
        where
          /* %JoinFKPK(inserted,LICENSE_BOOK) */
          inserted.BOOK_FORM_TYPE = LICENSE_BOOK.BOOK_FORM_TYPE and
          inserted.BOOK_NUM = LICENSE_BOOK.BOOK_NUM
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from
inserted where"," and") */
    select @nullcnt = count(*) from inserted where
      inserted.BOOK_FORM_TYPE is null and
      inserted.BOOK_NUM is null
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30002,
             @errmsg = 'Cannot INSERT ULF because LICENSE_BOOK does not
exist.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* LICENSE_STATUS R/349 ULF ON CHILD INSERT RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(LICENSE_STATUS)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,LICENSE_STATUS
        where
          /* %JoinFKPK(inserted,LICENSE_STATUS) */
          inserted.LICENSE_STATUS = LICENSE_STATUS.LICENSE_STATUS
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from
inserted where"," and") */

    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30002,
             @errmsg = 'Cannot INSERT ULF because LICENSE_STATUS does not
exist.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* SPORTSMAN R/332 ULF ON CHILD INSERT RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(SPORTSMAN_KEY)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,SPORTSMAN
        where
          /* %JoinFKPK(inserted,SPORTSMAN) */
          inserted.SPORTSMAN_KEY = SPORTSMAN.SPORTSMAN_KEY
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from
inserted where"," and") */
    select @nullcnt = count(*) from inserted where
      inserted.SPORTSMAN_KEY is null
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30002,
             @errmsg = 'Cannot INSERT ULF because SPORTSMAN does not exist.'
      goto error
    end
  end


  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  return
error:
    raiserror @errno @errmsg
    rollback transaction
end


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


create trigger tU_ULF on dbo.ULF for UPDATE as
/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* UPDATE trigger on ULF */
begin
  declare  @numrows int,
           @nullcnt int,
           @validcnt int,
           @insULF_KEY Surrogate_Key,
           @errno   int,
           @errmsg  varchar(255)

/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* Update Last Change UserID and Last Change Date */
UPDATE ULF
     SET lchg_date = GETDATE(),
    lchg_userid = CURRENT_USER
    FROM ULF, inserted
    WHERE ULF.ULF_KEY = inserted.ULF_KEY

select @numrows = @@rowcount
  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* ULF R/461 DEALER_TRAN ON PARENT UPDATE RESTRICT */
  if
    /* %ParentPK(" or",update) */
    update(ULF_KEY)
  begin
    if exists (
      select * from deleted,DEALER_TRAN
      where
        /*  %JoinFKPK(DEALER_TRAN,deleted," = "," and") */
        DEALER_TRAN.LOST_ULF_KEY = deleted.ULF_KEY
    )
    begin
      select @errno  = 30005,
             @errmsg = 'Cannot UPDATE ULF because DEALER_TRAN exists.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* ULF R/451 REVENUE_DETAIL ON PARENT UPDATE RESTRICT */
  if
    /* %ParentPK(" or",update) */
    update(ULF_KEY)
  begin
    if exists (
      select * from deleted,REVENUE_DETAIL
      where
        /*  %JoinFKPK(REVENUE_DETAIL,deleted," = "," and") */
        REVENUE_DETAIL.ULF_KEY = deleted.ULF_KEY
    )
    begin
      select @errno  = 30005,
             @errmsg = 'Cannot UPDATE ULF because REVENUE_DETAIL exists.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* ULF R/333 ULF_QUESTIONS ON PARENT UPDATE RESTRICT */
  if
    /* %ParentPK(" or",update) */
    update(ULF_KEY)
  begin
    if exists (
      select * from deleted,ULF_QUESTIONS
      where
        /*  %JoinFKPK(ULF_QUESTIONS,deleted," = "," and") */
        ULF_QUESTIONS.ULF_KEY = deleted.ULF_KEY
    )
    begin
      select @errno  = 30005,
             @errmsg = 'Cannot UPDATE ULF because ULF_QUESTIONS exists.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* ULF R/4 ULF_PRIVILEGES ON PARENT UPDATE RESTRICT */
  if
    /* %ParentPK(" or",update) */
    update(ULF_KEY)
  begin
    if exists (
      select * from deleted,ULF_PRIVILEGES
      where
        /*  %JoinFKPK(ULF_PRIVILEGES,deleted," = "," and") */
        ULF_PRIVILEGES.ULF_KEY = deleted.ULF_KEY
    )
    begin
      select @errno  = 30005,
             @errmsg = 'Cannot UPDATE ULF because ULF_PRIVILEGES exists.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* ULF R/335 HARVEST_TOTALS ON PARENT UPDATE RESTRICT */
  if
    /* %ParentPK(" or",update) */
    update(ULF_KEY)
  begin
    if exists (
      select * from deleted,HARVEST_TOTALS
      where
        /*  %JoinFKPK(HARVEST_TOTALS,deleted," = "," and") */
        HARVEST_TOTALS.ULF_KEY = deleted.ULF_KEY
    )
    begin
      select @errno  = 30005,
             @errmsg = 'Cannot UPDATE ULF because HARVEST_TOTALS exists.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* ULF_FORM_TYPE R/440 ULF ON CHILD UPDATE RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(ULF_FORM_TYPE)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,ULF_FORM_TYPE
        where
          /* %JoinFKPK(inserted,ULF_FORM_TYPE) */
          inserted.ULF_FORM_TYPE = ULF_FORM_TYPE.ULF_FORM_TYPE
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from
inserted where"," and") */

    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30007,
             @errmsg = 'Cannot UPDATE ULF because ULF_FORM_TYPE does not
exist.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */

  /* LICENSE_YEAR R/432 ULF ON CHILD UPDATE RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(LICENSE_YEAR)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,LICENSE_YEAR
        where
          /* %JoinFKPK(inserted,LICENSE_YEAR) */
          inserted.LICENSE_YEAR = LICENSE_YEAR.LICENSE_YEAR
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from
inserted where"," and") */
    select @nullcnt = count(*) from inserted where
      inserted.LICENSE_YEAR is null
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30007,
             @errmsg = 'Cannot UPDATE ULF because LICENSE_YEAR does not
exist.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* COUNTY R/421 ULF ON CHILD UPDATE RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(COUNTY_OF_DEALER)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,COUNTY
        where
          /* %JoinFKPK(inserted,COUNTY) */
          inserted.COUNTY_OF_DEALER = COUNTY.COUNTY_CODE
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from
inserted where"," and") */
    select @nullcnt = count(*) from inserted where
      inserted.COUNTY_OF_DEALER is null
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30007,
             @errmsg = 'Cannot UPDATE ULF because COUNTY does not exist.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* DEALER_BATCH R/455 ULF ON CHILD UPDATE RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(DEALER_BATCH_KEY)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,DEALER_BATCH
        where
          /* %JoinFKPK(inserted,DEALER_BATCH) */
          inserted.DEALER_BATCH_KEY = DEALER_BATCH.DEALER_BATCH_KEY
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from
inserted where"," and") */
    select @nullcnt = count(*) from inserted where
      inserted.DEALER_BATCH_KEY is null
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30007,
             @errmsg = 'Cannot UPDATE ULF because DEALER_BATCH does not
exist.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* LICENSE_BOOK R/418 ULF ON CHILD UPDATE RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(BOOK_FORM_TYPE) or
    update(BOOK_NUM)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,LICENSE_BOOK
        where
          /* %JoinFKPK(inserted,LICENSE_BOOK) */
          inserted.BOOK_FORM_TYPE = LICENSE_BOOK.BOOK_FORM_TYPE and
          inserted.BOOK_NUM = LICENSE_BOOK.BOOK_NUM
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from
inserted where"," and") */
    select @nullcnt = count(*) from inserted where
      inserted.BOOK_FORM_TYPE is null and
      inserted.BOOK_NUM is null
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30007,
             @errmsg = 'Cannot UPDATE ULF because LICENSE_BOOK does not
exist.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* LICENSE_STATUS R/349 ULF ON CHILD UPDATE RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(LICENSE_STATUS)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,LICENSE_STATUS
        where
          /* %JoinFKPK(inserted,LICENSE_STATUS) */
          inserted.LICENSE_STATUS = LICENSE_STATUS.LICENSE_STATUS
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from
inserted where"," and") */

    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30007,
             @errmsg = 'Cannot UPDATE ULF because LICENSE_STATUS does not
exist.'
      goto error
    end
  end

  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  /* SPORTSMAN R/332 ULF ON CHILD UPDATE RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(SPORTSMAN_KEY)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,SPORTSMAN
        where
          /* %JoinFKPK(inserted,SPORTSMAN) */
          inserted.SPORTSMAN_KEY = SPORTSMAN.SPORTSMAN_KEY
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from
inserted where"," and") */
    select @nullcnt = count(*) from inserted where
      inserted.SPORTSMAN_KEY is null
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30007,
             @errmsg = 'Cannot UPDATE ULF because SPORTSMAN does not exist.'
      goto error
    end
  end


  /* ERwin Builtin Tue Nov 23 18:59:15 1999 */
  return
error:
    raiserror @errno @errmsg
    rollback transaction
end


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO




Show quoteHide quote

Assuming the ULF_Backup backup table has the same schema as table ULF, all
you need to do is add the ULF_KEY column so that you can specify
IDENTITY_INSERT:

SET IDENTITY_INSERT ULF ON
GO
INSERT INTO ULF (
    ULF_KEY,
    SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
    ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
    HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
    HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
    DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE)
SELECT
    ULF_KEY,
    SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
    ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
    HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
    HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
    DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE
FROM ULF_Backup WITH (NOLOCK)
GO

Show quoteHide quote

That fixed the problem.

Thank you.

Show quoteHide quote

no sure what that error is, as there are multiple errors for locking hints
but there are more than that here:

1. mixed INSERT..SELECT and INSERT..VALUES syntax.
2. NOLOCK hint is one word
3. are you truncating the ULF table first? if not, you'll need to check
for existing rows in the ULF table, based on the key. [as no DDL posted,
assuming SPORTSMAN_KEY, LICENSE_NUM is the key)

e.g.
INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
STATUS_DATE)
SELECT SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
LICENSE_STATUS, STATUS_DATE
FROM ULF_Backup WITH (NOLOCK)
WHERE NOT EXISTS (
   SELECT * FROM ULF
   WHERE SPORTSMAN_KEY = ULF_Backup.SPORTSMAN_KEY
     AND LICENSE_NUM = ULF_Backup.LICENSE_NUM
)


Mike L wrote:
Show quoteHide quote

Mike,

My guess is that you are passing a column name as a parameter to
a user-defined function (or something the parser thinks is one).  What
is FROM ULF_Backup(column names here?) supposed to mean?
Generally the only time there is a parameter list in this part of a FROM
clause is when the source is a table-valued user-defined function, and
in that case the parameters cannot be column names.

Steve Kass
Drew University

Mike L wrote:

Show quoteHide quote

Select most recent date time
What Are The Issues With MS Access Client to SQL Server DB
trouble using a user defined function
Column X is same, column Y is different?
Imbedded subroutine prefix error