[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

SQL insert from one table to another

Hi guys,

Need help with structure of an SQL insert. I will try to make the scenario clear....

I have a Customer table with a column called Country, which is an oid field, I have another column called OldCountry, which in an NVarChar. I have a seperate table called Country which contains an oid field and the name of the country. I need to loop through OldCountry looking for a 'string' match against the country table, if a match is found then return the oid from the country table and insert it into the Country oid column in Customer.

Hope thats relatively clear

Many Thanks,
Dean
0
deanlee17
Asked:
deanlee17
  • 4
  • 4
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you want an INSERT or an UPDATE operation on you Customer table?

If it's an INSERT which are the values for the rest of columns?
If it's an UPDATE which record should be updates?

Anyway the following statement should gives all matches from Customer, Country and OldCountry tables. Can you check if it returns the correct records?

SELECT *
FROM Customer
INNER JOIN Country
    INNER JOIN OldCountry
        ON (Country.Name = OldCountry.Name)
    ON (Customer.Country = Country.ID)

Open in new window

0
 
deanlee17Author Commented:
Sorry my mistake, it is not an insert, it is an update on the Country field in the Customer table
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. I also thought that.
Now I need to know which records should be updated.

And you ran my previous code? Got the expected results?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
deanlee17Author Commented:
Nope getting errors, Invalid Object Name 'OldCountry' just to be clear:

Table:   Customer
Fields:   oldCountry (varchar)
               country (UniqueIdentifier)

Table:   Country
Fields:   Name (varchar)
               oid (UniqueIdentifier)

The field we need to update is the Country field in Customer, so we need to update it with the oid's
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't know the schema of your database so you need to change the table names and column names to yours.

So what are you saying me is that for a country name that exists in Customer and Country tables they can have different ID's so it's for update all Country ID's in Customer table with the OID from Country table, right?

If so then your update statement should be something like:

UPDATE Customer
SET country = (SELECT oid FROM Country WHERE Country.Name = Customer.oldCountry)

Open in new window

0
 
deanlee17Author Commented:
That looks like it could work, I will give it a try now...
0
 
deanlee17Author Commented:
Perfect, thanks.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Glad that I could help you.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now