Quickest Way to Clone Row in SQL

Quickest way to clone row in SQL

If you are cloning from multiple tables, the fastest way will be to use a stored procedure, so that all the queries stay on the database, and you don't pay a price for communication between the client and the server.

Once you do that, then start unit-testing, to see how long it takes to do the operation. Then begin experimenting with changing it, based on suggestions you get, and see if you get any improvement.

How to copy a row and insert in same table with a autoincrement field in MySQL?

Use INSERT ... SELECT:

insert into your_table (c1, c2, ...)
select c1, c2, ...
from your_table
where id = 1

where c1, c2, ... are all the columns except id. If you want to explicitly insert with an id of 2 then include that in your INSERT column list and your SELECT:

insert into your_table (id, c1, c2, ...)
select 2, c1, c2, ...
from your_table
where id = 1

You'll have to take care of a possible duplicate id of 2 in the second case of course.

Is there a way to quickly duplicate record in T-SQL?

The only way to accomplish what you want is by using Insert statements which enumerate every column except the identity column.

You can of course select multiple rows to be duplicated by using a Select statement in your Insert statements. However, I would assume that this will violate your business key (your other unique constraint on the table other than the surrogate key which you have right?) and require some other column to be altered as well.

Insert MyTable( ...
Select ...
From MyTable
Where ....

Clone a row with a primary key in SQL?

I'm not sure about PostgreSQL but if it supports the auto-increment like most systems do then just leave the primary key field blank. However this does mean you need to specify the column names.

Also, when you say clone it sounds like you want a duplicate of your record but in your description the address1 and address2 columns are on the same row of data.

If you want to replicate your address1 to the address2 column you can use a simple update statement.

If you are trying to clone the entire row to have two records that are exactly the same then you could use something like this and it should work on both environments.

INSERT INTO Addresses
( address1, address2 )
SELECT address1, address2
  FROM Addresses
 WHERE addressId = ID

Now the next question is, Are you cloning from one DB type to the other? If so then you will need to pull it into your app anyway. If you are then why don't you use something like nHibernate to abstract the db layer away from you.

If the goal is to just make a copy then you can do it straight in the db with the sql above.

While this sql is probably the easiest (and you could reflect it from the db if needed using system / master tables) you would still need to specify the column names. The primary reason is that as soon as you add the * wildcard it would still try to include your original primary key column in the select list which would then have 1 extra column to what you have.

Another issue to be aware of when using wildcards in insert statements is that your column orders HAVE TO MATCH otherwise you will get very unexpected results.

How to quickly duplicate rows in SQL

To do this many inserts you will want to disable all indexes and constraints (including foreign keys) and then run a series of:

INSERT INTO mytable
SELECT fields FROM mytable

If you need to specify ID, pick some number like 80,000,000 and include in the SELECT list ID+80000000. Run as many times as necessary (no more than 10 since it should double each time).

Also, don't run within a transaction. The overhead of doing so over such a huge dataset will be enormous. You'll probably run out of resources (rollback segments or whatever your database uses) anyway.

Then re-enable all the constraints and indexes. This will take a long time but overall it will be quicker than adding to indexes and checking constraints on a per-row basis.

How do you copy a record in a SQL table but swap out the unique id of the new row?

Try this:

insert into MyTable(field1, field2, id_backup)
    select field1, field2, uniqueId from MyTable where uniqueId = @Id;

Any fields not specified should receive their default value (which is usually NULL when not defined).

MySQL: How to copy rows, but change a few fields?

INSERT INTO Table
          ( Event_ID
          , col2
           ...
          )
     SELECT "155"
          , col2
           ...
      FROM Table WHERE Event_ID = "120"

Here, the col2, ... represent the remaining columns (the ones other than Event_ID) in your table.

Copy rows from the same table and update the ID column

INSERT INTO ProductDefinition (ProdID, Definition, Desc)
SELECT
  xxx, Definition, Desc
FROM
  ProductDefinition
WHERE
  ProdID = yyy

The xxx is your new ProdID and the yyy is your old one. This also assumes that DefID is automagically populated on INSERT.

How to duplicate a row in SQL Server?

not sure if I understand exactly but something like this maybe:

with X as 
(
select Element = [Key]
    ,New = max(case when time_index=1 then value end)
    ,'Current' = max(case when time_index>=2 then value end)
From  (
    Select [time_index]
            ,B.*
        From  (select * from ifrs17.output_bba where id in (602677,602777)) A
        Cross Apply (
                    Select [Key]
                        ,Value
                    From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper ) ) 
                    Where [Key] not in ('time_index')
                    ) B
    ) A
Group By [Key]
)
select Element, New, [Current] from X 
union all
select 'DDD' as Element, New, [Current] from X where Element = 'BBB'

---

Related Topics