copy a record

[luciddream]

there has got to be a way to copy a record so that i don't have to insert a thousand values from an existing record.

please, someone has to know how to do this. luciddream@subdimension.com

 

[Qaroven]

So you want to duplicate a record in a table?

I assume that's duplicate all fields BUT the primary key?

insert into table1
select t.field1 as field1, t.field2 as field2 (etc)
from table1 t
where primarykeyfield = ABC

Could just do select * but that will duplicate the pri key.
Above assumes using autonumber/counter field for pri key, otherwise you'll have to get your next pri key number first.
Ben
+61 403 395 052

 

[palbano]

Yeah Ben is right on it. The only other thing I would add is that you should consider using a stored procedure for this.

The reason is that in that environment you have access to proprietary coding techniques that might solve some of the problems like primary keys etc., in other words there might be a way to make Select * work and not have to type out all of the columns. To find out more check out the TT forum for the database you are using, most of the database forums here have really sharp people in them.

Good luck
-pete

 

[DonP]

I hope a reply to an old posting won't confuse things but I need help with the exact same question. In following the example above, I ended up with the code below. It does not crash but neither does it insert anything into the backup table. Can anyone see what I might be doing wrong?

[tt]Set rscomp = Server.CreateObject("ADODB.Recordset&quot
cmdTemp.CommandText = "select * Comp WHERE ID = " & ID
Set cmdTemp.ActiveConnection = DataConn
rscomp.Open cmdTemp, , 1, 3

Set rsbackup = Server.CreateObject("ADODB.Recordset&quot
cmdTemp.CommandText = "select * FROM CompDeleted"
Set cmdTemp.ActiveConnection = DataConn
rsbackup.Open cmdTemp, , 1, 3

dim item
rsbackup.AddNew
for each item in rscomp.Fields
If item.name="value" then
rsbackup(item.name)="changed value"
else
rsbackup(item.name) = rscomp(item.name)
end if
next
rsbackup.Update[/tt]

The only difference between the two SQL Server tables is that the backup table does not have autonumbering on the ID field. Don
don@pc-homepage.com

http://www.pc-homepage.com/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT/2000 (only when I have to!)