0

If I have a query 

select * into #tmp1 from dbo.t1  inner join dbo.t2  on t1.Sender_Id=t2.Id

I get an error

Column names in each table must be unique. Column name 'Id' in table '#tmp1' is specified more than once.

How can I do the same thing without resorting to 

select t1.Id as t1id, t1.col2. ... t1.col30, t2.Id as t2id, ... t2.col40 as t2col40 from ...

notation.

I just need to quickly and manualy examine several tables, so I'd like to have a quick way of examining joins.

alex440
  • 1,647
  • 3
  • 20
  • 35
  • 1
    Unfortunately if there are duplicate columns you need to resolve that when using `SELECT * INTO` – dotnetom Oct 26 '14 at 10:17
  • 1
    possible duplicate of [How to SELECT \* but without "Column names must be unique in each view"](http://stackoverflow.com/questions/6423684/how-to-select-but-without-column-names-must-be-unique-in-each-view) – bummi Oct 26 '14 at 10:17
  • As already mentioned. You can't. You will need to specify the column names and ensure they are unique. However, rather than using 'SELECT * INTO'. You could explicitly create the table beforehand (with unique column names) and do an 'INSERT INTO' instead. This allows you to use SELECT * still. – JBond Oct 26 '14 at 15:50

3 Answers3

1

If you have to persist the result via select * into #tmp or want to create a view, every field name has to be unique and you will have to uses aliases for fields with identical names.
A simple query does not need unique names.

bummi
  • 27,123
  • 14
  • 62
  • 101
-1

Yes, columns name must unique in select statement, in your case, you have for example two (02) id, one from tbl1, and the other one from tbl2, on esolution is list them as:

Select t1.id, t2.id From tbl1 as t1 Inner Join tbl2 as t2 on t1.id = t2.id

Hope this help.

Mahmoud DEKKAR
  • 1
  • 1
-1

Or, if you have columns with same name in both tables, use this:

Select t1.*, t2.* From tbl1 as t1 Inner join tbl2 as t2 On t1.id = t2.id

Regards

Mahmoud DEKKAR
  • 1
  • 1