How to join two databases - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to join two databases

Author

Topic

paulalvin
Starting Member

8 Posts

Posted - 2011-06-17 : 12:19:45

Guys i need your help on how to join two databases at a time please i need a hand thanks :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-17 : 13:47:31

By joining, do you mean writing queries against two databases, pulling data from tables/views in both at the same time,

OR

Merging two databases into one so you end up with a single database?

If it is the former, you can use three-part (four part if the databases are on different servers) naming convention: http://msdn.microsoft.com/en-us/library/ee336228.aspx

For example:


select * from database1.dbo.Table1 t1
inner join database2.dbo.Table2 t2 on t1.joinCol = t2.joinCol

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-06-18 : 01:44:54

If the databases are on separate instances use Linked Servers

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

paulalvin
Starting Member

8 Posts

Posted - 2011-06-18 : 09:59:58

thanks for the reply this is the case

my first database is all about the applicant
1st table - applicant info
2nd table - contact

second database is about the product
1st table - products
2nd table - inventory

In this case i want to join them when you view them in datagrid in c# with applicant name and the products ordered how can i join them? thanks :)

paulalvin
Starting Member

8 Posts

Posted - 2011-06-18 : 10:11:18

additional question sir if im going to use this "select * from database1.dbo.Table1 t1
inner join database2.dbo.Table2 t2 on t1.joinCol = t2.joinCol
"

how am i going to declare my connstring for two databases?should i declare them and that's it?

paulalvin
Starting Member

8 Posts

Posted - 2011-06-18 : 10:15:23

by the way here is my code

string connString = @"Data Source=GUINTO-PC\SQLEXPRESS;Initial Catalog=purchase;Integrated Security=True";
SqlConnection connapplicantinfo = new SqlConnection(connString);
connapplicantinfo.Open();
string sqlSelectStatement = @"SELECT distinct * from product";
SqlDataAdapter da = new SqlDataAdapter(sqlSelectStatement, connString);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-18 : 10:39:11

Your connection string is fine just the way it is where you have specified "purchase" database. When you use the 3-part naming convention, you are telling SQL server to go and look in the specific database without regard to the database name specified in the connection string.

From your description of the tables, it is not clear to me how you will be able to associate applicant table with product table. There has to be something that indicates which applicant purchased which product.

Take a look at Brett's blog here and see if you can post the DDL for the tables and some sample data: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

paulalvin
Starting Member

8 Posts

Posted - 2011-06-18 : 10:56:04

this is my new code as of now and it seems working :) i was able to display the name of the applicant(1st database) together with the purchased no of product(2nd database) :)

string connString = @"Data Source=GUINTO-PC\SQLEXPRESS;Initial Catalog=purchase;Integrated Security=True";
//string connString1 = @"Data Source=GUINTO-PC\SQLEXPRESS;Initial Catalog=franchise;Integrated Security=True";

SqlConnection connapplicantinfo = new SqlConnection(connString);
//SqlConnection connapplicantinfo1 = new SqlConnection(connString1);
connapplicantinfo.Open();
//connapplicantinfo1.Open();
//string sqlSelectStatement = @"SELECT distinct * from product";
string sqlSelectStatement = @"SELECT distinct * from franchise.dbo.applicantinfo A inner join purchase.dbo.product B on A.appno = B.appno";
SqlDataAdapter da = new SqlDataAdapter(sqlSelectStatement, connString);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources