How to Dump data into temp table from inserted magical table

Question

How to Dump data into temp table from inserted magical table

Sudip Bhatt 2,281

Select *
     From sourceTable st;

  Declare @outputTable Table (col1 int, col2 int, col3 int);

 ; WITH CTE AS (
    SELECT TOP (5) * FROM sourceTable WITH (READPAST)
    ORDER  BY col1
 )   
   Update Top (5)
          CTE
      Set readFlag = 1
    Output inserted.col1, inserted.col2, inserted.col3 Into @outputTable
    Where readFlag = 0

 SELECT * FROM @outputTable

How to dump data to #tmp data from inserted magical table ? i do not want to create temp table rather i want to use select * into tmp date from inserted magical table.....how is it possible ?

Thanks

Accepted answer

0 additional answers

Your answer

Answer 1

Stefan Hoffmann 621

You can use any table-like destination for the OUTPUT..INTO clause. It is not limited to table variables. E.g. using a table:

USE tempdb;
GO

DROP TABLE IF EXISTS #Data;
CREATE TABLE #Data (
    ID INT ,
    Payload INT
);

DROP TABLE IF EXISTS #Temp;
CREATE TABLE #Temp (
    ID INT ,
    NewPayload INT ,
    OldPayload INT
);

INSERT INTO #Data ( ID ,
                    Payload )
VALUES ( 1, 1 );

UPDATE #Data
SET    Payload += 1
OUTPUT Inserted.ID ,
       Inserted.Payload ,
       Deleted.Payload
INTO #Temp;

SELECT *
FROM   #Data D;

SELECT *
FROM   #Temp T;

Sudip Bhatt 2,281 Reputation points

2020-09-18T17:58:37.277+00:00

I do not want to create temp table rather i want to create temp table at run time just like

Select * into #tmp from sourcetable.

here you create temp table which i do not want. just tell me how could i dump inserted table data into temp table at runtime without creating #temp table ?

suppose i am updating big table then it is not possible to mention Inserted.col1,Inserted.col2,Inserted.col3 this way all column. so i want something like select * into temp table from Inserted

is it possible ?

how to store data into output clause ? if possible then i can extract data from output clause and insert into temp table. if possible please show me by sample code. thanks
Stefan Hoffmann 621 Reputation points

2020-09-21T08:16:30.597+00:00
Well, just create it manually..

SELECT youColumnList INTO #tmp FROM yourTable WHERE 0 = 1;

suppose i am updating big table then it is not possible to mention

It is possible, being lazy isn't a reason. And keep in mind, SQL is declarative and schema based, so coding this explicitly is in most cases the correct approach, You can create the table creation statement once during development or deployment using dynamic SQL,

how to store data into output clause ?

D'oh? You store it in a table or table variable.. obviously.
Sudip Bhatt 2,281 Reputation points

2020-09-22T12:38:01.967+00:00

I want to dump magic table data into #tmp table without creating #tmp table with column definition. see this code

UPDATE #Data
SET Payload += 1
OUTPUT Inserted.ID ,
Inserted.Payload ,
Deleted.Payload
INTO #Temp;

here column name mention from Inserted magic table which i do not want. so tell me is it possible ?
Stefan Hoffmann 621 Reputation points

2020-09-22T12:54:12.583+00:00

It should be already obvious:

The OUTPUT..INTO clause is not the same as the SELECT..INTO clause. The destination for the OUTPUT..INTO clause must exists before using it. There is no auto-creation possible.

Share via

How to Dump data into temp table from inserted magical table

0 additional answers

Your answer