SQL Server Select into @Variable

SQL Server SELECT INTO @variable?

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

SQL Server - storing SELECT statement into a variable in a Stored Procedure

You should use SET

SET @process_PK = (SELECT process_pk 
    FROM dbo.Process 
    WHERE process_id = @process_id)

Store value from SELECT statement into variable on SQL Server

Your query selects all names and successively stores them in the variable, meaning that each name overwrites the previously stored name, so only the last selected name is available in the variable when the SELECT statement terminates. 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.synonyms 
where base_object_name = '[ABC].[dbo].[tblABC]';

select * from @variable;

But: Also on this query, the server will "care".

select value into variable and alias in SQL Server

A select can either assign values to variables or return column values, but not both.

In some cases, e.g. when using select to provide data to an insert, an output clause may be useful. A tool well worth having in your pocket as it provides access to identity values from insert and both before and after values when used with update.

Set Variable for Select Result in SQL Server

Your syntax is wrong for the SET

It should be

DECLARE @bDate DATE;
SET @bDate = (SELECT birth_date FROM person WHERE id='1');

SELECT @bDate;

OR, you can use SELECT

DECLARE @bDate DATE;
SELECT @bDate = birth_date FROM person WHERE id='1';

SELECT @bDate;

SELECT INTO a table variable in T-SQL

Try something like this:

DECLARE @userData TABLE(
    name varchar(30) NOT NULL,
    oldlocation varchar(30) NOT NULL
);

INSERT INTO @userData (name, oldlocation)
SELECT name, location FROM myTable
INNER JOIN otherTable ON ...
WHERE age > 30;

---

Related Topics