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 [quoted text, click to view] "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 back
> from Table2 to Table1]
>
> Thanks for any help,
> Chris
>
>
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,
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 [quoted text, click to view] "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:
>
> > 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,
> > Chris
> >
> >
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. [quoted text, click to view] "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:
>
> >
> > 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:
> >
> > > 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,
> > > Chris
> > >
> > >
> > >
Chris (rooster575@hotmail.com) writes: [quoted text, click to view] > 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/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Don't see what you're looking for? Try a search.
|
