Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server creating a temporary table from another table

I am looking to create a temporary table which is used as an intermediate table while compiling a report.

For a bit of background I am porting a VB 6 app to .net

To create the table I can use...

SELECT TOP 0 * INTO #temp_copy FROM temp;

This creates an empty copy of temp, But it doesn't create a primary key

Is there a way to create a temp table plus the constraints?

Should I create the constraints afterwards?

Or am I better off just creating the table using create table, I didn't want to do this because there are 45 columns in the table and it would fill the procedure with a lot of unnecessary cruft.

The table is required because a lot of people may be generating reports at the same time so I can't use a single intermediary table

like image 335
DeveloperChris Avatar asked Feb 03 '10 06:02

DeveloperChris


People also ask

How do I create a temp table from another table in SQL?

Create a Global Temporary Table in SQL Server. You can also create a global temporary table by placing double hash (##) before the temporary table name. The global temporary table will be available across different connections. 3 records will be inserted into the table.

How do you create a temporary table in SQL?

To create a Global Temporary Table, add the “##” symbol before the table name. Global Temporary Tables are visible to all connections and Dropped when the last connection referencing the table is closed. Global Table Name must have an Unique Table Name.

How do you reference a temporary table in SQL?

Temporary tables/procedures are normally created via a special syntax but are referenced by their names just like normal tables/procedures. For example, we use “CREATE [[GLOBAL] TEMPORARY] TABLE …” to create a temporary table in ORACLE, and it's referenced by its identifier/name once created.


3 Answers

Do you actually need a Primary Key? If you are flitering and selecting only the data needed by the report won't you have to visit every row in the temp table anyway?

like image 192
Mitch Wheat Avatar answered Oct 20 '22 06:10

Mitch Wheat


By design, SELECT INTO does not carry over constraints (PK, FK, Unique), Defaults, Checks, etc. This is because a SELECT INTO can actually pull from numerous tables at once (via joins in the FROM clause). Since SELECT INTO creates a new table from the table(s) you specify, SQL really has no way of determining which constraints you want to keep, and which ones you don't want to keep.

You could write a procedure/script to create the constraint automatically, but it's probably too much effort for minimal gain.

like image 31
George Dontas Avatar answered Oct 20 '22 06:10

George Dontas


You'd have to do one or the other:

  • add the PK/indexes afterwards
  • explicitly declare the temp table with constraints.

I'd also do this rather then TOP 0

SELECT * INTO #temp_copy FROM temp WHERE 1 = 0;
like image 6
gbn Avatar answered Oct 20 '22 06:10

gbn