Creating Table with inner join of all Child tables giving error

indian143

Hi All, I have a parent table and multiple child tables I want to create a resultant Flat table from all result columns, is there any way to create all those columns in a table to put the result into a flat table? If the Object Ids are going to be different I am fine to put them also into it. Here is the script I am trying to use, and understandably it is giving me the error "

Column names in each table must be unique. Column name 'ApplicationId' in table 'TempDelta' is specified more than once.

" Any help is much appreciated thanks in advance friends.

select * into TempDelta from [Application] a
inner join ApplicationComponent b on a.ApplicationId=b.ApplicationId
inner join ApplicationPage c on a.ApplicationId=c.ApplicationId
inner join Audit d on a.ApplicationId=d.ApplicationId
inner join OrgEntityApplication e on a.ApplicationId=e.ApplicationId

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

CHill60

Don't use select * List the columns you actually want. If you have two columns with the same name in two separate tables (i.e. the data contained in the column is different) then you can use something similar to

select A.Col1, B.Col1 AS BCol1, A.Col2, B.Col2 as BCol2 ...

Specifically listing the columns you want protects code from subsequent schema changes - imagine you have a carefully crafted gridview that is populated from the database with a query that does a SELECT * FROM Table1...for a specific business reason Table1 gets an extra column that has absolutely nothing to do with your carefully-crafted-gridview ... you're going to end up with an extra column that you were not expecting.

indian143

Yeah thanks buddy, but actually some tables have 20 column also and I have to all the column as flat to write into Date Warehouse table. May be I have to do like you said but a lot of writing though :)

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

Sascha Lefevre

indian143 wrote:

May be I have to do like you said

No way around that :)

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

indian143

OK sure thank you

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

CHill60

Use the database information to generate the SQL for you. For example (using the Northwind sample database from MS):

USE [Northwind]
DECLARE @listStr VARCHAR(MAX)
;WITH Source AS
(
-- Generate list of all the columns from the table
-- each preceded by our chosen table alias
SELECT
CASE WHEN TABLE_NAME = 'Orders' THEN 'O.'
WHEN TABLE_NAME = 'Customers' THEN 'C.'
WHEN TABLE_NAME = 'Employees' THEN 'E.'
WHEN TABLE_NAME = 'Order Details' THEN 'OD.'
ELSE ''
END + COLUMN_NAME AS ColName
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('Orders', 'Customers','Employees','Order Details')
AND TABLE_SCHEMA='dbo'
)
-- generate a comma-separated list of those columns
SELECT @listStr = COALESCE(@listStr+',' ,'') + ColName
FROM Source
-- build the rest of the SQL statement
DECLARE @SQL varchar(max)
SET @SQL = 'SELECT ' + @listStr + ' FROM Orders O '
SET @SQL = @SQL + 'JOIN Customers C on O.CustomerID=C.CustomerID '
SET @SQL = @SQL + 'JOIN Employees E on O.EmployeeID=E.EmployeeID '
SET @SQL = @SQL + 'JOIN [Order Details] OD on OD.OrderID = O.OrderID '
-- use PRINT rather than select as it is easier to copy
PRINT @SQL

Produces this output (line breaks inserted for clarity):

SELECT C.CustomerID,C.CompanyName,C.ContactName,C.ContactTitle,C.Address,C.City,C.Region,
C.PostalCode,C.Country,C.Phone,C.Fax,
E.EmployeeID,E.LastName,E.FirstName,E.Title,E.TitleOfCourtesy,E.BirthDate,
E.HireDate,E.Address,E.City,E.Region,
E.PostalCode,E.Country,E.HomePhone,E.Extension,E.Photo,E.Notes,E.ReportsTo,E.PhotoPath,
OD.OrderID,OD.ProductID,OD.UnitPrice,OD.Quantity,OD.Discount,
O.OrderID,O.CustomerID,O.EmployeeID,O.OrderDate,O.RequiredDate,O.ShippedDate,
O.ShipVia,O.Freight,O.ShipName,
O.ShipAddress,O.ShipCity,O.ShipRegion,O.ShipPostalCode,O.ShipCountry
FROM Orders O
JOIN Customers C on O.CustomerID=C.CustomerID
JOIN Employees E on O.EmployeeID=E.EmployeeID
JOIN [Order Details] OD on OD.OrderID = O.OrderID

There are other ways of getting the column names (you should really use the object id instead of the name for example), and you can use FOR XML PATH to generate the CSV - but for a quick and dirty one-off this works