1

I have imported some data into a new sql table and have managed to update the imported data into the correct table but its unable to update for records without a customer number, i have tried updating the customer number but it says it cant update a primary key record.

This is the code i used to update the fields so far...

update SalBudgetCust

Set 

SalBudgetCust.Sales1 = SalBudgetCust_temp.Sales1, 
SalBudgetCust.Sales2 = SalBudgetCust_temp.Sales2, 
SalBudgetCust.Sales3 = SalBudgetCust_temp.Sales3,
SalBudgetCust.Sales4 = SalBudgetCust_temp.Sales4,
SalBudgetCust.Sales5 = SalBudgetCust_temp.Sales5,
SalBudgetCust.Sales6 = SalBudgetCust_temp.Sales6,
SalBudgetCust.Sales7 = SalBudgetCust_temp.Sales7,
SalBudgetCust.Sales8 = SalBudgetCust_temp.Sales8,
SalBudgetCust.Sales9 = SalBudgetCust_temp.Sales9,
SalBudgetCust.Sales10 = SalBudgetCust_temp.Sales10,
SalBudgetCust.Sales11 = SalBudgetCust_temp.Sales11,
SalBudgetCust.Sales12 = SalBudgetCust_temp.Sales12

From 

SalBudgetCust_temp where SalBudgetCust.Customer = SalBudgetCust_temp.Customer

Would anyone be able to offer some light on how if the customer record doesnt exist already it populates from the other table and then includes the corresponding sales figures?

lewiscooksey
  • 31
  • 3
  • 1
    What SQL are you using? Microsoft, Oracle, mySQL? – Sparky Dec 16 '11 at 09:06
  • Sounds like you are looking for a MERGE statement: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9016.htm –  Dec 16 '11 at 09:13

1 Answers1

0

Try SELECT INTO statement as below for inserting records where customer doesnt exist:

SELECT * INTO SalBudgetCust FROM SalBudgetCust_temp WHERE NOT EXISTS (SELECT Customer FROM SalBudgetCust WHERE Customer = SalBudgetCust_temp.Customer)

This SO Answer might also help you to the solution:

How to avoid duplicates in INSERT INTO SELECT query in SQL Server?

Community
  • 1
  • 1
S2S2
  • 8,322
  • 5
  • 37
  • 65
  • seems like its trying to recreate the table, the table already exists "There is already an object named 'SalBudgetCust' in the database." – lewiscooksey Dec 16 '11 at 11:17