How can you duplicate rows using SQL...

[Brad Pears]

I am working on an application where I want the user to be able to
"Duplicate" a Contract item.

To do this I have to duplicate the row in the master table and duplicate all
related rows in the related "children" tables (of which there are twelve..)

I was wondering if there was an SQL clause that would allow me to duplicate
rows in a table (and specify the new primary key value) without having to go
through and "select from" and then "insert into" for all of the tables...

If there is not an easy way, does anyopne know of some sort of a generic
duplicate fucntion I could download from soemwhere where you can pass it
table names etc... to do the job???

Thanks,

Brad

 

[Michel Walsh]

Hi,


An INSERT INTO sql statement.


INSERT INTO myTable(f2, f3, f4) SELECT f2, f3, f4 FROM myTable WHERE
f5='Kansas'


will try to copy the records you can see with the statement

SELECT f2, f3, f4 FROM myTable WHERE f5='Kansas'


into the table myTable. Fields with default value, or fields allowing NULL
do not need to be "filled", but those not allowing NULL and not having a
default value must be supplied. Autonumber fields are considered as having a
default value (automatically computed).



Hoping it may help,
Vanderghast, Access MVP