Using dynamic Server and Database Name in SQL Server stored procedure over linked servers

Question

I have a requirement. Using SSIS i am import data from flat file/excel file into my staging table. From staging table i need to filter data and transfer it to different databases over different linked server.i.e. let say for California i have dbCalifornia on Server A, For Taxes i have dbTaxes on Server B etc etc. I need to read config table and redirect data accordingly.i.e. if column value =CALI insert data in dbCalifornia.tblA, for column value =TAX insert data in dbTaxes.tblA. I am trying to use Server Name and Database name as variable (because i am reading these from config table) i.e.

INSERT INTO [@server].[@database].[DBO].[BASIC]

But i am getting error . I am not expert DBA please suggest my solution how can i implement this scenario.

TIA

You should use SSIS to insert the data to the @server.@database directly. That is what SSIS was made for. Pushing data through a linked server with SSIS is a bad idea. — Wes H
– Wes H, Commented Oct 23, 2017 at 14:19

sepupic · Accepted Answer · 2017-10-23 17:44:01Z

1

You can do it using dynamic sql like this:

declare @server varchar(100), @database varchar(100);

DECLARE @sql varchar(8000) ='INSERT INTO[' + @server +'].['+ @database + '.[DBO].[BASIC]' + 
'(EmpID,EmployeeID,ADDR1, ADDR2, ADDR3,ADDR4,TELNUM,MARRIED,LNAME,MNAME,FNAME,' + 
'SEX, EMAIL,COUNTRYCODE, CITIZEN) ' + 
'select EmpID,EmployeeID,ADDR1, ADDR2, ADDR3,ADDR4,TELNUM,MARRIED,LNAME,MNAME,FNAME,
SEX, EMAIL,COUNTRYCODE, CITIZEN from dbo.myExcelTable where state = ' + @database;

exec(@sql);

I don't understand what are your 100 variables tht you use in your insert, didn't you say

if column value =CALI insert data in dbCalifornia.tblA, for column value =TAX insert data in dbTaxes.tblA. ?

So you just need to filter your table using @database value and insert those rows in corresponding table

edited Oct 23, 2017 at 17:44

answered Oct 23, 2017 at 12:30

sepupic

8,7371 gold badge11 silver badges21 bronze badges

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

3 Comments

Tech_starter Over a year ago

can you please give my example for insert statement with variables like with insert values stored in variable i.e. employerid etc etc. Because i am getting conversion error. TIA

sepupic Over a year ago

Post here your code, I cannot know what your types are, all that you pass within @sql should be STRING.

sepupic Over a year ago

I updated my answer but I still don't understand what are your "other" variables, don't you insert ROWS from your table and not variables?

Telmo Ivo · Accepted Answer · 2017-10-24 07:08:38Z

0

normally when reading a table from another server and DB. I use the LinkedServer, user and table like this:

Select * from Link..User.Table ;

after a linked server I have to use 2 dots.

I don't know if this can help since this is to read from an Oracle database.

answered Oct 24, 2017 at 7:08

Telmo Ivo

4,3381 gold badge18 silver badges23 bronze badges

Collectives™ on Stack Overflow

Using dynamic Server and Database Name in SQL Server stored procedure over linked servers

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related