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

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

Posted 18-May-15 18:43pm

Member 10833473

Add a Solution

Comments

Advay Pandya 19-May-15 1:08am

I am not sure about the relation between both the tables. Can you use join to fetch columns from both the tables ?

Please let me know if you have any concern or query on this.

Member 10833473 19-May-15 1:59am

I was using datepart(mm, Transaction_Date) from STG_Shipped_Invoiced table, 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). This logic i have to put in above query, but i am not able to do.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Advay Pandya · Accepted Answer · 2015-05-18T20:42:00

Hi,

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

SQL

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

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