Inserting query results into a table variable in T-SQL

Data and Databases
1

I need to load a result set into a table variable, but T-SQL does not allow SELECT INTO for table variables. What alternative methods can I use?

DECLARE @tempData TABLE (
    userName NVARCHAR(30),
    userCity NVARCHAR(30)
);

INSERT INTO @tempData (userName, userCity)
SELECT fullName, city FROM EmployeeRecords
WHERE age > 40;
2

One viable alternative is to leverage the OUTPUT clause with your DML statements. This approach allows capturing the results of an INSERT, UPDATE, or DELETE operation directly into a table variable. Indeed, I have used the OUTPUT clause to record changes or capture newly inserted identifiers without the need for a separate SELECT statement. This method can simplify both code maintenance and debugging. Although its performance is generally comparable to explicit INSERT INTO with a SELECT, it offers an integrated solution for scenarios that require tracking or further processing of affected rows.

3

While the typical solution involves using an explicit INSERT INTO statement to load data into a table variable as outlined, another viable approach is to consider using a temporary table if the goal is to handle a larger result set or leverage additional features such as indexes and statistics. In many instances, table variables perform well for smaller data volumes, but I have encountered situations where switching to a temporary table provided better performance and flexibility, especially with complex joins and multiple operations.

4

hey, have you tried using a cte to prep your data before inserting it? i think it can keep things more organized. what pros or cons have you run into with that approach?

5

hey, you could also wrap your query in a table-valued fn and then insert its output. it kinda tidies up the code, but performance may differ so be sure to test on your data.