Hi,
I have two customer databases, each with different information about the
same customer. How can I pull the data from both databases and combine it
into one record in an Excel spreadsheet. The customer name and ID number
are the same in both databases.

Please Help...

Thanks,

Dan

join both the tables using 3 part naming convention.
Ex:
select a.col1,a.col2,a.col3, b.col1,b.col2,b.col3
from db1.owner.table a join db2.owner.table b
on a.id = b.id

To export data to excel file you can use DTS. or if you want to do it through T-SQL you can try
following.
Ex:
bcp "select * from northwind..orders" queryout c:\cust.xls -c -S<server> -Usa -P

above command will create a file with extension XLS but, in fact its a tab delimited flat file
since it has extension XLS by default it will get opened in EXCEL and tab character is considered
as a column delimiter.
I've put TAB as a column delimiter assuming this character is not used in any of the varchar/char
field. because if you are including such a character which exists in the varchar/char datatype then
whole format will get disturbed (comma seperated file is a good example for this). Also point to be
noted that if any of the char/varchar field contains TAB character even this file's format will get
disturbed. you can also run above command line using xp_cmdshell and thus can be used in T-SQL
script. The same command can be interpreted as

exec master..xp_cmdshell 'bcp "select * from northwind..orders" queryout
c:\cust.xls -c -S<server> -Usa -P'

--
- Vishal

Thanks for the response, however, I forget to mention that one db is Access
and the other is SQL. Will your solution still work in this case?

Thanks!!


"Vishal Parkar" <_vgparkar (AT) hotmail (DOT) com> wrote

nope, Take a look at "linked servers" topic in Books online. you will have to create a linked
server that will be connecting to ms access. If you are using linked server then you will have to
use 4 part naming convention. ie <linked_server>.<db>.<owner>.<table>
Also look at OPENROWSET function in books online.

--
- Vishal

In addition to Vishal's answer you could also DTS over the Access table to
SQL Server then use TSQL to manipulate the two+ tabled from there before
firing it over to Excel.



--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Vishal Parkar" <_vgparkar (AT) hotmail (DOT) com> wrote

I'll give those a try....thanks alot!


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote