Generating identity values without IDENTITY keyword

Generating identity values without IDENTITY keyword

Post by Mario De Sousa Barrer » Thu, 23 Jan 1997 04:00:00



Hi!!
I want to insert several rows in an existent table. This table doesn't use
the IDENTITY keyword in the primary key field, but I would like to fill it
with increasing integer numbers.
The inserted rows come from a query from another table, and I found that
you can't assign a variable inside of a query to do something like this:


insert into MyTable (pk, field1, field2, ...)

from MyOtherTable

I have used the IDENTITY function (which is diferent from an Identity
property in a table), but then I have to use the INTO clause in the select
and create a temporary table.


into #TempTable
from MyOtherTable

insert into MyTable (pk,field1,field2, ...)
select *
from #TempTable

Is there any way to skip the temporary table creation?
--
Mario De Sousa Barrera
Eniac, C.A.
Caracas, Venezuela

 
 
 

Generating identity values without IDENTITY keyword

Post by Barbara Geor » Tue, 28 Jan 1997 04:00:00




insert into MyTable (pk, field1, field2, ...)

from MyOtherTable


>Hi!!
>I want to insert several rows in an existent table. This table doesn't use
>the IDENTITY keyword in the primary key field, but I would like to fill it
>with increasing integer numbers.
>The inserted rows come from a query from another table, and I found that
>you can't assign a variable inside of a query to do something like this:

>insert into MyTable (pk, field1, field2, ...)

>from MyOtherTable
>I have used the IDENTITY function (which is diferent from an Identity
>property in a table), but then I have to use the INTO clause in the select
>and create a temporary table.

>into #TempTable
>from MyOtherTable
>insert into MyTable (pk,field1,field2, ...)
>select *
>from #TempTable
>Is there any way to skip the temporary table creation?
>--
>Mario De Sousa Barrera
>Eniac, C.A.
>Caracas, Venezuela


 
 
 

1. Identity without IDENTITY

I'm upgrading from Access to SQL Server 6.5.  I have some tables that I
manually auto increment their primary key fields.  Currently I store the
current value in another table and increment and query that value,
before I insert a new record into the table.  I do this because I need
to know the new key value BEFORE I insert a new record, so AuotInc won't
work.

I have a feeling that I could use a stored procedure on SQL Server to
make a cleaner version of this scheme.  But, I'm not sure about a couple
of things.  Can I use a "static" variable in a stored procedure, and
will there be any concurrency problems if two users try to get the value
from the sp at the same time?

Thanks in advance,

Adam Ruth
Common Knowledge, Inc.


2. SQL Server SP2

3. Retrieve Identity value (select @@identity...)

4. Using RAISERROR and @@ROWCOUNT (Transact-SQL)

5. Setting the Next identity value on an IDENTITY column

6. LI, NY - Oracle/Sybase on NT and Unix clients

7. Identity columns and invalid identity values.

8. Code for accounting systems

9. Attempt to insert duplicate identity values in table (has identity and key fields)

10. bcp with automatically generated identity column values?

11. Getting generated IDENTITY value

12. Retrival of newly created row auto generated identity column values

13. Getting the generated value for an Identity column