CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqldynamic-sql
Henrov
Henrov

Reputation: 1610

use variable in FROM statement

I am trying to create a query that will be executed once for a specific table in a specific database in each server in sys.server.

For each server.database.dbo.table I want to know the contents.

So what I need is something like:

declare @numrows int = (select count(*) from sys.servers)
declare @i int = 1

while @i <= @numrows
BEGIN
declare @servername varchar(max) = (select servernaam from #servers where rij = @i)

select * from @servername.DATABASE.DBO.TABLE

set @i = @i+1

END

However, the @servername in @servername.DATABASE.DBO.TABLE does not seem to work.

Suggestions? Thanks for thinking with me.

Upvotes: 2

Views: 4509

Answers (1)

Robert
Robert

Reputation: 25753

You have to use dynamic sql:

declare @numrows int = (select count(*) from sys.servers)
declare @i int = 1
declare @Sql(1000)
declare @servername varchar(max)

while @i <= @numrows
BEGIN
select @servername = servernaam 
from #servers where rij = @i

set @Sql = 'select * from '+@servername+'.DATABASE.DBO.TABLE'
exec(@Sql)

set @i = @i+1

END

Here is more informations about exec.

Upvotes: 7

Related Questions