Q: Mutli-Row Inserts, can I get a unique value for each row inserted? 
Author Message
 Q: Mutli-Row Inserts, can I get a unique value for each row inserted?
Hi,

I want to select a number of records from one table and insert part of  
them into another.  The destination table uses a primary key obtained from  
a counter.  How can I get a unique value from the counter for each row  
that I need to insert into the destination table?

The end result is to attach a short note to a number of records(clients)  
to record an action.

Any help greatly appreciated,
wayne



Sat, 01 Feb 1997 08:00:10 GMT
 Q: Mutli-Row Inserts, can I get a unique value for each row inserted?

Quote:

>Hi,

>I want to select a number of records from one table and insert part of  
>them into another.  The destination table uses a primary key obtained from  
>a counter.  How can I get a unique value from the counter for each row  
>that I need to insert into the destination table?

>The end result is to attach a short note to a number of records(clients)  
>to record an action.

>Any help greatly appreciated,
>wayne

Wayne,

insert the rows with the primary key set to NULL.  Then have the insert trigger
update the primary key to the next counter-value where the primary key is null.
You have to do this in a while-loop with rowcount set to 1 and break out of
the loop when there are no more primary keys with a null-value.

Pseudo Code for Trigger:

        set rowcount 1
        while (select count(*) from table where pkey is NULL) > 0
        begin
                get new_pkey from counter
                update table set pkey = new_pkey where pkey is null
        end
        set rowcount 0

Drawback of this solution: You cannot have a unique index on pkey since you
will have multiple NULL values, and it must allow nulls.

Good luck,

Frank



Sat, 01 Feb 1997 22:29:28 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Inserting unique rows - last row skipped ???

2. Get a Unique value to each row using INSERT

3. getting a value from inserted row?

4. getting a value from inserted row?

5. Getting IDENTITY value of a row that was just INSERTED

6. Getting IDENTITY value of newly inserted row

7. Getting the identity value of a newly inserted row

8. Insert record -> getting unique id value

9. Newbie: Insert rows and Unique Primary Key Constraints

10. Return the unique ID from the last inserted row

11. How to transactionally insert a row and rows into parent and child table

12. Insert or update from n number of rows into a single row without a cursor


 
Powered by phpBB® Forum Software