Adding a column alias to be the field name in a new table

Question

Post reply

Adding a column alias to be the field name in a new table

79J

SSC-Addicted

Points: 469
More actions
November 27, 2011 at 4:50 am

#251235

Hi,
I am creating a staging table in sql 2008 R2.This will take all my data from 2 or more tables and put them into one table.
However when i do this i get an error message saying that the field names need to be unique.
How can i add the table alias to be part of the field name without doing this for each field manually ie sd.SalesOrder as SD_SalesOrderID
The query i am using is as below
SELECT SD.SalesOrderID, SD.SalesOrderDetailID, SH.SalesOrderID
into tbl_test4
FROM Sales.SalesOrderDetail AS SD LEFT OUTER JOIN
Sales.SalesOrderHeader AS SH ON SD.SalesOrderID = SH.SalesOrderID
The columns i would expect to see created in my new table is
SD_SalesOrderID,SH_SalesOrderID
Thanks
Jag

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

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

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 1

It's the same as any other alias... either of the following methods work just fine...

SELECT SD_SalesOrderID = SD.SalesOrderID,

SH_SalesOrderID = SH.SalesOrderID

INTO dbo.tbl_test4

FROM Sales.SalesOrderDetail AS SD

LEFT OUTER JOIN Sales.SalesOrderHeader AS SH

ON SD.SalesOrderID = SH.SalesOrderID

;

--------------------------------------------------------------

SELECT SD.SalesOrderID AS SD_SalesOrderID,

SH.SalesOrderID AS SH_SalesOrderID

INTO dbo.tbl_test4

FROM Sales.SalesOrderDetail AS SD

LEFT OUTER JOIN Sales.SalesOrderHeader AS SH

ON SD.SalesOrderID = SH.SalesOrderID

;

That's the first part of your request. The second part about "without doing this for each field manually" would require you to form the query using dynamic SQL that is formed by reading either from the system views or the Information_Schema views. Plrease post back if you need help on that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

79J SSC-Addicted Points: 469 More actions · Answer 2

Hi Jeff,

Thank you get getting back to me.This works like a dream.I would like to know how to do this automatically using dynamic SQL as i could have a lot of fields in the very first table.

Thanks for your help its very much appreciated.

J

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 3

I have to ask... how many columns do you have in common between the header and detail tables? There should only be one an only one column in common between the two and that should be the SalesOrderID. You can simply drag "Columns" from the "explorer" window in SSMS into a query and it should create a comma delimited list of all the columns. Since the columns in the two tables (except for the one) should be different, there shouldn't be a need to alias all of the columns.

I guess I need to understand what you're actually trying to do. Can you explain in a bit more detail, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

79J SSC-Addicted Points: 469 More actions · Answer 4

Hi Jeff,

The goal is to create a datawarehouse.As i am just started out i am going to data dump of my data into one table.This means i will have the primary key and foreign keys of each table in the table multiple times.I am thinking that if i add the table name to each column ie Sales_OrderID,Products_orderid it would create unique identifiable fields.

This table will become the base table and as the datawarehouse progresses the tables will be better structured.For the moment i am just wanting to create the base table quickly in order to get the data in.

Thanks for helping

J

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 5

1) Create a View with editor.

2) Copy the View's SQL query.

3) Drop the View.

Use Dynamic SQL by splitting the copied view query into proper sections.

SET strQuery=strSel + strFrm + strWhr + StrGrp + StrOrd

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 6

Jeff Moden (11/27/2011)
...There should only be one an only one column in common between the two and that should be the SalesOrderID...

In my experience, SQL Server doesn't enforce unique column names unless I'm only referring to one table in my query.

79J SSC-Addicted Points: 469 More actions · Answer 7

Hi Jeff,

I used this option for re-naming my fields.

Thanks to everyone for your help.It was all very much appreciated.

J

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 8

patrickmcginnis59 (11/28/2011)
Jeff Moden (11/27/2011)
...There should only be one an only one column in common between the two and that should be the SalesOrderID...
In my experience, SQL Server doesn't enforce unique column names unless I'm only referring to one table in my query.

Not what I was talking about, Patrick. I realize that two tables can have the same column name. I was talking about having a reference between two tables and, in this case, it should be SalesOrderID in both tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)