Use SELECT result for another query

Question

Is there any way other than using a cursor that I can use SELECT results for a subsequent INSERT/UPDATE query?

Something like:

DECLARE @SELECTRESULT;

SELECT Something into @SELECTRESULT
FROM Somewhere

INSERT INTO SomewhereElse (X, XX, XXX)
SELECT Something, GETDATE(), 'XXX'
FROM @SELECTRESULT

UPDATE Somewhere
Set SomethingElse = 'ABC'
WHERE
Something in
(SELECT Something FROM @SELECTRESULT)

The reason is that I have a relatively complex query from multiple tables and I don't want duplicate this code, once for the insert and second time for the update.

Depending on the [requirement][1] you can use a temp table or table variable. [1]: http://stackoverflow.com/questions/1597309/what-is-the-difference-between-temporary-table-and-table-variable-in-sql-2008 — Niladri Biswas, Aug 31 '12 at 04:56

score 0 · Accepted Answer · answered Aug 31 '12 at 04:26

0

You can use a table variable.

Something like

DECLARE @Table TABLE(
Col1 INT
)

INSERT INTO @Table
SELECT Col1
FROM Table

Have a look at

Table Variables In T-SQL

DECLARE @local_variable (Transact-SQL)

answered Aug 31 '12 at 04:26

Adriaan Stander

162,879
31
289
284

score 0 · Answer 2 · answered Aug 31 '12 at 08:20

0

Just use a temporary table it's ok for your case :

SELECT Something into #temporary_table FROM Somewhere WHERE blabla

answered Aug 31 '12 at 08:20

Hassan

1,413
1
10
12

score 0 · Answer 3 · answered Aug 31 '12 at 15:33

use a temp table.

CREATE TABLE #tempTable
(
Something int NOT NULL,
CurrentDate DateTime NULL,
    XXX Varchar(50)
)

Then use your complex query on multiple tables and insert the result set into the tempTable.

Insert into #tempTable
-- Complex Select Query

Just make sure that the columns returned by the selected query match in the order as per the #tempTable structure. Also the number of columns should match. Once you have #tempTable with the complex data, you can use it multiple number of times for insert and update queries.

INSERT INTO SomewhereElse (X, XX, XXX)
SELECT * from #tempTable

UPDATE Somewhere
Set SomethingElse = 'ABC'
WHERE
Something in (SELECT Something FROM #tempTable)

score 0 · Answer 4 · answered Aug 31 '12 at 17:48

DECLARE @SELECTRESULT table(Something nvarchar(50)) --the data that you could reuse.
insert into @SELECTRESULT(Something)
SELECT Something
FROM Somewhere

INSERT INTO SomewhereElse (X, XX, XXX)
SELECT Something, GETDATE(), 'XXX'
FROM @SELECTRESULT

UPDATE Somewhere
Set SomethingElse = 'ABC'
WHERE Something in
(SELECT Something FROM @SELECTRESULT)

Use SELECT result for another query

4 Answers4