SQL to copy from one table to another

[Endorphin]

Using Oracle.

I have two tables

TABLE1
id,
name,
attribute

TABLE2
id,
name,
attribute

I need to copy all rows from TABLE1 to TABLE2 where name = :name,

However when doing this I have to also set the id of all the fields being copied into TABLE2 to :id (this value remains constant across all fields that are being copied).

So if :id = 1000, irrespective of what their value in TABLE1.id is, all rows being copied to TABLE2 will have the id.value of 1000.

I know I need something like


INSERT INTO TABLE2 (id, name, attribute) SELECT (id, name, attribute) FROM TABLE1 WHERE name = :name


But just need to get at that id value to set it.

[George1111]

Be careful with the ID field

If it is an autoallocated field (in both tables) then your statement wont work because the receiving table will want to allocate the ID automatically

But if in the receiving table the ID field is NOT autoallocated, and is not a unique index field, then your statement should work

...... FROM TABLE1 WHERE name = :name

may need to be

...... FROM TABLE1 WHERE name = '" & name & "'"

(But then, I know nothing about Oracle)

[Endorphin]

But surely the SQl statement I wrote does not deal with setting the id field in TABLE2 to a bind variable?

It can't as easy as


INSERT INTO TABLE2 (id, name, attribute) SELECT (:id, name, attribute) FROM TABLE1 WHERE name = :name


can it?

[davide++]

Hi all.

@George1111
Oracle hasn't the concept of AUTOALLOCATE field (I think you mean AUTOINCREMENT field, as Access). Unique values are generated using sequences.

@Endorphin.
I don't understand your requirement. Using the statement

INSERT INTO TABLE2 (id, name, attribute) SELECT (id, name, attribute) FROM TABLE1 WHERE name = :name

you'll get in TABLE2 a full copy of all rows extracted by the query, so id's value of TABLE2 will be the same id's values of TABLE1.
If you want different values, you should use sequences; first create a sequence, for example SEQ_ID_TABLE2, then you can write something like this

INSERT INTO TABLE2 (id, name, attribute) SELECT (SEQ_ID_TABLE2.NEXTVAL, name, attribute) FROM TABLE1 WHERE name = :name


I hope this will help you.