Search code examples
sqlsql-server

How to insert into two temp tables in SQL query?

I am working on a project and I want to insert into two tables with the SQL query like this:

SELECT *
INTO #temp1, #temp2
FROM table

I actually want to insert the same items (rows) in two temp tables.

Is there any way to do this by a single query?

Solution

  • Oh, that tricky OUTPUT clause. Yes it can be done.

    INSERT INTO #Temp1
OUTPUT INSERTED.Value INTO #Temp2
SELECT V.val 
FROM (
    VALUES ('A'), ('B'), ('C')
) V(val)

    Note that with this form of the INSERT, both target tables need to be created in advance.

    See this db<>fiddle.