4

I came across the following script and I am trying to understand it.

select columnA, columnB, columnC
  into #tmpTable
  from tableA

The #tmpTable is referenced after that query, but not before it. There is not a declaration for #tmpTable anywhere in the sql script.

My somewhat general question is:

Will the above create the temp table automatically and why? Is this only for T-SQL or applies generally?

0

2 Answers 2

7

It's not only about Temporary table it's about the INTO Statement in Sql Server. The INTO statement creates a table in the Database and this table is equal to same table we create with Create Table statement.

But this statement does not inherit any constraints.

3

Using your example you are utilising the

SELECT ... INTO ... FROM

SQL syntax. This syntax creates a new table and insert the resulting rows from the query into the new table.

select columnA, columnB, columnC
into #tmpTable
from tableA

Using the above example a new table, #tmpTable will be created with the data from coulmnA, columnB and columnC from tableA. The data types in #tmpTable will match the datatypes of tableA.

You can of course create a true physical table and have multiple tables in your select statement.

select t1.columnA, t1.columnB, t1.columnC,t2.columnD
into #tmpTable
from tableA t1
join tableB t2
on t1.id = t2.id

If you do create a table using this syntax then you should be aware that Indexes, Constraints and Triggers are not added to the new table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.