How to Copy a Row from One SQL Server Table to Another

How to copy a row from one SQL Server table to another

As long as there are no identity columns you can just

INSERT INTO TableNew
SELECT * FROM TableOld
WHERE [Conditions]

Quickest way to copy records from one table to another sql server

You should specify columns in the select statement and escape reserved words used as column names Month, Year with []:

Insert Into EmployeeVersion (EmpID, EmpName, [Month], [Year])  
select EmpId, EmpName, MONTH(GETDATE(), YEAR(GETDATE()) from Employee

And stored procedure:

CREATE PROCEDURE [dbo].[CopyEmployee] 
    @Month INT,
    @Year INT
AS
BEGIN
    SET NOCOUNT ON;

    Insert Into EmployeeVersion (EmpID, EmpName, [Month], [Year]) 
    select EmpId, EmpName, @Month AS [Month], @Year AS [Year] from Employee;
END
GO

SQL Server copy all rows from one table into another i.e duplicate table

Duplicate your table into a table to be archived:

SELECT * INTO ArchiveTable FROM MyTable

Delete all entries in your table:

DELETE * FROM MyTable

Copy rows from one table to another in MySQL

Since they are the same structure then you can just do

insert into table1 select colum1, column2,... from table2

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).

Copy rows between databases in SQL server

If your log IO subsystem allows it, then:

INSERT INTO target(field1, field2, field3)
SELECT field1, field2, field3 FROM source;

But having the entire transfer occur in one single statement means one single transaction, which means that x2.5 data size log has to be generated and kept during the entire statement. Ie. if you transfer 50Gb, your target db log will grow to 250Gb (even if the recovery mode is set to simple!).

If you are concerned about log, then you have to transfer in batches. You can still do the INSERT ... SELECT trick, but your SELECT has to use some key range and batch acceptable number of rows.

Ultimately, you can always do a bcp out followed by a bcp in, it will work pretty fast and is probably the fastest way to get a reliable transfer going.

Copying row from 1 table to another in SQL

Why not just handle this on the back end? You can create a trigger on the original table to insert into another table after every delete?
Your trigger will look like this:

CREATE TRIGGER onOriginalTableDelete
ON originalTable
FOR DELETE
AS 
INSERT INTO anotherTable
SELECT * FROM deleted;

When a record is deleted on the original table, it will insert the deleted record into the other table. You might want to read on using the deleted table here.

Check this SQL Fiddle. Since you're inserting the timestamp in another column, you can just add this on the INSERT INTO SELECT statement:

INSERT INTO OtherTable
SELECT *, CURRENT_TIMESTAMP FROM MainTable;

This could be the query for your trigger:

CREATE TRIGGER onOriginalTableDelete
ON originalTable
FOR DELETE
AS 

INSERT INTO anotherTable
SELECT *, CURRENT_TIMESTAMP FROM deleted;

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.

---

Related Topics