Menu
I want to use table variables in stored procedures but here is an issue. My tables are very large and declaring a table variable need a long code to write and debug as well.
Kindly advice me some way to declare table variables quickly, is it possible to create table variable based on an existing table ?
Or please share any tip to create code for creating table variable.
Thanks
873
Question: How can I create a SQL table from another table without copying any values from the old table? Answer: To do this, the SQL CREATE TABLE syntax is: CREATE TABLE new_table AS (SELECT * FROM old_table WHERE 1=2);
Table variables can be declared within batches, functions, and stored procedures, and table variables automatically go out of scope when the declaration batch, function, or stored procedure goes out of scope. Within their scope, table variables can be used in SELECT, INSERT, UPDATE, and DELETE statements.
Table variables allow us to create the following constraints: Primary Key. Unique.
Right click the table, choose Script As Create.
Replace create table xxx with declare @xxx table.
57
As discussed in this SO Question you can't select into a table variable.
When you say "large", if you mean a lot of columns, the best approach for you would probably be to script that table as create and save the definition and use that in your Declare statement.
If you mean large as far as the number of rows you'll have in the table variable, you may want to consider using a temporary table which you could then do a SELECT INTO statement to create it based off of the original.
SELECT * INTO #tmpTable FROM srcTable
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
