Re: Table has identity field

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Adam Machanic (amachanic_at_air-worldwide.nospamallowed.com)
Date: 03/29/04 

Date: Mon, 29 Mar 2004 12:56:51 -0500

IF EXISTS (select * from syscolumns where id=object_id('mytable') and
objectproperty(id, 'tablehasidentity') = 1)
BEGIN
...
END

Replace 'mytable' with the name of your table...

"Seal" <sealtielp@afex.com> wrote in message
news:ur3ipUbFEHA.1600@tk2msftngp13.phx.gbl...
> Hi everyone,
>
>
>
> Is there a fast and reliable way to determinate if a table has an identity
> field?
>
> I'm working in a transfer process between servers and from time to time I
> have to transfer records between tables with identity fields. The process
> uses dynamic sql widely because is driven by some configurations tables.
>
>
>
> This is part of the code:
>
> After getting the list of fields that I need to transfer and marking those
> records to update or insert (All the records from the remote server are
> already in a temp table):
>
> ---------------
>
> Set @sql = 'Insert into ' + @sTableLocal + '(' + @sFieldsL + ') '
>
> Set @sql = @sql + 'Select ' + @sFieldsR + ' From ' + @sTempTable + ' Where
> tfrbExists = 0'
>
> exec sp_Executesql @sql
>
> ----------------
>
>
>
> This work fine for tables without identity field however fails if I ever
add
> a table with identity field:
>
> I would like to do something like this
>
> ----------------
>
> -- For those tables with Identity fields
>
> If funTableHasIdentityField(@sTableLocal) = 1 -- Or something like that
>
> Begin
>
> Set @sql = 'SET IDENTITY_INSERT ' + @ sTableLocal + ' ON '
>
> Set @sql = @sql + ' Insert into ' + @ sTableLocal + '(' +
> @sFieldsL + ') '
>
> Set @sql = @sql + 'Select ' + @sFieldsR + ' From ' + @sTempTable + ' Where
> tfrbExists = 0'
>
> End
>
> Else
>
> Begin
>
> Set @sql = 'Insert into ' + @ sTableLocal + '(' + @sFieldsL +
')
> '
>
> Set @sql = @sql + 'Select ' + @sFieldsR + ' From ' + @sTempTable + ' Where
> tfrbExists = 0'
>
> End
>
> exec sp_Executesql @sql
>
> ---------------
>
>
>
> What will be the best way to accomplish "funTableHasIdentityField"?
>
> Any sugestions? Ideas?
>
>
>
> Thanks in advance.
>
>
>
>
>
>

Relevant Pages

  • Re: Table has identity field
    ... > uses dynamic sql widely because is driven by some configurations tables. ... > records to update or insert (All the records from the remote server are ... > exec sp_Executesql @sql ... > This work fine for tables without identity field however fails if I ever ...
    (microsoft.public.sqlserver.programming)
  • Non updatable query & duped IDs
    ... There is some VBA code (Access2k, Win2k, ODBC linked SQL ... Server tables) that has run perfectly for a weekly process ... The table had an identity field but no ... Why is a non-duplicated id field required to do lookups? ...
    (microsoft.public.access.modulesdaovba)
  • SQL Insert that return Identity, any samples
    ... I am looking for SQL Insert sample which returns the newly created id.I am ... using HTTP adapter to send an xml message that contains 1 Order and many ... the field Order.Id because it is an identity field.If i ...
    (microsoft.public.biztalk.general)
  • Re: IDENTITY Data Type Gaps
    ... > We recently upgraded an Access XP database to SQL Server, ... In the original Access database, ... to converting to SQL Server.) ... Identity field value. ...
    (microsoft.public.sqlserver.odbc)
  • Re: High Throughput Database
    ... the UserID and the Identity field? ... But whatever way you do it, should have a clustered index with UserID ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)