T-SQL: How to join @variable tables (another try)
Either give them an alias that you then refer to in the
JOIN or use square brackets. The below mixes both possibilities.
FROM @_Errors Errors
INNER JOIN @_Files
ON Errors.ID = [@_Files].ID
There is a Connect Item discussing this issue here
SELECT INTO a table variable in T-SQL
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;
How to use table variable in a dynamic sql statement?
Your EXEC executes in a different context, therefore it is not aware of any variables that have been declared in your original context. You should be able to use a temp table instead of a table variable as shown in the simple demo below.
create table #t (id int)
declare @value nchar(1)
set @value = N'1'
declare @sql nvarchar(max)
set @sql = N'insert into #t (id) values (' + @value + N')'
select * from #t
drop table #t
Can I loop through a table variable in T-SQL?
Add an identity to your table variable, and do an easy loop from 1 to the @@ROWCOUNT of the INSERT-SELECT.
DECLARE @RowsToProcess int
DECLARE @CurrentRow int
DECLARE @SelectCol1 int
DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int )
INSERT into @table1 (col1) SELECT col1 FROM table2
--do your thing here--
How to concatenate a variable and string as a table name
You need to use dynamic SQL:
DECLARE @sql NVARCHAR(max);
SET @sql = '
INTO ' + @ENTITY + '_COMPLETENESS_NAME
EXEC sp_executesql @sql;
Note: You cannot use a parameter for the table name, because you can't use parameters for table, column, schema, or database identifiers. To be safe, you might want to use:
SET @sql = '
INTO ' + QUOTENAME(@ENTITY + '_COMPLETENESS_NAME') +
This protects you if
@ENTITY has unusual characters.
How to use table variable in dynamic sql? OR create temporary table from user defined table type?
I can think of the following workarounds to solve this using your UDTT:
1. Declare the UDTT variable within your dynamic script and then you can as well retrieve results from there:
DECLARE @dynvariable [UDTT];
insert @dynvariable values (1);
select * from @dynvariable';
2. Pass the UDTT variable to the
SP_EXECUTESQL, but then it is readonly, meaning you can only
select within the dynamic script:
DECLARE @variable [UDTT];
insert @variable values (1);
N'select * from @dynvariable',
N'@dynvariable [UDTT] READONLY',
3. I think it's not possible to 'create a temp table from UDTT' so your approach would be to dynamically create the temp table using system information for your UDTT (columns, types, etc.).
4. Reading that you want to have a "dynamic" pivot code, the most appropriate would be to dynamically generate the pivot statement based on the columns info and values of the target table.
SQL MERGE with variables
You can use the
VALUES clause to make a single row derived table then the rest is as usual.
MERGE order AS o
USING (VALUES (@id,
@amount)) AS s(id, payment_date, amount)
ON s.id = o.id
WHEN MATCHED THEN
UPDATE SET o.payment_date = s.payment_date,
o.amount = s.amount,
o.last_updated_on = GETDATE()
WHEN NOT MATCHED THEN
You might want to read Use Caution with SQL Server's MERGE Statement as well though.
Use of variable table name in Query
You'll need to use Dyanmic SQL.
Dynamic SQL is simply that you build your query "dynamically" in a string either in your SQL procedure or in an application, and then you execute that string.
DECLARE @tableVar1 VARCHAR(255) = 'T_Atable1'
DECLARE @tableVar2 VARCHAR(255) = 'T_Atable2'
DECLARE @tableVar3 VARCHAR(255) = 'T_Atable3'
SELECT * FROM ' + @tableVar1 + '
WHERE ref IN (SELECT ref FROM ' + @tableVar2 + ')
AND customer IN (SELECT customer FROM ' + @tableVar3 + ')
A table name as a variable
For static queries, like the one in your question, table names and column names need to be static.
For dynamic queries, you should generate the full SQL dynamically, and use sp_executesql to execute it.
Here is an example of a script used to compare data between the same tables of different databases:
SELECT * FROM [DB_ONE].[dbo].[ACTY]
SELECT * FROM [DB_TWO].[dbo].[ACTY]
Since I want to easily change the name of
schema, I have created this dynamic query:
declare @schema sysname;
declare @table sysname;
declare @query nvarchar(max);
set @schema = 'dbo'
set @table = 'ACTY'
set @query = '
SELECT * FROM [DB_ONE].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
SELECT * FROM [DB_TWO].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table);
EXEC sp_executesql @query
Since dynamic queries have many details that need to be considered and they are hard to maintain, I recommend that you read: The curse and blessings of dynamic SQL