The column was specified multiple times I am getting error when trying to run the following query :

INSERT stock_daily_data (item_code,date,quantity_used,stock_left) select 
distinct item_code,'2017-11-07',0,stock_left from ( select *, row_number() 
over (partition by stock_daily_data.item_code order by stock_daily_data.date 
desc) as rn from stock_item_detail join stock_daily_data on 
stock_item_detail.item_code = stock_daily_data.item_code ) tt where rn = 1;


Error is :

The column 'item_code' was specified multiple times for 'tt'.


I am using mssql 2012.
I am quite naive to SQL .
Thanks in advance for helpful advice.
It's very likely that when using * to select all columns in your subquery, you have 2 columns with the same name ITEM_CODE, one from stock_item_detail table and one from stock_daily_data table.

I suggest you explicitly list all the columns you want for this subquery and you should be fine.

I think something like the code below should do the trick:

INSERT stock_daily_data (item_code,date,quantity_used,stock_left) 
select distinct 
    item_code,
    '2017-11-07',
    0,
    stock_left 
from (
    select 
        stock_item_detail.item_code   -- I assume that these are the columns you want
        , stock_daily_data.stock_left
        , row_number() over (partition by stock_daily_data.item_code order by stock_daily_data.date desc) as rn 
    from stock_item_detail 
        join stock_daily_data
            on stock_item_detail.item_code = stock_daily_data.item_code 
      ) tt 
where rn = 1;