Data loading through t-script from stage to dev or qa or prod.

Question

Data loading through t-script from stage to dev or qa or prod.

dsh_roy

Old Hand

Points: 335
More actions
June 15, 2012 at 9:25 am

#257607

Hi,
following is the code snippet to load data from stage to target in dev/qa/prod environments. both dev and qa are on the same server. and prod is in different server.
----When I load data from stagdb to DEV db
Use stageDB
go
SELECT a.col1,
a.col2,
a.col3
FROM StageTable a
WHERE NOT EXISTS(SELECT * FROM [TargetDB_Dev].[dbo].[TargetTable] b
WHERE B.col1 = A.col1
)
----When I load data from stagdb to QA db
Use stageDB
go
SELECT a.col1,
a.col2,
a.col3
FROM StageTable a
WHERE NOT EXISTS(SELECT * FROM [TargetDB_QA].[dbo].[TargetTable] b
WHERE B.col1 = A.col1
)
----When I load data from stagdb to Production db
Use stageDB
go
SELECT a.col1,
a.col2,
a.col3
FROM StageTable a
WHERE NOT EXISTS(SELECT * FROM [TargetDB_Prod].[dbo].[TargetTable] b
WHERE B.col1 = A.col1
)
My problem is everytime based on the environment I am running this code , i need to change the entire script whereever I used [TargetDB_Dev], I need to change either _QA or _prod. sometime if I forget any of it to change, it is problem.
Is there any way that load data from stage to target without changing in the script? One more thing is I do not want to use dynamic t-sql.
I greatly appreciate your help.
thanks
disha

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 1

You could set a synonym in each database that points to the appropriate environment and then make that the same in each database. Then use the synonym in your script.

Lowell SSC Guru Points: 323518 More actions · Answer 2

here's one way to do it, but at some point, you have to make a logical decision about what database to use.

by using synonyms, you can reuse the same code, and by simply uncommenting out the example for the parameter @DBA_Decision,

you get what you are after.

something like this:

DECLARE @DBA_Decision int = -1

--SET @DBA_Decision =1

--If the decision is not made, raise an error so horrible it dosconnects you from the server

If @DBA_Decision < 0

raiserror ('Connection Aborted because you did not specify which target source for the staging data!' ,20,1) with nowait

--make sure you are in the StageDb database.

IF DB_NAME() <> 'stageDB'

raiserror ('Connection Aborted because you are in the wrong database for the staging data!' ,20,1) with nowait

--drop the previous synonym

IF EXISTS(SELECT * FROM stageDB.sys.synonyms WHERE name = 'SynTargetTable')

BEGIN

DROP SYNONYM SynTargetTable;

END

--create a synonym for flexibility

IF @DBA_Decision = 1

BEGIN

CREATE SYNONYM SynTargetTable FOR [TargetDB_Dev].[dbo].[TargetTable];

END

ELSE

BEGIN

CREATE SYNONYM SynTargetTable FOR [TargetDB_QA].[dbo].[TargetTable];

END

SELECT

a.col1,

a.col2,

a.col3

FROM stageDB.dbo.StageTable a

WHERE NOT EXISTS(SELECT *

FROM SynTargetTable b

WHERE B.col1 = A.col1

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dsh_roy Old Hand Points: 335 More actions · Answer 3

Hi lowell, and steve

It looks like this synonym concept is woking for me. I would have asked this question a couple months back.

I am very much greatful to you.

Disha.