use variable in FROM statement

Question

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.

You might be interested in How to: Execute Statements Against Multiple Servers Simultaneously — Martin Smith
– Martin Smith, Commented Sep 20, 2012 at 8:52

Robert · Accepted Answer · 2012-09-20 09:21:47Z

7

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.

edited Sep 20, 2012 at 9:21

answered Sep 20, 2012 at 8:42

Robert

25.8k8 gold badges70 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

GarethD Over a year ago

You cannot use SP_EXECUTESQL in this way, you still have to use string concatenation to make the query in the same way as you did for EXEC(), although I'd still advocate using SP_EXECUTESQL. I would also advise using 'SELECT * FROM ' + QUOTENAME(@Servername) + '.DATABASE.SCHEMA.TABLE' to allow for any server names with reserved characters in, or Server names that are reserved words.

Robert Over a year ago

@GarethD t-clausen.dk You have right! Thanks! I edited the. post

Damien_The_Unbeliever Over a year ago

@t-clausen.dk - what's wrong with declare inside a loop? declares don't participate in control flow at all.

Robert Over a year ago

@Damien_The_Unbeliever Maybe it's work correctly It's not good practice.

Henrov Over a year ago

@Parado: could you elaborate on that?

|

Collectives™ on Stack Overflow

use variable in FROM statement

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related