Duplicate Existing Record in Same Table

[Pizzadude]

I want to copy an existing record using SELECT *, then insert the record into the same table, with a new Primary Key. The table has an Auto-increment Primary Key.
Is there an easy way to do this?
I could grab every column in the table, and put each field into an argument, then insert the record. But that seems like the long way around.

[Lance Gurd]

A simple way I do this is to get a detail view of a record, I use viewboxes but can be done with a list etc. Then program a button to blank the primary key and save the record. New record is made with new key.

I use this to make new similar records.

[Pizzadude]

Hmm... that is an interesting method. Could that work for all records in the list?
I want to do this for many records, in one operation.

[Lance Gurd]

Not sure as never tried it, but don't see why not.

I do copy a group of records to a temporary table which does not have a primary key, then copy it back to the main table

[madtowng]

Using select * is the problem. SQL uses auto_increment when no primary key value is selected.
With that said, you could insert into table( <all fields but PK>) select <all fields but PK> from table where <something matches criteria>.
if you prefer the temporay table route you could use create temporary table <table name> select <all fields but PK> from <current table> where <something matches criteria>; insert into <original table> select * from <temporary table name>.
The temporary should automatically drop when the session is ended, but if you want to be sure you can always drop temporary table <temporary table name>

[ferryk17]

use xbasic:
args.add("id",[your primary key])
sql = <<%str%
INSERT INTO bbb
(cl1,cl2,cl3)
SELECT cl1,cl2,cl3 FROM aaa WHERE ID = :ID
%str%

dim newID as n
if cn.execute(sql,args) then
newID = cn.CallResult.LastInsertedIdentity
end if

'if newID is 0 that means it failed to generate new record

[Russell Craycraft]

I'm not sure of what your goal is with this, but see if this works. You can copy/paste into SQL Server, and I added notes.

--Create new table for testing, and inserts all of the original data into it.
SELECT *
INTO NEWTABLENAME
FROM ORIGINALTABLENAME

--Set IDENTITY_INSERT ON, copies all of the data into the new list again, then set IDENTITY_INSERT back to OFF.
SET IDENTITY_INSERT NEWTABLENAME ON
INSERT INTO NEWTABLENAME
SELECT
LIST ALL OF YOUR FIELDS HERE
FROM ORIGINALTABLENAME
SET IDENTITY_INSERT NEWTABLENAME OFF

--Run this to see if all records now have 2 records. Change the > 1 to greater than 2 to see if you already had any duplicates. A numeric unique PK will not work because there will be no duplicates. You may need to use something like WHERE field1 AND field2 AND field3.
SELECT DISTINCT FieldYouUseToID,
Count(FieldYouUseToID)
FROM NEWTABLENAME
GROUP BY FieldYouUseToID
HAVING Count(FieldYouUseToID) > 1

[Pizzadude]

All good suggestions. The reason I want to use SELECT * is because some my tables have a lot of columns, and I need to do all the columns.
I have a list of factoring companies that I want to copy so another Company can use the same list of factoring companies.

This is what came up with. All in xbasic:

Create a table by duplicating an existing table:
CREATE TABLE factoringcompany2 AS SELECT * FROM factoringcompany WHERE Whatever = "Anything";

Then eliminate the primary key in the new table:
ALTER TABLE factoringcompany2 DROP PRIMARY KEY;

The modify (former) the primary key column definition, to remove the Auto Increment property:
ALTER TABLE factoringcompany2 MODIFY IndexPK INT(16) DEFAULT 0;

Then set the (former) Primary Key to NULL for all records. Also change the value in one column - this is the reason I want to do this operation in the first place.
UPDATE factoringcompany2 SET Company_FK = 5452, IndexPK = NULL;

Now insert all the records from the new table into the original table.
INSERT INTO factoringcompany SELECT * FROM factoringcompany2;

Then delete the new table.
DROP TABLE factoringcompany2