"select * into table" Will it work for inserting data into existing table

Question

I am trying to insert data from one of my existing table into another existing table.

Is it possible to insert data into any existing table using select * into query. I think it can be done using union but in that case i need to record all data of my existing table into temporary table, then drop that table and finally than apply union to insert all records into same table

eg.

select * into #tblExisting from tblExisting
drop table tblExisting
select * into tblExisting from #tblExisting union tblActualData

Here tblExisting is the table where I actually want to store all data tblActualData is the table from where data is to be appended to tblExisting.

Is it right method. Do we have some other alternative ?

Answer 1

You should try

INSERT INTO ExistingTable (Columns,..)
SELECT Columns,...
FROM OtherTable

Have a look at INSERT

and SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE

Answer 2

No, you cannot use SELECT INTO to insert data into an existing table.

The documentation makes this very clear:

SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it.

You generally want to avoid using SELECT INTO in production because it gives you very little control over how the table is created, and can lead to all sorts of nasty locking and other performance problems. You should create schemas explicitly and use INSERT - even for temporary tables.