How to get column from another table and insert into other table?

• manoj.ramaiah

  SSC Eights!

  Points: 864

  More actions

  May 18, 2015 at 10:43 pm

  #318237

  Hi All,

  I have two table 'Cal_date' and 'RPT_Invoice_Shipped'.

  Table cal_data has column month_no, start_date and end_date. And table RPT_Invoice_Shipped has columns Day_No, Date, Div_code, Total_Invoiced, Shipped_Value, Line_Shipped, Unit_Shipped, Transaction_Date.

  I am using below insert statment to insert data in RPT_Invoice_Shipped table.

  insert into [Global_Report_Staging].[dbo].[RPT_Invoice_Shipped]

  (Day_No, Date, Div_code, Total_Invoiced, Transaction_Date)

  select , CONVERT(DATE,Getdate()) as Date, LTRIM(RTRIM(div_Code)),

  sum(tot_Net_Amt) as Total_Invoiced, (dateadd(day, -1, convert(date, getdate())))

  from [Global_Report_Staging].[dbo].[STG_Shipped_Invoiced]

  WHERE CONVERT(DATE,Created_date )=CONVERT(DATE,Getdate())

  group by div_code

  while inserting in column Day_No in RPT_Invoice_Shipped table, I have to use formula (Transaction_Date-start_date+1) where Transaction_Date from STG_Shipped_Invoiced and start_date from Cal_date table. I was using datepart (mm, Transaction_Date) so it gives month_no, and this month_no we can join with month_no of Cal_date table and fetch start_date from Cal_date table, so that we can use start_date for formula (Transaction_Date-start_date+1).

  But I am getting difficulty to arrange this in above query. Can you please guide me how to achive this?

  Thanks in advance

• SQL.AP

  Hall of Fame

  Points: 3244

  More actions

  May 19, 2015 at 12:22 am

  #1798901

  I am surprised with your query.

  If you are inserting something in your table, then how come you can use same column name in the select query?

  Still..

  You are inserting "(dateadd(day, -1, convert(date, getdate())))" in your Transaction_Date column, so use "(dateadd(day, -1, convert(date, getdate())))" in the calculation for first column in your select query instead of the destination column name.

  It would be better if you can elaborate your query. Because I cannot understand why you want to use a destination column name.

  ____________________________________________________________

  AP