Link to home
Start Free TrialLog in
Avatar of deanlee17
deanlee17

asked on

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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

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

Avatar of deanlee17
deanlee17

ASKER

Sorry my mistake, it is not an insert, it is an update on the Country field in the Customer table
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?
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
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That looks like it could work, I will give it a try now...
Perfect, thanks.
Glad that I could help you.