CODEWITHSUNDEEP
sql-server
VeryNew2SQL
VeryNew2SQL

Reputation: 3

Insert Into TABLE from 3 separate tables

I am getting my face kicked in....

I have a total of 4 tables
1. Business (BusinessID, CustomerID, BusName, Territory)
2. Customer (CustomerID, Name)
3. Sales (BusinessID, CustomerID, Territory, Jan, Feb, Mar, Apr, May, Jun)
4. Performance (this is the table I want the info in)

I've already created the table to have the following columns, BusinessID, CustomerID, BusName, Name, Territory, Jan,Feb,Mar,Apr,May,Jun

Every time I try to insert its not properly joining and I am getting a bunch of errors "multi-part identifier could not be bound" 

insert into Performance (BusinessID, CustomerID, BusName, Name, Territory, January2018, February2018, March2018, April2018, May2018, June2018)
select Business.BusinessID, Customer.CustomerID, Business.BusName, Customer.Name, Sales.Territory, Sales.January2018, Sales.February2018, Sales.March2018, Sales.April2018, Sales.May2018, Sales.June2018
from Business A
inner join Customer B ON a.CustomerID = b.CustomerID
inner join Sales C ON b.CustomerID = c.CustomerID;

Due to this error I had to do 3 seperate insert into and that caused a bunch of nulls....

face palm is happening and could use some advice.

Image: enter image description here

Thanks, VeryNew2SQL

Upvotes: 0

Views: 89

Answers (2)

AB_87
AB_87

Reputation: 1156

You have used table ALIASES, so you have to use those aliases in you SELECT

A for Business, B for Customer and C for Sales. Read about ALIASES here.

select A.BusinessID, B.CustomerID, A.BusName, B.Name, C.Territory, C.January2018, C.February2018, C.March2018, C.April2018, C.May2018, C.June2018
from Business A
inner join Customer B ON a.CustomerID = b.CustomerID
inner join Sales C ON b.CustomerID = c.CustomerID;

Upvotes: 2

Mr Moose
Mr Moose

Reputation: 6344

When you create a table alias in your FROM and JOIN clauses, you need to refer to the aliases in your SELECT statement and not the actual table names.

Alternatively, leave your SELECT statement as it is, and adjust your table names to remove the alias. You'll then need the join conditions to refer to your actual table names, rather than the alias. So for example;

select Business.BusinessID, Customer.CustomerID, Business.BusName, Customer.Name, Sales.Territory, Sales.January2018, Sales.February2018, Sales.March2018, Sales.April2018, Sales.May2018, Sales.June2018
from Business
inner join Customer ON Business.CustomerID = Customer.CustomerID
inner join Sales  ON Customer.CustomerID = Sales.CustomerID;

Even just try running the SELECT statement above first to make sure you get the query correct before trying it in your insert.

Upvotes: 0

Related Questions