CODEWITHSUNDEEP
sql-server
Ziil
Ziil

Reputation: 351

mssql - Insert multiple rows back into original table from temporary table

If I select multiple rows into temporary table, how to insert these rows back into the same original table?
For example:
Select * into #temp_table from mytable where id=1 - will provide 4 rows for my temp_table.
Now I would like to insert these 4 rows back into the same original table (mytable) and If I will execute the same select statement after insert, I want to see 8 rows in results with just unique id.

What is the best and easiest way to do it? Maybe temporary table isn't good idea at all.

Upvotes: 1

Views: 509

Answers (1)

Joe
Joe

Reputation: 626

If you just want to add everything straight from the temporary table back into myTable, then you can do it simply by:

Insert into mytable 
Select * from #temp_table

If you need to reuse the temporary table, then remember to clear this before adding the next lot of entries:

delete from #temp_table

Of course, it would be easier skipping the temporary table part all together if you just want to add some more entries from one table back into itself:

Insert into mytable
Select * from mytable where id=1

One thing to note here, this won't work if you have an auto incrementing identity column (as a Primary Key for example) in mytable, as select * will try to insert the ID again into the table which isn't allowed (must be unique). therefor, instead of using select *, you are best specifying the column names. This example would be better:

Insert into mytable (column1Name, column2Name)
Select column1Name, column2Name from mytable where id=1

Hope this is of some help. Good luck.

Upvotes: 2

Related Questions