| Author |
Topic |
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
 Posted - 2012-01-18 : 22:04:01
|
| I have a table which contains patients and a Doctor key.which is char 4. This table is Patients_Mast.In Doctor_Mast table I have the Doctor Key also 4 char and the Doctor name varchar 30.I would like to create a new temp file which will have all of the columns of Patients_Mast but as well the Doctor Name.I believe I need an inner join on the Doctor key but how do I then write all the columns and the Doctor name? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-01-19 : 04:59:01
|
select pm.*,dm.namefrom Patients_Mast pmjoin Doctor_Mast dm on dm.Key = pm.Key
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
Posted - 2012-01-19 : 08:47:50
|
thank you Fred, how do I create the temp file that will hold the new column that i joined to get the DOctor Name.quote:
Originally posted by webfred
select pm.*,dm.namefrom Patients_Mast pmjoin Doctor_Mast dm on dm.Key = pm.Key
No, you're never too old to Yak'n'Roll if you're too young to die.
|
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-01-19 : 08:53:44
|
This will create and fill #MyTmpTable but the statement will give an error if #MyTmpTable already exists (so you have to drop).select pm.*,dm.nameINTO #MyTmpTablefrom Patients_Mast pmjoin Doctor_Mast dm on dm.Key = pm.Key
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-01-19 : 09:16:45
|
quote:
Originally posted by AdamWest
I have a table which contains patients and a Doctor key.which is char 4. This table is Patients_Mast.In Doctor_Mast table I have the Doctor Key also 4 char and the Doctor name varchar 30.I would like to create a new temp file which will have all of the columns of Patients_Mast but as well the Doctor Name.I believe I need an inner join on the Doctor key but how do I then write all the columns and the Doctor name?
Why would you want to do that?Couldn't you use a VIEW instead that has the information in it.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
Posted - 2012-01-19 : 09:29:40
|
I want to get this to an excel spreadsheet.quote:
Originally posted by Transact Charlie
quote:
Originally posted by AdamWest
I have a table which contains patients and a Doctor key.which is char 4. This table is Patients_Mast.In Doctor_Mast table I have the Doctor Key also 4 char and the Doctor name varchar 30.I would like to create a new temp file which will have all of the columns of Patients_Mast but as well the Doctor Name.I believe I need an inner join on the Doctor key but how do I then write all the columns and the Doctor name?
Why would you want to do that?Couldn't you use a VIEW instead that has the information in it.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
|
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-01-19 : 11:00:56
|
quote:
Originally posted by AdamWest
I want to get this to an excel spreadsheet.quote:
Originally posted by Transact Charlie
quote:
Originally posted by AdamWest
I have a table which contains patients and a Doctor key.which is char 4. This table is Patients_Mast.In Doctor_Mast table I have the Doctor Key also 4 char and the Doctor name varchar 30.I would like to create a new temp file which will have all of the columns of Patients_Mast but as well the Doctor Name.I believe I need an inner join on the Doctor key but how do I then write all the columns and the Doctor name?
Why would you want to do that?Couldn't you use a VIEW instead that has the information in it.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
You could reference a view just as easily as a table and it would always be up to date.Brett suggested BCP.How do you plan to get the data into excel?if this is a one off thing you could simply run the query in management studido (to a grid view)Then highlight all data and right click and select "copy with headers"you can the paste into excel.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
Posted - 2012-01-19 : 14:37:59
|
| It's a one off. We have customers that make these sort of requests for data to excel. When it's going to be a regular need, I write a C# program, or modify one which does this more or less. |
|
|
|
|
Simple Join add column to a new table