Specify the DB Name Dynamically in a Query

Question

I have this Monster SQl Query that looks to fetch data from a Staging Database, do a join on the Destination Database and insert/update data on a destination Table.

A simplified format would be somehting like this

MERGE INTO SOURCEDB.SCHEMA.DESTIANTIONTABLE AS TARGET
USING (SELECT COLA, COLB, COLC FROM STAGEDB.SCHEMA.SOURCE A INNER JOIN SOURCEDB.Schema,TABLEA) AS SOURCE
ON TARGET.ID = SOURCE.ID
WHEN MATCHED THEN UPDATE--

WHEN NOT MATCHED INSERT

Can i set the STAGING DB Name dynamically, the query is huge so i do not want to use the exec SQl syntax. Any suggestions?

i was gonna suggest EXEC SQL until you poo-pood that idea :) — Randy
– Randy, Commented Aug 1, 2012 at 20:33
thanks for the edits - bluefeet. @Randy, the query as i said is huge i have about 60 columns and the names are long as well. so the exec sql mode will be cumbersome to maintain for the support group. — Dinesh Menon
– Dinesh Menon, Commented Aug 2, 2012 at 17:36

Justin Swanhart · Accepted Answer · 2012-08-01 21:00:37Z

3

You should be able to use synonyms.

DROP SYNONYM syn_a;
CREATE SYNONYM a FOR STAGEDB.SCHEMA.SOURCE;
DROP SYNONYM syn_b;
CREATE SYNONYM b FOR STAGEDB.SCHEMA.TABLEA;
DROP SYNONYM syn_target;
CREATE SYNONYM target FOR SOURCEDB.SCHEMA.DESTINATIONTABLE;

MERGE INTO syn_target as TARGET
USING (SELECT COLA, COLB, COLC FROM syn_a as A INNER JOIN syn_b as B
) AS SOURCE
ON TARGET.ID = SOURCE.ID
WHEN MATCHED THEN UPDATE--

WHEN NOT MATCHED INSERT

answered Aug 1, 2012 at 21:00

Justin Swanhart

1,86413 silver badges16 bronze badges

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

1 Comment

Dinesh Menon Over a year ago

Thanks GreenLion, Synonym it is :)

Snives · Accepted Answer · 2012-08-01 21:19:21Z

0

Sql Server doesn't like to substitute @variables for the database name, schema, or table, which is probably what you are after, so one way or the other you will need to go the dynamic sql route by using EXEC.

There isn't a problem executing very large sql statements in Sql Server 2005 and later when you use nvarchar(max) data type. The limit is the lesser of 2Gb or server memory.

Declare @Sql nvarchar(max)
set @Sql = '
MERGE INTO SOURCEDB.SCHEMA.DESTIANTIONTABLE AS TARGET
USING (SELECT COLA, COLB, COLC FROM '+@StageDB+'.SCHEMA.SOURCE A INNER JOIN SOURCEDB.Schema,TABLEA) AS SOURCE
ON TARGET.ID = SOURCE.ID
WHEN MATCHED THEN UPDATE--

WHEN NOT MATCHED INSERT ...
'
exec (@Sql)

answered Aug 1, 2012 at 21:19

Snives

1,26412 silver badges21 bronze badges

1 Comment

Dinesh Menon Over a year ago

the query as i said is huge i have about 60 columns and the names are long as well. so the exec sql mode will be cumbersome to maintain for the support group. –

Collectives™ on Stack Overflow

Specify the DB Name Dynamically in a Query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related