CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Zanoni
Zanoni

Reputation: 31028

How can I create two temporary tables with the same structure without write twice?

How can I create two temporary tables with the same structure without write twice?

Something like that:

DECLARE @TEST_TABLE1, @TEST_TABLE2 TABLE
(
    FIELD1 INT,
    FIELD2 INT
)

and NO:

DECLARE @TEST_TABLE1 TABLE
(
    FIELD1 INT,
    FIELD2 INT
)

DECLARE @TEST_TABLE2 TABLE
(
    FIELD1 INT,
    FIELD2 INT
)

Upvotes: 1

Views: 5309

Answers (3)

Yoav
Yoav

Reputation: 170

Create the first temp table, then select into a second temp table:

-- Create first temp table

CREATE TABLE #TEST_TABLE1 ( FIELD1 int ,FIELD2 int )

-- Select into second temp table

SELECT * INTO #TEST_TABLE2 FROM #TEST_TABLE1

-- Vet existence of both temp tables

SELECT * FROM #TEST_TABLE1

SELECT * FROM #TEST_TABLE2

Upvotes: 0

KM.
KM.

Reputation: 103707

These are not "temp tables", a temp table is CREATE TABLE #TempTable(x int)

to make this work for true table tables, try:

CREATE TABLE #TempTable(x int)
insert into #TempTable values(5) --test data to show no data copied to new table
select * into #tempTable2 from #TempTable where 1=2
select * from #TempTable
select * from #TempTable2

These are table vaiables (@tableVariable) and you have to declare each variable, there is no way around it.

Upvotes: 6

James Black
James Black

Reputation: 41838

The only very non-standard way I can think this may work is to just write to the sys.tables directly, but you would still have to do two inserts, but you are doing the tables at the same time.

That may not be what you want, but short of using a stored procedure, and making one call from your app, and two on the database I can't think of any other solution.

Upvotes: 0

Related Questions