如何从另一个表中获取列并插入到其他表中? [英] How to get column from another table and insert into other table?

查看:64
本文介绍了如何从另一个表中获取列并插入到其他表中?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

大家好,

我有两个表'Cal_date'和'RPT_Invoice_Shipped'。

表cal_data有列month_no,start_date和end_date。并且表RPT_Invoice_Shipped具有列Day_No,Date,Div_code,Total_Invoiced,Shipped_Value,Line_Shipped,Unit_Shipped,Transaction_Date。



我使用下面的insert statment在RPT_Invoice_Shipped表中插入数据。





插入[Global_Report_Staging]。[dbo]。[RPT_Invoice_Shipped]

(Day_No,Date,Div_code,Total_Invoiced,Transaction_Date)

select,CONVERT(DATE,Getdate) ())作为日期,LTRIM(RTRIM(div_Code)),

sum(tot_Net_Amt)为Total_Invoiced,(dateadd(day,-1,convert(date,getdate()))) >来自[Global_Report_Staging]的
。[dbo]。[STG_Shipped_Invoiced]

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

group通过div_code



在RPT_Invoice_Shipped表中插入Day_No列时,我必须使用公式(Transaction_Date-start_date + 1),其中来自STG_Shipped_Invoiced的Transaction_Date和来自Cal_date表的start_date。我使用的是datepart(mm,Transaction_Date)所以它给了month_no,而这个月_no我们可以加入Cal_date表的month_no并从Cal_date表中获取start_date,这样我们就可以使用start_date作为公式(Transaction_Date-start_date + 1)。 />


但是我很难在上面的查询中安排这个。能否指导我如何实现这个目标?



提前致谢

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

推荐答案





我通过在两个表之间添加连接来对您的查询进行少量更改:



Hi,

I have done little changes in your query by adding join between both the tables:

insert into [Global_Report_Staging].[dbo].[RPT_Invoice_Shipped]
(Day_No, Date, Div_code, Total_Invoiced, Transaction_Date)
select cal_data.month_no,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] SSI
Inner join cal_data ON datepart(mm, SSI.Transaction_Date) = cal_data.month_no
WHERE CONVERT(DATE,Created_date )=CONVERT(DATE,Getdate()) 
group by div_code





现在您可以使用两个表中的任何一列。它会对你有用吗?



如果您有任何疑虑或疑问或我遗失了什么,请告诉我。



谢谢

Advay Pandya



Now you can use any of the column from both of the table. Will it work for you ?

Please let me know if you have any concern or query or I am missing something.

Thanks
Advay Pandya


这篇关于如何从另一个表中获取列并插入到其他表中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆