I have 3 tables each consisting of approximately 150 fields each. I want to create a single new table and have all the unique fields from each table in the new one. The one method i know is to name each of the fields in a SELECT clause along with a join on all the three tables. But then this is a cumbersome solution and error prone. If anyone has a better solution i would appreciate it. Thanks.

-- Anonymous, February 28, 2001

Answers

Sashindar,

By 'unique fields' I presume that you want to avoid listing the join keys more than once.

I would do this by using a cursor to create a statement that could then be executed. For the example I show below, I will show how you would do a join of the pubs database titleauthor, titles, and authors tables, but avoid listing the title_id and au_id columns more than once. (Forgive the spacious formatting. it looks better in the forum.)

Hope this helps,

Eric

-- You may have to experiment with the size of your strings.

-- Keep in mind that the exec statment allows you to pass in

-- several concatenated strings that may total more than 8000

-- characters.

declare @SQLSTring varchar (8000)

declare @column_name varchar (200)

set @SQLString = 'select '

declare column_name_cursor cursor

for

select o.name + '.' + c.name

from syscolumns c,

sysobjects o

where o.name in ('titles', 'authors', 'titleauthor')

and o.id = c.id

and not (o.name = 'titleauthor' and

c.name in ('au_id', 'title_id'))

order by o.name, c.name

open column_name_cursor

fetch next from column_name_cursor into @column_name

set @SQLString = @SQLString + @column_name

while (@@fetch_status <> -1)

begin

set @SQLString = @SQLString + ', '

fetch next from column_name_cursor into @column_name

set @SQLString = @SQLString + @column_name

end

close column_name_cursor

deallocate column_name_cursor

set @SQLString = @SQLString + ' from authors,'

set @SQLString = @SQLString + ' titles,'

set @SQLString = @SQLString + ' titleauthor'

set @SQLString = @SQLString +

' where authors.au_id = titleauthor.au_id'

set @SQLString = @SQLString +

' and titles.title_id = titleauthor.title_id'

exec (@SQLString)

-- Anonymous, March 01, 2001