SET parameters to microsoft SQL server to set environment

I have created some microsoft SQL server queries to insert values into some of my tables created. Below are the sql queries i used.

INSERT INTO dev_data.extract VALUES ('a-dev-extract', '/dev/dev_data/storage/a' ;
INSERT INTO dev_data.extract VALUES ('b-dev-extract', '/dev/dev_data/storage/b' ;
INSERT INTO dev_data.extract VALUES ('c-dev-extract', '/dev/dev_data/storage/c' ;
INSERT INTO dev_data.extract VALUES ('d-dev-extract', '/dev/dev_data/storage/d' ;
INSERT INTO dev_data.extract VALUES ('e-dev-extract', '/dev/dev_data/storage/e' ;
INSERT INTO dev_data.extract VALUES ('f-dev-extract', '/dev/dev_data/storage/f' ;

Above is a piece of code which i have used for dev environment. Same code with prod instead of dev should be used for production.

Is there any way to parameterize dev and prod to achieve this?

For example lets take a single query as below:

INSERT INTO dev_data.extract VALUES ('a-dev-extract', '/dev/dev_data/storage/a' ;

Expected output for prod is as below:

INSERT INTO prod_data.extract VALUES ('a-prod-extract', '/prod/prod_data/storage/a' ;

YES I cpould use notepad to find and replace dev with prod, but the ultimate aim is to keep a common code that can be used in different environments.

I have tried as below:

DECLARE @env varchar;
SET @env = 'dev';
INSERT INTO @env+_data.extract VALUES ('a-+@env+-extract', '/@env/@env+_data/storage/a' ;

Tried with

SET @env = 'dev'
SELECT 'INSERT INTO ' + @env + '_data.extract VALUES (''a-' + @env + '-extract'',''/' + @env + '/' + @env + '_data/storage/a'')'

Am getting the output as a string, not executing the query. how to execute this query part? Instead of SELECT do we need to ADD some other command?

Any leads appreciated!

Answer

You can try this:

SET @env = 'dev';
SELECT CONCAT("INSERT INTO ", @env, "_data.extract VALUES (", "'a-",@env,"-extract', '/", @env, "/", @env, "_data/storage/a')") FROM DUAL

Snapshot:

Updated Query:

DECLARE @env varchar(10) 
DECLARE @qry varchar(200) 
SET @env = 'dev'
SET @qry = 'INSERT INTO ' + @env + '_data.extract VALUES (''a-' + @env + '-extract'',''/' + @env + '/' + @env + '_data/storage/a'')'
EXECUTE(@qry)

Advertisement

Answer