When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
If you want a variable that you can query like a temporary table, you will have to declare a table variable and insert the names into that "table", afterwards you can run a select statement against that variable: Declare @variable table (name nvarchar(128)); INSERT INTO @variable (name) SELECT name FROM sys.
A row value can be assigned to a variable of type row by using a SELECT INTO statement, a VALUES INTO statement, or a FETCH INTO statement. The field values of the source row value must be assignable to the field values of the target row variable.
If you wanted to simply assign some variables for later use, you can do them in one shot with something along these lines:
declare @var1 int,@var2 int,@var3 int;
select
@var1 = field1,
@var2 = field2,
@var3 = field3
from
table
where
condition
If that's the type of thing you're after
You cannot SELECT .. INTO .. a TABLE VARIABLE. The best you can do is create it first, then insert into it. Your 2nd snippet has to be
DECLARE @TempCustomer TABLE
(
CustomerId uniqueidentifier,
FirstName nvarchar(100),
LastName nvarchar(100),
Email nvarchar(100)
);
INSERT INTO
@TempCustomer
SELECT
CustomerId,
FirstName,
LastName,
Email
FROM
Customer
WHERE
CustomerId = @CustomerId
you can do this:
SELECT
CustomerId,
FirstName,
LastName,
Email
INTO #tempCustomer
FROM
Customer
WHERE
CustomerId = @CustomerId
then later
SELECT CustomerId FROM #tempCustomer
you doesn't need to declare the structure of #tempCustomer
It looks like your syntax is slightly out. This has some good examples
DECLARE @TempCustomer TABLE
(
CustomerId uniqueidentifier,
FirstName nvarchar(100),
LastName nvarchar(100),
Email nvarchar(100)
);
INSERT @TempCustomer
SELECT
CustomerId,
FirstName,
LastName,
Email
FROM
Customer
WHERE
CustomerId = @CustomerId
Then later
SELECT CustomerId FROM @TempCustomer
Sounds like you want temp tables. http://www.sqlteam.com/article/temporary-tables
Note that #TempTable is available throughout your SP.
Note the ##TempTable is available to all.
I found your question looking for a solution to the same problem; and what other answers fail to point is a way to use a variable to change the name of the table for every execution of your procedure in a permanent form, not temporary.
So far what I do is concatenate the entire SQL code with the variables to use. Like this:
declare @table_name as varchar(30)
select @table_name = CONVERT(varchar(30), getdate(), 112)
set @table_name = 'DAILY_SNAPSHOT_' + @table_name
EXEC('
SELECT var1, var2, var3
INTO '+@table_name+'
FROM my_view
WHERE string = ''Strings must use double apostrophe''
');
I hope it helps, but it could be cumbersome if the code is too large, so if you've found a better way, please share!
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