Got a complex SELECT query, from which I would like to insert all rows into a table variable, but T-SQL doesn't allow it.
Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries. http://odetocode.com/Articles/365.aspx
Short example:
declare @userData TABLE( name varchar(30) NOT NULL, oldlocation varchar(30) NOT NULL ) SELECT name, location INTO @userData FROM myTable INNER JOIN otherTable ON ... WHERE age > 30
The data in the table variable would be later used to insert/update it back into different tables (mostly copy of the same data with minor updates). The goal of this would be to simply make the script a bit more readable and more easily customisable than doing the SELECT INTO
directly into the right tables. Performance is not an issue, as the rowcount
is fairly small and it's only manually run when needed.
...or just tell me if I'm doing it all wrong.
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.
INSERT INTO SELECT statement reads data from one table and inserts it into an existing table. Such as, if we want to copy the Location table data into a temp table using the INSERT INTO SELECT statement, we have to specify the temporary table explicitly and then insert the data.
If we want to declare a table variable, we have to start the DECLARE statement which is similar to local variables. The name of the local variable must start with at(@) sign. The TABLE keyword specifies that this variable is a table variable.
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;
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