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.
SELECT *
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')'
exec (@sql)
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.
Try this:
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
SET @RowsToProcess=@@ROWCOUNT
SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@SelectCol1=col1
FROM @table1
WHERE RowID=@CurrentRow
--do your thing here--
END
How to concatenate a variable and string as a table name
You need to use dynamic SQL:
DECLARE @sql NVARCHAR(max);
SET @sql = '
SELECT *
INTO ' + @ENTITY + '_COMPLETENESS_NAME
FROM TABLE1';
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 = '
SELECT *
INTO ' + QUOTENAME(@ENTITY + '_COMPLETENESS_NAME') +
FROM TABLE1';
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:
EXECUTE SP_EXECUTESQL
N'
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);
EXECUTE SP_EXECUTESQL
N'select * from @dynvariable',
N'@dynvariable [UDTT] READONLY',
@dynvariable=@variable;
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,
@payment_date,
@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
INSERT(o.id,
o.payment_id,
o.amount)
VALUES(s.id,
s.payment_id,
s.amount);
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.
For example;
DECLARE @tableVar1 VARCHAR(255) = 'T_Atable1'
DECLARE @tableVar2 VARCHAR(255) = 'T_Atable2'
DECLARE @tableVar3 VARCHAR(255) = 'T_Atable3'
EXEC('
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:
Static query:
SELECT * FROM [DB_ONE].[dbo].[ACTY]
EXCEPT
SELECT * FROM [DB_TWO].[dbo].[ACTY]
Since I want to easily change the name of table
and 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) + '
EXCEPT
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
Related Topics
Complex Sort of Field "String - Number - String"
Ora-00936: Missing Expression Oracle
Trouble Making a Running Sum in Access Query
Select Top Rows Until Value in Specific Column Has Appeared Twice
Cursor in Procedure Returning More Values Than Query
Combine Two SQL Queries in One Statement
How to Convert "2019-11-02T20:18:00Z" to Timestamp in Hql
Ordered Count of Consecutive Repeats/Duplicates
Search If Number Is Contained Within an Expression Like: 1-3,5,10-15,20
Issue Related to Join in Ms Access Database
How to Find 11Th Entry in SQL Access Database Table
Passing Multiple Values in Single Parameter
How to Merge Two or More Rows Based on Their Foreign Key