Working With Table name as a Variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Working With Table name as a Variable

Author

Topic

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-01-28 : 13:51:53

hi gurus,

I'm trying to select from a Linker server, using variables as server, db, table names.

IF OBJECT_ID(@table,'U') is not null
BEGIN
PRINT 'table exists'
select @sql = 'INSERT INTO [' + @table + ']
Select * from [' + @server + '.' + @database + '.' + @owner + '.' + @table +']';
exec (@sql);
END

I'm get error - Invalid object name 'PNCLRMDB.AVNAPPDB.DBO.AC_ACI_AIRCRAFT_INFO'.

However, select * from PNCLRMDB.AVNAPPDB.DBO.AC_ACI_AIRCRAFT_INFO completes successfully.

Can anyone tell me what I've done incorrectly?
Thanks, John

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-28 : 15:01:05

It's in your brackets. Instead of exec(@sql), do print @sql
your code looks like this [servername.databasename.owner.table] and should like this
[servername].[databasename].[owner].[tableName]

Jim

Everyday I learn something that somebody else already knew

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-01-28 : 15:48:09

Thanks jimf.

This is what I have now:

set @sql = 'INSERT INTO [' + @table + ']
Select * from [' + @server + '] + [' + @dot + '] + [' + @database + '] + [' + @dot + '] + [' + @owner + '] + [' + @dot + '] + [' + @table + ']';
exec (@sql);

But I get invalid systax near '+' Well that's not much help because I have so many +s

As you see, I decided to define a variable for the period separator.
thanks, John

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-28 : 16:06:28

You can't put brackets around the dots. Try this instead:

set @sql = 'INSERT INTO ' + quotename(@table) + '
Select * from ' + quotename(@server) + @dot + quotename(@database) + @dot + quotename(@owner) + @dot + quotename(@table);
print (@sql);

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-01-28 : 16:41:57

Wow thanks, Robvolk. I didn't know about the quotename trick. That makes it so much easier.

Works well.

John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources