July 9, 2013 at 10:41 am
I was wondering if its possible to have a variable hold the location of a database and table
so instead of writing
Select * from database1.dbo.table1
i want to write it like
Declare @Location varchar(30)
set @location = 'database1.dbo.table1'
Select * from @Location
The end result is to use this in a stored proc that accesses two different databases on the same server
July 9, 2013 at 10:57 am
not directly like that.
there's two ways to do it;
one is by using dynamic SQL, and the other is by using synonyms.
Declare @Location varchar(30)
set @location = 'database1.dbo.table1'
EXECUTE('Select * from ' + @Location)
synonym
IF OBJECT_ID('HENN', 'SN') IS NOT NULL
DROP SYNONYM HENN;
CREATE SYNONYM HENN FOR database1.dbo.table1;
SELECT * FROM HENN ;
Lowell
July 9, 2013 at 11:09 am
Thank you, I like the synonym one as its more like what i wanted.
Just to be sure, the performance and space for the synonym shouldn't be anything noteworthy right?
July 9, 2013 at 11:16 am
meltingchain (7/9/2013)
Thank you, I like the synonym one as its more like what i wanted.Just to be sure, the performance and space for the synonym shouldn't be anything noteworthy right?
nope no significant impact;
as far as usage, it's just an alias that gets resolved to a full object name, in the same way that SELECT * FROM Table1 eventually resolves to SELECT * FROM servername.databasename.dbo.Table1
a synonym must point at an object(must exist in some database's sys.objects), and not part of the name of an object.
Lowell
July 9, 2013 at 12:22 pm
Here's a snippet of code that I think does what you want. You can define the schema variables using the SYSNAME datatype, but you will still have to use dynamic SQL in most cases to use the variables in your procedure.
DECLARE
@SourceCatalogName SYSNAME
,@SourceSchemaName SYSNAME
,@SourceTablename SYSNAME
,@TargetCatalog SYSNAME
,@TargetSchema SYSNAME
,@TargetTable SYSNAME
,@strSQL NVARCHAR(MAX)
,@suffix NVARCHAR(60)
,@SourceFullPath SYSNAME
,@TargetFullPath SYSNAME
SET @SourceCatalogName = 'LocalTestDB'
SET @SourceSchemaName = 'dbo'
SET @SourceTablename = 'Tabs'
SET @TargetCatalog = 'LocalTestDB'
SET @TargetSchema = 'dbo'
SET @TargetTable = 'AAADynamicTempTable'
/* Unable to use #TempTable with dynamic SQL */
IF @TargetCatalog = 'tempdb'
SET @TargetTable = '#'+@TargetTable
SET @SourceFullPath =
CAST(@SourceCatalogName AS NVARCHAR(50))+'.'+
+CAST(@SourceSchemaName AS NVARCHAR(50))+'.'+
+CAST(@SourceTablename AS NVARCHAR(50))
SET @TargetFullPath =
CAST(@TargetCatalog AS NVARCHAR(50))+'.'+
+CAST(@TargetSchema AS NVARCHAR(50))+'.'+
+CAST(@TargetTable AS NVARCHAR(50))
/* Create one row in the target with columns from the source */
IF @TargetCatalog = 'tempdb'
BEGIN
--its a temp table so safe to delete
SET @strSQL = N'
IF OBJECT_ID('''+@TargetFullPath+''') IS NOT NULL
DROP TABLE '+@TargetTable+'
SELECT TOP(1) *
INTO '+CAST(@TargetFullPath AS NVARCHAR(100))+'
FROM '+CAST(@SourceFullPath AS NVARCHAR(100))
END
ELSE
BEGIN
SELECT @suffix =
CAST(CAST(DATEPART(minute,GETDATE()) AS NVARCHAR(20))
+CAST(DATEPART(second,GETDATE()) AS NVARCHAR(20))
+CAST(DATEPART(millisecond,GETDATE()) AS NVARCHAR(20))
AS NVARCHAR(60))
IF OBJECT_ID(@TargetFullPath) IS NOT NULL
BEGIN
SET @TargetTable = REPLACE(REPLACE(@TargetTable,'[',''),']','')
SET @TargetTable = @TargetTable+'_'+@suffix
SET @TargetTable = '['+@TargetTable+']'
SET @TargetFullPath =
CAST(@TargetCatalog AS NVARCHAR(50))+'.'+
+CAST(@TargetSchema AS NVARCHAR(50))+'.'+
+CAST(@TargetTable AS NVARCHAR(50))
END
SET @strSQL = N'
SELECT TOP(1) *
INTO '+CAST(@TargetFullPath AS NVARCHAR(100))+'
FROM '+CAST(@SourceFullPath AS NVARCHAR(100))
END
PRINT @strSQL
--EXEC sp_executeSQL @strSQL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply