CODEWITHSUNDEEP
sqlsql-servert-sqlopenquery
Mikkel Paulsen
Mikkel Paulsen

Reputation: 75

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

Upvotes: 0

Views: 908

Answers (2)

Mikkel Paulsen
Mikkel Paulsen

Reputation: 75

The answer i have, is this:

USE [DataBase]
GO

DROP SYNONYM [dbo].[eCallByCallStat]
GO

declare @tablename varchar(50) 

set @tablename = 'Server1..dbo.eCallByCallStat'+substring(cast(cast(getdate()-1 as date) as varchar(50)),1,4)+substring(cast(cast(getdate()-1 as date) as varchar(50)),6,2)+substring(cast(cast(getdate()-1 as date) as varchar(50)),9,2)

declare @sql varchar(500)

set @sql = 'CREATE SYNONYM [dbo].[eCallByCallStat] FOR ' + @tablename 

exec (@sql)

This will run everymorning updating the table name in the synonym, and then we will insert that data into a prober table so we have all the data.

Upvotes: 0

Intern87
Intern87

Reputation: 479

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

Upvotes: 1

Related Questions