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
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.
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.
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.
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?
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.
You'd have to do one or the other:
I'd also do this rather then TOP 0
SELECT * INTO #temp_copy FROM temp WHERE 1 = 0;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With