December 1, 2014 at 10:20 am
Hi SQL Gurus, I am new to the SQL Programming and also new to this forum.
I have 3 tables: CUSTOMER, SALES_HEADER, SALES_DETAIL and there are no relationships / keys between these tables. I want to INSERT into SALES_HEADER from CUSTOMER & SALES_DETAIL. Here is the query I used.
insert into sales_header (SALES_ID, CUST_ID, SALES_AMOUNT)
select SALES_DETAIL.sales_id, SUM(SALES_DETAIL.prod_price) as sales_amount, CUSTOMER.CUST_ID
from SALES_DETAIL, CUSTOMER
where SALES_HEADER.sales_id = CUSTOMER.cust_id
group by sales_detail.SALES_ID, CUSTOMER.cust_id;
It shows parsed correctly, but giving error: The multi-part identifier "SALES_HEADER.CUST_ID" could not be bound.
Can anyone please help me how to insert from multiple tables when there are no primary / foreign keys & relationships.
Thanks in advance
Raj
December 1, 2014 at 10:42 am
rajd2106 (12/1/2014)
Hi SQL Gurus, I am new to the SQL Programming and also new to this forum.I have 3 tables: CUSTOMER, SALES_HEADER, SALES_DETAIL and there are no relationships / keys between these tables. I want to INSERT into SALES_HEADER from CUSTOMER & SALES_DETAIL. Here is the query I used.
insert into sales_header (SALES_ID, CUST_ID, SALES_AMOUNT)
select SALES_DETAIL.sales_id, SUM(SALES_DETAIL.prod_price) as sales_amount, CUSTOMER.CUST_ID
from SALES_DETAIL, CUSTOMER
where SALES_HEADER.sales_id = CUSTOMER.cust_id
group by sales_detail.SALES_ID, CUSTOMER.cust_id;
It shows parsed correctly, but giving error: The multi-part identifier "SALES_HEADER.CUST_ID" could not be bound.
Can anyone please help me how to insert from multiple tables when there are no primary / foreign keys & relationships.
Thanks in advance
Raj
SALES_HEADER is not in your query. When you want to use a select statement as the source of your insert the select statement needs to work on its own.
select SALES_DETAIL.sales_id, SUM(SALES_DETAIL.prod_price) as sales_amount, CUSTOMER.CUST_ID
from SALES_DETAIL, CUSTOMER
where SALES_HEADER.sales_id = CUSTOMER.cust_id
group by sales_detail.SALES_ID, CUSTOMER.cust_id;
That query will not work.
What are you really trying to do here?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 1, 2014 at 11:23 am
SALES_DETAIL table contains sales_id, prod_type & Prod_price.
In this query I am trying to do aggregation from SALES_DETAIL table :
sum(prod_price) by sales_id & INSERTING into SALES_HEADER table as sales_amount.
Selecting cust_id from CUSTOMER table & INSERTING into SALES_HEADER.
So, SALES_HEADER table will contain sales_id, cust_id, sales_amount from SALES_DETAIL & CUSTOMER tables.
Any suggestions please?
Thanks in advance
Raj
December 1, 2014 at 1:17 pm
rajd2106 (12/1/2014)
SALES_DETAIL table contains sales_id, prod_type & Prod_price.In this query I am trying to do aggregation from SALES_DETAIL table :
sum(prod_price) by sales_id & INSERTING into SALES_HEADER table as sales_amount.
Selecting cust_id from CUSTOMER table & INSERTING into SALES_HEADER.
So, SALES_HEADER table will contain sales_id, cust_id, sales_amount from SALES_DETAIL & CUSTOMER tables.
Any suggestions please?
Thanks in advance
Raj
Right...you are inserting to SALES_HEADER so you can't reference it in your query...it isn't in there. You need to capture the data you want to insert.
It would probably make your life a lot easier if you used ANSI-92 style joins instead of the older ANSI-89 style joins. They are the new way of doing joins (well they have actually been around for more than 20 years now).
Here is a complete shot in the dark. If this doesn't work you need to provide a lot more detailed information.
select sd.sales_id, SUM(sd.prod_price) as sales_amount, c.CUST_ID
from SALES_DETAIL sd
join CUSTOMER c on sd.cust_id = c.cust_id
group by sd.SALES_ID, c.cust_id;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply