Insert all values of a table into another table in SQL

Question

I am trying to insert all values of one table into another. But the insert statement accepts values, but i would like it to accept a select * from the initial_Table. Is this possible?

Answer 1

The insert statement actually has a syntax for doing just that. It's a lot easier if you specify the column names rather than selecting "*" though:

INSERT INTO new_table (Foo, Bar, Fizz, Buzz)
SELECT Foo, Bar, Fizz, Buzz
FROM initial_table
-- optionally WHERE ...

I'd better clarify this because for some reason this post is getting a few down-votes.

The INSERT INTO ... SELECT FROM syntax is for when the table you're inserting into ("new_table" in my example above) already exists. As others have said, the SELECT ... INTO syntax is for when you want to create the new table as part of the command.

You didn't specify whether the new table needs to be created as part of the command, so INSERT INTO ... SELECT FROM should be fine if your destination table already exists.

Answer 2

From here:

SELECT *
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

Answer 3

You can use a "select into" statement. See more at W3Schools.

Answer 4

I think this statement might do what you want.

INSERT INTO newTableName (SELECT column1, column2, column3 FROM oldTable);

Answer 5

If you are transferring a lot data permanently, i.e not populating a temp table, I would recommend using SQL Server Import/Export Data for table-to-table mappings.

Import/Export tool is usually better than straight SQL when you have type conversions and possible value truncation in your mapping. Generally, the more complex your mapping, the more productive you are using an ETL tool like Integration Services (SSIS) instead of direct SQL.

Import/Export tool is actually an SSIS wizard, and you can save your work as a dtsx package.

Answer 6

INSERT INTO newTable SELECT * FROM initial_Table

Answer 7

insert into stock_take_item_copy (stock_take_id, item_id) select stock_take_id, item_id from mymissingdata

why i got #1062 - Duplicate entry '20239' for key 'PRIMARY'? how to alter the primary key in which table?