Posted by Rich on 01/13/06 20:24

In article <1137011309.156279.200480@g47g2000cwa.googlegroups.com>, Josh
McFarlane says...
>
>I've got a structure where I have a database that I want to merge into
>another (same format), however, I need to update some fields.
>
>Is it possible to do an INSERT and an UPDATE in the same SQL statement
>without modifying the original data with the update statement? (Making
>a modification to the recordset in memory that you are inserting)
>
>I'd like to do the following:
>
>Insert data into first new table
>Retreive new primary key ID for each record
>
>Pull child records out of second table
>Update foreign key to new key from above
>Insert records into new table
>
>Thanks in advance!
>Darsant
>

Hi Darsant,

Depending which SQL server you're running, there may be a command you can use to
get the next value of a primary key. That way you know the value ahead of time
and saves having to run a query in the middle of inserts and updates.

As an example, I believe PostgreSQL can use "select nextval()" if the the table
is configured correctly for that. MySQL has the "LAST_INSERT_ID()" function
which seems similar to that, though you'd have to increment your result to get
to the next value. Hope the information is helpful.

Rich
--
Basic Newsguy - 3 GB / month - $39.95 / year
http://newsguy.com/overview.htm

[Back to original message]