non-identity Primary Key

I need to be able to increment a Primary Key of type int without using
IDENTITY.
The reason is that I need to be able to Archive and restore data to this
table, and maintain the Primary Key.
Anyway, I setup a trigger to get the Max(MyIDfield) which works fine when
inserting single records into Table1.
The problem is when I need to insert new records from a select statement.
The trigger doesn't work for a "set" type insert.
I searched examples, but everything I found only supported single inserts.
Here's an example of the table structure.
CREATE TABLE Table1(
MyIDfield int NOT NULL PRIMARY KEY,
MyText varchar(50)
)
CREATE TABLE Table2(
MyIDfield int NOT NULL PRIMARY KEY,
MyText varchar(50)
)
As I explained, I need to..
[1] have unique Primary Keys [MyIDfield] across both tables
[2] be able to insert thousands of records into Table1 using an
insert/select query [trigger solution preferred]
[3] NOT use IDENTITY, in case I need to restore archived records [move back
from Table2 to Table1]
Thanks for any help,
ChrisYou can use the IDENTITY property, archive and restore data and still
maintain the primary key. Deleting records does not reset the IDENTITY
property (unless you use TRUNCATE).
Ben Nevarez, MCDBA, OCP
Database Administrator
"Chris" wrote:

> I need to be able to increment a Primary Key of type int without using
> IDENTITY.
> The reason is that I need to be able to Archive and restore data to this
> table, and maintain the Primary Key.
> Anyway, I setup a trigger to get the Max(MyIDfield) which works fine when
> inserting single records into Table1.
> The problem is when I need to insert new records from a select statement.
> The trigger doesn't work for a "set" type insert.
> I searched examples, but everything I found only supported single inserts.
> Here's an example of the table structure.
> CREATE TABLE Table1(
> MyIDfield int NOT NULL PRIMARY KEY,
> MyText varchar(50)
> )
> CREATE TABLE Table2(
> MyIDfield int NOT NULL PRIMARY KEY,
> MyText varchar(50)
> )
> As I explained, I need to..
> [1] have unique Primary Keys [MyIDfield] across both tables
> [2] be able to insert thousands of records into Table1 using an
> insert/select query [trigger solution preferred]
> [3] NOT use IDENTITY, in case I need to restore archived records [move ba
ck
> from Table2 to Table1]
> Thanks for any help,
> Chris
>
>|||And forgot to mention that you can restore records maintaining their primary
key using SET IDENTITY_INSERT ON (or DTS/SSIS with 'Enable identity insert'
checked).
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ben Nevarez" wrote:
> You can use the IDENTITY property, archive and restore data and still
> maintain the primary key. Deleting records does not reset the IDENTITY
> property (unless you use TRUNCATE).
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Chris" wrote:
>|||That's the ticket! I've heard of that, but it never came to mind. Never
had a case where I had to use SET IDENTITY_INSERT ON.
Thanks for the help! Perfect solution.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:217620C1-DAF2-45E5-8660-DD23BE1A8B3C@.microsoft.com...
> And forgot to mention that you can restore records maintaining their
primary
> key using SET IDENTITY_INSERT ON (or DTS/SSIS with 'Enable identity
insert'
> checked).
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Ben Nevarez" wrote:
>
this
when
statement.
inserts.
[move back|||Chris (rooster575@.hotmail.com) writes:
> I need to be able to increment a Primary Key of type int without using
> IDENTITY.
> The reason is that I need to be able to Archive and restore data to this
> table, and maintain the Primary Key.
> Anyway, I setup a trigger to get the Max(MyIDfield) which works fine when
> inserting single records into Table1.
> The problem is when I need to insert new records from a select statement.
> The trigger doesn't work for a "set" type insert.
> I searched examples, but everything I found only supported single inserts.
Bounce the data over a temp table with an IDENTITY column, and the
MAX value to the IDENTITY column.
If you are on SQL 2005, you could use the Row_number() function and be
saved the temp table.
I assume that the trigger is an INSTEAD OF trigger?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx