-1

I begin with SQL server. I joined two tables. Both of them contain a column called "id".

After the inner join, I have now two columns with the same name ("id").

How can I fix this issue (- without typing all columns I want to keep. I have 200 columns -).

declare @contract_number nvarchar(255) = '2019.37080'
declare @reportingperiod nvarchar(255) = '2019-Q1'
 

select *
from ifrs17.output_bba
inner join ifrs17.unit_of_account 
    on unit_of_account.contract_number = output_bba.unit_of_account_number
    and unit_of_account.reporting_period = output_bba.reporting_period
    and unit_of_account.currency_source = output_bba.currency_source
where 
    output_bba.unit_of_account_number = @contract_number
    and output_bba.meta_id in ( select max(meta_id) from ifrs17.output_bba where contract_number = @contract_number and legal_entity_code = 'SR' group by reporting_period )
    and unit_of_account.meta_id in ( select max(meta_id) from ifrs17.unit_of_account where contract_number = @contract_number and legal_entity_code = 'SR' group by reporting_period )
    and output_bba.time_index > 0
    and output_bba.reporting_period = @reportingperiod
Nassim
  • 117
  • 9
  • 4
    You can't - so start typing. There are tools and techniques to assist but it still comes down to your usage of "*" as the column list. – SMor Feb 09 '21 at 16:53
  • 1
    The method is simple, list the columns you need, and don't use `*`. List the columns you need, and you only get the columns you need. Also, I recommend giving your objects aliases in your queries; it'll make your SQL far more succinct. – Thom A Feb 09 '21 at 16:54
  • The only time(s) I'd accept use of `SELECT *` is a) whilst still prototyping the query where you're trying to get the JOINs/WHERE right, and b) inside an `EXISTS()` test. Otherwise, with you mythical "everything but one column" feature, what happens when someone adds an extra 2GB column of documentation in every row that *you don't need*. Select the columns you do actually need, I doubt that all 200 are relevant. – Damien_The_Unbeliever Feb 09 '21 at 16:57
  • @Damien_The_Unbeliever One other place is in a derived table with a single real table reference, where all you are doing is adding columns. Any column not referenced on the outside is elided by the parser – Charlieface Feb 09 '21 at 17:03
  • Good question +1. This is one of the areas of the SQL Standard I would improve. – The Impaler Feb 09 '21 at 17:04
  • Maybe the best idea for me would be to change the col names... id to id_1 and id_2. Is it possible ? if yes, do you see how to edit my query? – Nassim Feb 09 '21 at 17:04
  • 1
    I disagree, @TheImpaler, and Damien gives one (really good) reason. There should *not* be syntax like `* EXCEPT SomeColumn`. I can certainly see lazy people using such syntax for things like a `VIEW`'s definition, and that would be disasterous. – Thom A Feb 09 '21 at 17:04
  • 1
    Does this answer your question? [SQL exclude a column using SELECT \* \[except columnA\] FROM tableA?](https://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea) – SMor Feb 09 '21 at 17:11
  • I will copy a row and paste it into an excel workbook. Then copy and transpose my columns. Then you can either concat in excel or even better in SSMS `ALT+Click Drag` and add a comma to the needed ones after you paste them – tlk27 Feb 09 '21 at 17:37

1 Answers1

2

You do not need to type the column names. Drag and drop works.

Open up SSMS Object Explorer. Click Databases. Click your database. Click Tables. Click the table. You will see "Columns". Drag "Columns" (the label, not the individual column names) into the query window. All of the column names appear as a comma delimited list. Change "id," to "id as FirstID," Repeat for the second table. Also helpful when you use "SET IDENTITY_INSERT" which requires that the column names be specified.

jim
  • 401
  • 4
  • 10