Search code examples
sqlsql-servert-sqlopenquery

SQL OpenQuery variable tablename

I need to transfer data from a linked server to our main SQL server. The issue is, that the table name changes everyday. I have looked around this site to find out if it is even possible to have a variable database name, which it is, but also to see if it is possible to have variables in a OPENQUERY, which it also is.

But i am struggling to combine those needs, so i have a variable table name in a OPENQUERY.

I need something like this:

Declare @LinkedServer as varchar(max) = 'LinkedServer'
Declare @TName as varchar(max) = 'TName'+substring(cast(cast(getdate() as date) as
varchar(50)),1,4)+substring(cast(cast(getdate() as date) as
varchar(50)),6,2)+substring(cast(cast(getdate() as date) as
varchar(50)),9,2)

SELECT * FROM OPENQUERY(@LinkedServer, 'SELECT * FROM dbo.@TName')

Is there any way i can make a variable table name in a OPENQUERY ?

Thank you for your help.

/Mikkel

Solution

  • I'd write a synonym which gets updated every day before you kick off your data extraction job. Then you don't need to be updating (potentially a tonne of) references.

    CREATE SYNONYM LinkedTableA
FOR
ServerName.DBName.dbo.TName20170331

SELECT * FROM LinkedTableA