How to Insert rows using select in SQL Server?

Question

Please post me some sample query to insert rows into a table using select query to select data from another table. What are the ways we can do?

Answer 1

There are 2 ways you can Insert and Select to insert data from one table to another. For example, if you have a Customer table with 500 rows, and you want to select any one whos age is greater than 45 then you want copy that into a temp table, then you can use anyone of the below query.

If have the temporay table called #CustomerTemp existing with the same structure as Customer, then

INSERT INTO #CustomerTemp
SELECT * FROM Customer WHERE Age > 45

Above query will insert all the rows that satified the condition from Customer table to a temporary table #CustomerTemp

If you have not created the temp table but you want to create it on the fly by the SQL Server then, you ca use below query

SELECT * INTO #CustomerTemp FROM Customer
WHERE Age > 45

This query also going to copy the same set of records into #CustomerTemp temporary table. But when it executes, if there are not tempory table with that name, then it will create one automatically based of the result set.