T-Sql: How to Join @Variable Tables (Another Try)

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



Leave a reply



Submit