How can I utilize SELECT INTO with table variables in SQL Server?

Data and Databases
1

I am currently engaged in a project using SQL Server, and I’m seeking guidance on how to fill a table variable with data using the SELECT INTO method. I have a stored procedure that operates correctly with INSERT and OUTPUT, but I am eager to make my code more efficient.

Here’s an example of my current setup:

CREATE PROCEDURE [dbo].[Product_AddProduct]
    @ClientId uniqueidentifier,
    @ProductDescription nvarchar(100),
    @CategoryId int,
    @User nvarchar(100)
AS
BEGIN
    DECLARE @RelatedCategoryId uniqueidentifier;
    SET @RelatedCategoryId = 
    (
       SELECT top(1) CategoryId 
       FROM Categories 
       WHERE ClientId = @ClientId
    ) 

    DECLARE @ProductTable TABLE
    (
        ProductId uniqueidentifier,
        ClientId uniqueidentifier,
        ProductDescription nvarchar(100),
        CategoryId int,
        User nvarchar(100),
        CreatedAt datetime
    );

    INSERT INTO Product
    OUTPUT INSERTED.* INTO @ProductTable
    SELECT NEWID(), @ClientId, @ProductDescription, @CategoryId, @User, GETDATE()
END

Now, I wish to achieve something similar to this:

DECLARE @CustomerInfo TABLE
(
   CustomerId uniqueidentifier,
   FirstName nvarchar(100),
   LastName nvarchar(100),
   Email nvarchar(100)
);

SELECT 
    CustomerId, 
    FirstName, 
    LastName, 
    Email 
INTO 
    @CustomerInfo 
FROM 
    Customers
WHERE 
    CustomerId = @CustomerId

However, I encounter an error when attempting this method. Is it possible to directly populate a table variable without resorting to separate INSERT queries or executing multiple queries on the same table?

2

yup, that’s right! SQL Server won’t let ya use SELECT INTO with table vars. super annoying! just go with INSERT INTO…SELECT instead, it’s the way to go. don’t worry about perf, it’s pretty much on par. your OUTPUT setup is good too!

3

Nope, SQL Server doesn’t let you use SELECT INTO with table variables. It’s a built-in limitation - SELECT INTO only works for creating permanent or temp tables, not for populating table variables.

I ran into this same issue when I started with SQL Server. The fix is simple: use INSERT INTO…SELECT instead. It does the exact same thing and performs just as well. You’re already on the right track with INSERT and OUTPUT.

For your customer example, just do this:

INSERT INTO @CustomerInfo (CustomerId, FirstName, LastName, Email)
SELECT CustomerId, FirstName, LastName, Email 
FROM Customers
WHERE CustomerId = @CustomerId

Performance is identical to SELECT INTO, and it works with table variables.

4

Wait, how large are your datasets? The OUTPUT clause you’re using might actually be faster than other approaches. Have you compared execution plans between your current method and a regular INSERT INTO…SELECT? What kind of performance boost are you hoping for?