INSERT INTO IN Same Table 1

[meravsha]

Hi,
I tried to copy One record to a new record with this sentence.
INSERT INTO Table1 SELECT * FROM Table1 WHERE Code=22

AS expected I Get an Error about duplicate Key.
what can I do?

 

[CCLINT]

List the all the fields except the AutoNumber field (if no autonumber field, but another unique key, then list that field and assign the new key)

 

[meravsha]

Well, I have a lot of fields to write, AND for write in your way will required To open a Source recordSet
I Look for a short Way to do it.
I'm working with Sql Server or Access.
Thanks

 

[CCLINT]

>required To open a Source recordSet
No it doesn't...

 

[CCLINT]

Oh, and if you are not using an autoincrement field, then you can try to do this (1234 is the new Code):

SELECT 1234 As IDCode, Table1.* From Table1 Where Code = Code 22

 

[meravsha]

I'm not using autoincrement field So this is the reason I
get the error of duplicate key.
If i'll try the statment you suggest:
INSERT INTO TblOrd SELECT 1234 AS Or_codeOrder ,TblOrd.* FROM TblOrd WHERE Or_CodeOrder=22
I'll Get an error:"Number of query values and destinatin fields are not the same"

perhaps I didn't understand , Can you please give me example?

 

[CCLINT]

Yes, you are right - it will not work.
It would only work only for a situation where the field being manually set is not in the source table:

SELECT TblOrd.*, 1234 AS SomeOtherFieldNotInTheSourceTable From TblOrd

if the SomeOtherField wasn't in the source table (such as when copying data from one table to another and the you do not want to list out all fields, but but to also set values for the fields in the receiving table which are not in the source table - also a nice short cut).

So, your only other choice is to list the fields in the source which the data should be used in the receiving record.

Say you have 20 fields in the source,the first field is a primary field so you want to set that value yourself.
The value in the next 2 fields should come from the source and the fields in the destination are the same name, and the last field is not in the source, but you want to set it's value also, and the values in the rest of the source fields are not needed, then you can do this.

INSERT INTO TblOrd SELECT 1234 AS Or_codeOrder ,Field2, Field3, Field4 AS Field4X, 'XYZ' AS SomeFieldNotInTheSourceTbl,... FROM TblOrd WHERE Or_CodeOrder=22

In your case, if you want to take the value of all fields from the source record and insert them into the destination record, but change the value of just one of those fields, such as the primary/unique key, then you are going to have to list all of the fields.

Another way would be to use a recordset...

And yet another way would be to build the field list for the INSERT statement dynamically using OpenSchema and extracting the field names from the table.

And, If you are using SQL Server, you could use an INSERT statement as you were using and insert the record into a temp table, use an UPDATE statement to change the primary key, and a last INSERT statement to insert the record into the original tabel - 3 little action queries.
In JET/ACCESS you would need to do this using a SELECT INTO statement:
[blue]

On error resume next
conn.Execute "DROP Table tempTable"
On Error Goto errHandler
conn.Execute "SELECT * INTO tempTable FROM TblOrd WHERE Or_CodeOrder=22"
conn.Execute "UPDATE tempTable SET Or_codeOrder =1234"
conn.Execute "INSERT INTO TblOrd SELECT * FROM tempTable"
On error resume next
conn.Execute "DROP Table tempTable"

 

[meravsha]

Thank you for your Advise.
I will Make a list of all emportent fields or maybe use the OpenSchema.
you were very helpfull to me.