Help exporting from multiple tables

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help exporting from multiple tables

Author

Topic

stylishjm
Starting Member

17 Posts

Posted - 2012-02-29 : 11:20:08

We have software for table management and reservations used in restaurants.
All of the information on the customer is split up into different tables ie:

company.customers - basic information including last visit etc
company.customersphone - home and work telephone numbers
company.customersaddress - home and work addres
company.customersnotes - misc notes from VIP status to preferances

each of these tables then has various columns ie:

TABLE company.customers
COLUMN firstname
COLUMN lastname
COLUMN middlename
COLUMN customerguid
COLUMN guid

TABLE company.customersphone
COLUMN workphone
COLUMN homephone
COLUMN mobile
COLUMN customerguid
COLUMN guid

All of the tables have a column called CustomerGuid which link to the specific customer.

How would I export all information from all of these tables and match up the customers to their relevant details?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-29 : 11:43:42

What do you mean when you say "export"?
Why do have a table customersphone that way?

No, you're never too old to Yak'n'Roll if you're too young to die.

stylishjm
Starting Member

17 Posts

Posted - 2012-02-29 : 11:47:04

Sorry just realised I may have put this in the wrong section!
But anyway, I'm using Microsoft SQL Server Management 2005, and using the syntax I can export as a CSV.
What do you mean by "Why do have a table customersphone that way?"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-29 : 12:01:04

If there is only a one to one relationship then you don't need a second table. Just place the columns in the customers table.

For a one to many relationship the customersphone table would have column phonenumber and phonetype and then for each phonetype a new row in the table. That's why I asked.

No, you're never too old to Yak'n'Roll if you're too young to die.

stylishjm
Starting Member

17 Posts

Posted - 2012-02-29 : 12:05:54

Oh yes, sorry it is like that. I've just posted a quick example with made up column names. Exact Table/column names:

QsrUser.Customers
Title
FirstName
LastName
EmailAddress
CustomerGuid
Guid

QsrUser.CustomerAddresses
Address1
Address2
City
State
Postal
Country
CustomerGuid
Guid

QsrUser.CustomerNotes
Note
CustomerGuid
Guid

QsrUser.CustomerCare
Birthday
SpouseBirthday
Anniversary
CustomerGuid
Guid

QsrUser.CustomerPhones
PhoneType
PhoneNumber
Extension
CustomerGuid
Guid

I've only displayed column names that I need, there are more

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-29 : 12:37:29

So what you need is a SELECT statement and then for example an export to csv file via bcp.
Do you really need a join into only one file?
Is it ok if a customer has for example a homephone and a mobilphone that the SELECT will output two rows?

There are questions...

Can you give some example data and the wanted result so we don't have to guess about so many things?

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 13:05:22

something like



CREATE VIEW myView99
AS
    SELECT * 
	  FROM company.customers		c
 LEFT JOIN company.customersphone	p
		ON c.[GUID] = p.[GUID]
 LEFT JOIN company.customersaddress	a
		ON c.[GUID] = a.[GUID]
 LEFT JOIN company.customersnotes	n
		ON c.[GUID] = n.[GUID]
		GO
		
DECLARE @cmd varchar(8000)
SET @cmd = 'bcp dbnbame.dbo.myView99 out d:\temp.dat -S<servername> -T -c'
EXEC master..xp_cmdshell @cmd
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

stylishjm
Starting Member

17 Posts

Posted - 2012-03-01 : 04:58:24

quote:
Originally posted by X002548

something like

CREATE VIEW myView99
AS
SELECT *
FROM company.customers c
LEFT JOIN company.customersphone p
ON c.[GUID] = p.[GUID]
LEFT JOIN company.customersaddress a
ON c.[GUID] = a.[GUID]
LEFT JOIN company.customersnotes n
ON c.[GUID] = n.[GUID]
GO

DECLARE @cmd varchar(8000)
SET @cmd = 'bcp dbnbame.dbo.myView99 out d:\temp.dat -S<servername> -T -c'
EXEC master..xp_cmdshell @cmd
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Hello,

Yes that looks like something that may work.
Would you be able to explain how it works, and what bits mean such as the "c" at the end of "FROM company.customers c"

stylishjm
Starting Member

17 Posts

Posted - 2012-03-01 : 05:13:43

In fact, it almost works, although every value is showing NULL in the joined columns. Everything from the Company.customers and GUID is displaying though.
Is this because I didnt specifically include those, in the SELECT statement even if I used "*".

Would I have to do something like:

SELECT QsrUser.Customers.Firstname, QsrUser.Customers.Lastname, QsrUser.Customers.Email, QsrUser.CustomerPhones.Phonetype, QsrUser.CustomerPhones.Phonenumber ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-01 : 07:39:16

Just a shot:
replace [Guid] with [CustomerGuid] in the ON clauses

No, you're never too old to Yak'n'Roll if you're too young to die.

stylishjm
Starting Member

17 Posts

Posted - 2012-03-01 : 09:03:36

hello, yes It was my mistake.
The Guid was completely random for each table
The Customer Guid was also not present in the QsrUser.Customers table so there was no way of linking that table to the others.
I checked the ID column and realised they all matched up through that!

All resolved now, thank you both for your help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-01 : 09:13:29

cool