Insert statement from one table to another

I hope I can explain this!!

I am trying to insert data from one table into another:

from table "sheet1" into table "actoremployments". sheet1 is a temp table that had data imported into it from an Excel doc.

Here is what I used

INSERT ACTOREMPLOYMENTS

(EmployerCity, EmployerZip, EmployerFax, EmployerPhone, EmployerEmail, EmployerBlockNbr, StateID, EmployerStreet)

SELECT EmployerCity, EmployerZip, EmployerFax, EmployerPhone, EmployerEmail, EmployerBlockNbr, StateID, EmployerStreet

FROM Sheet1

WHERE (BarNum = ACTORS.BarNum)

ACTOREMPLOYMENTS has a column named ActorID (FK) and key links to a table.column called ACTORS.ActorID. I need to insert the data from sheet1 into actoremployments where the BarNum is linked to ActorID. Sheet1 table also has a column called BarNum.

Does this make sense? How can I make this work?

You need to JOIN against the Actors table.

Try this:

INSERT ACTOREMPLOYMENTS

( EmployerCity,

EmployerZip,

EmployerFax,

EmployerPhone,

EmployerEmail,

EmployerBlockNbr,

StateID,

EmployerStreet

)

SELECT

s.EmployerCity,

s.EmployerZip,

s.EmployerFax,

s.EmployerPhone,

s.EmployerEmail,

s.EmployerBlockNbr,

s.StateID,

s.EmployerStreet

FROM Sheet1 s

JOIN Actors a

ON s.ActorID = a.ActorID

WHERE s.BarNum = a.BarNum

I'm not too sure the WHERE clause is required -BUT it may be, depending upon what you are attempting to accomplish.|||

Arnie already answered. But my guess is that you mean BarNum as a parameter, so just replace "s.BarNum" in arnie's script for the parameter value.

|||

I have BarNum columns in the ACTORS table and in my temp table Sheet1. Im trying to get the data inserted into ACTOREMPLOYMENTS using the BarNum reference. The table ACTOREMPLOYMENTS and ACTORS are linked with the column ActorID. I want to insert the data from Sheet1 to the table ACTOREMPLOYMENTS with records that match the BarNum in ACTORS table and Sheet1 table.

ACTOREMPLOYMENTS already has data in it, but I need to add information from my Sheet1 table to the existing data, ie..EmployerZip, EmployerEmail, StateID etc..which is NULL. So I need to apply the data from Sheet1 into ACTOREMPLOYMENTS. I guess I should use UPDATE with a FROM clause?

UPDATEACTOREMPLOYMENTS

SETEmployerStreet = Sheet1.EmployerStreet, EmployerCity = Sheet1.EmployerCity, EmployerZip = Sheet1.EmployerZip,

EmployerFax = Sheet1.EmployerFax, EmployerPhone = Sheet1.EmployerPhone, EmployerEmail = Sheet1.EmployerEmail,

EmployerBlockNbr = Sheet1.EmployerBlockNbr, StateID = Sheet1.StateID

FROMSheet1 INNER JOIN

ACTORS ON Sheet1.BarNum = ACTORS.BarNum INNER JOIN

ACTOREMPLOYMENTS ON ACTORS.ActorID = ACTOREMPLOYMENTS.ActorID

Or something like

INSERT INTO ACTOREMPLOYMENTS

(EmployerStreet, EmployerCity, EmployerZip, EmployerFax, EmployerPhone, EmployerEmail, EmployerBlockNbr, StateID)

SELECTSheet1.EmployerStreet, Sheet1.EmployerCity, Sheet1.EmployerZip, Sheet1.EmployerFax, Sheet1.EmployerPhone, Sheet1.EmployerEmail,

Sheet1.EmployerBlockNbr, Sheet1.StateID

FROMACTORS INNER JOIN

ACTOREMPLOYMENTS AS ACTOREMPLOYMENTS_1 ON ACTORS.ActorID = ACTOREMPLOYMENTS_1.ActorID INNER JOIN

Sheet1 ON ACTORS.BarNum = Sheet1.BarNum

WHERE(ACTORS.BarNum IS NOT NULL)

|||

It is important to understand what you are needing on the business side.

An update would mean you just want to update the existing employment info. On the other hand, the data you are loading might correspond to new actor employments.

The point is it only depends on what is the business scenario you are implementing. As a matter of fact, the Excel Data might even be new records mixed with existing records, meaning a different solution would be needed.