Menu
I want to declare a table variable and fill it with a select, without having to explicitly define its columns. Does T-SQL allow something like this:
DECLARE @people TABLE() SELECT * FROM Persons;
Hypothetically, the above statement would match column types identically, and fill the @people table variable at the same time. :)
845
Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.
Setting a Value in a Transact-SQL Variable To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
You can't do it with a table variable since a variable has to be declared before it can be used, but you could use a temp table instead.
SELECT * INTO #people FROM Persons;
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
