213

How to copy/append data from one table into another table with same schema in SQL Server?

Edit:

let's say there is a query 

select * 
into table1 
from table2 
where 1=1

which creates table1 with the same schema as well as data as in table2.

Is there any short query like this to only copy entire data only into an already existing table?

Rajaram Shelar
  • 7,537
  • 24
  • 66
  • 107
  • How to solve this ? `INSERT INTO newTable (col1, col2, col3,col4) values ((SELECT column1, column2, column3 FROM oldTable where condition),'string');` –  Oct 31 '17 at 04:07
  • 1
    Just for complete information, be careful this commands DO NOT copy indexes and triggers of table! See following post for copy indexes and triggers script: https://stackoverflow.com/questions/7582852/programmatically-copy-indexes-from-one-table-to-another-in-sql-server – HausO Jul 19 '16 at 09:17
  • you can do like `INSERT INTO newTable (col1, col2, col3,col4) values ((SELECT column1, column2, column3, 'string' as col4 FROM oldTable where condition))` – warrior107 Nov 12 '20 at 15:16

12 Answers12

418

If both tables are truly the same schema:

INSERT INTO newTable
SELECT * FROM oldTable

Otherwise, you'll have to specify the column names (the column list for newTable is optional if you are specifying a value for all columns and selecting columns in the same order as newTable's schema):

INSERT INTO newTable (col1, col2, col3)
SELECT column1, column2, column3
FROM oldTable
lc.
  • 113,939
  • 20
  • 158
  • 187
  • 1
    what if I want to enter `blank data` for some column ?? – Nad Dec 11 '15 at 07:31
  • 3
    @coder it's just a select clause, so you're welcome to put anything in it you want, including explicit `NULL`s, string constants, expressions, or even subqueries. – lc. Dec 11 '15 at 11:10
  • why this does not work `INSERT INTO newTable (col1, col2, col3,col4) values ((SELECT column1, column2, column3 FROM oldTable where condition),'string');` –  Oct 31 '17 at 04:06
  • 1
    @AbdullahNurum Because your scalar query is selecting more than one column. See https://stackoverflow.com/questions/4141370/sql-insert-with-select-and-hard-coded-values and https://stackoverflow.com/questions/6254913/sql-select-inside-insert-values – lc. Oct 31 '17 at 04:14
  • 2
    If both tables present similar schema, but there is a column with 'Identity Specification' on YES, the SQL statement need to enumerate all the columns, except the one with Identity Specification. – Gabriel Marius Popescu Nov 14 '18 at 15:10
  • Hi @lc. , My new table Schema and old table Schema is different. so I want to specify column names to copy the data from one table to another. However, how to copy data of system versioned columns from old table to new table? – prasanthi Sep 10 '19 at 04:57
  • If both tables are identical and have an IDENTITY COLUMN, I turn the destination identity column's property "Identiity Specification/(Is Identity)" to No manually, perform the INSERT/SELECT, and then manually turn the identity back on. – John Kurtz Nov 01 '19 at 21:17
  • See https://stackoverflow.com/questions/32002876/how-to-copy-data-in-identity-column for details on @Gabriel's case. Questions could be merged. – Xanderak Dec 02 '19 at 22:19
35

Simple way if new table does not exist and you want to make a copy of old table with everything then following works in SQL Server.

SELECT * INTO NewTable FROM OldTable
Satish Patel
  • 1,784
  • 1
  • 27
  • 39
24

This is the proper way to do it:

INSERT INTO destinationTable
SELECT * FROM sourceTable
Zzz
  • 2,927
  • 5
  • 36
  • 58
18
INSERT INTO table1 (col1, col2, col3)
SELECT column1, column2, column3
FROM table2
Gwenc37
  • 2,064
  • 7
  • 18
  • 22
user3566871
  • 193
  • 1
  • 6
10

Try this:

INSERT INTO MyTable1 (Col1, Col2, Col4)
   SELECT Col1, Col2, Col3 FROM MyTable2
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
6

Try this:

Insert Into table2
Select * from table1
Kapil Khandelwal
  • 15,958
  • 2
  • 45
  • 52
6

Insert Selected column with condition

INSERT INTO where_to_insert (col_1,col_2) SELECT col1, col2 FROM from_table WHERE condition;

Copy all data from one table to another with the same column name.

INSERT INTO where_to_insert 
SELECT * FROM from_table WHERE condition;
Nimmi Verma
  • 465
  • 6
  • 11
6
CREATE TABLE `table2` LIKE `table1`;
INSERT INTO `table2` SELECT * FROM `table1`;

the first query will create the structure from table1 to table2 and second query will put the data from table1 to table2

Renish Gotecha
  • 2,232
  • 22
  • 21
4
INSERT INTO DestinationTable(SupplierName, Country)
SELECT SupplierName, Country FROM SourceTable;

It is not mandatory column names to be same.

S.Adikaram
  • 480
  • 5
  • 12
2

Copy all columns from one table to another table:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Copy only some columns from one table into another table:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

You can duplicate or "clone" a table's contents by executing:

CREATE TABLE new_table AS SELECT * FROM original_table;
Amandeep
  • 41
  • 6
0

-- for Sql Server users.

if you don't have the new table then you can create the new table with same structure as old table, and also copy data over from old table to the new table. For example:

select * into new_table
from old_table;

also you can copy the column / table structure, and just some of data. For example:

select * into new_table
from old_table 
where country = 'DE';
Milad Hamid Elhaei Sahar
  • 543
  • 8
  • 12
0

Copy schema & data from one table to another table:

  create table table1 SELECT * FROM table2;

Copy data from one table to another table:

insert into table2 select * from table1;
Apurv
  • 21
  • 5