How to use two tables , each from different database into one stored procdure

Question

Post reply

How to use two tables , each from different database into one stored procdure

Learner44

SSCarpal Tunnel

Points: 4466
More actions
May 29, 2013 at 1:09 pm

#294531

My Soruce table A is in A1 database and Destincation table B is in A2 database , how I can call them in one stored procedure as in paramater value.
http://www.sqlservercentral.com/articles/EDW/77100/ , in this it is using two tables from same database , but i want to know how we can use this SP between two database tables.
Please help me, If someone has any idea.
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Use a fully qualified name,

such as server_name.[database_name].[schema_name].object_name

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 2

Hi ,

But the issue is when I supply full name into GUI of the stored procedure, it gives an error due to '.' (period) because one my table reside on the linked server.

and other is in the normal database folder of Sql Server 2008 r2.

can you please guid me in this.?

thanks.

Pratik.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Does your linked server is named by its IP address?

Here's an example:

SELECT *

FROM [192.168.0.1].[Adventureworks].[Production].[Product] AS p

You could also post the code you're using and the error you're getting. You can change names to mantain privacy.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 4

I can not post the code,because I am developing it from this http://www.sqlservercentral.com/articles/EDW/77100/ article.

in this article author has used tables from same database.

In my case, one table is under the database folder, and other table is in the ServerObject>Linked server>servername>defaults>tables

and now I don't know how I can provide the information about the table which resides in Server, in above procedure, where it ask for

@database name, @schema, @table name.

Please guide me.

Thanks.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

prtk.raval (5/29/2013)
I can not post the code,because I am developing it from this http://www.sqlservercentral.com/articles/EDW/77100/ article.
in this article author has used tables from same database.
In my case, one table is under the database folder, and other table is in the ServerObject>Linked server>servername>defaults>tables
and now I don't know how I can provide the information about the table which resides in Server, in above procedure, where it ask for
@database name, @schema, @table name.
Please guide me.
Thanks.

Are you trying to do a merge between two tables on different servers?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 6

ya...

One is in the SQL SERVER 2008 R2 and one is on the ORACLE.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

prtk.raval (5/29/2013)
ya...
One is in the SQL SERVER 2008 R2 and one is on the ORACLE.

I don't think that is going to work. There may be somebody out there that has made it work but with my knowledge of MERGE you are not going to be able to use MERGE.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/