Can SQL Server store query results directly into a variable?

Data and Databases
1

I’m working on a stored procedure in SQL Server 2008 and I’m trying to find a way to store query results directly into a variable. Here’s what I want to do:

DECLARE @CustomerInfo TABLE
(
   Id uniqueidentifier,
   Name nvarchar(50),
   Email nvarchar(100)
);

SELECT 
    Id, 
    Name, 
    Email 
INTO 
    @CustomerInfo 
FROM 
    Users
WHERE 
    Id = @UserId

But this doesn’t work. SQL Server doesn’t like it. I know I can use separate variables for each column, but that feels clunky. Is there a better way to grab multiple columns from a single row and store them for later use in the procedure? I want to avoid running multiple queries on the same table if possible. Any ideas?

2

While SQL Server doesn’t allow direct insertion of query results into a table variable as you’ve shown, there’s a workaround using INSERT INTO. Here’s how you can achieve what you’re looking for:

DECLARE @CustomerInfo TABLE
(
Id uniqueidentifier,
Name nvarchar(50),
Email nvarchar(100)
);

INSERT INTO @CustomerInfo (Id, Name, Email)
SELECT Id, Name, Email
FROM Users
WHERE Id = @UserId;

This method populates your table variable with the query results. You can then use @CustomerInfo throughout your stored procedure as needed. It’s efficient and avoids multiple queries on the Users table. Remember, table variables are best for small result sets. For larger datasets, consider using a temporary table instead.

3

hmm, interesting question! have u considered using the OUTPUT clause? it might work for wat ur trying to do. something like:

DECLARE @CustomerInfo TABLE (…);
INSERT INTO @SomeTable
OUTPUT INSERTED.Id, INSERTED.Name, INSERTED.Email INTO @CustomerInfo
SELECT …

what do you think? could that solve ur problem?

4

hey mate, try using a cursor: declare vars, open a cursor on your query, fetch results, then close it. not as effiecient but works fine. hope it helps!