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
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
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?
Now I need to know which records should be updated.
And you ran my previous code? Got the expected results?
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That looks like it could work, I will give it a try now...
ASKER
Perfect, thanks.
Glad that I could help you.
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?
Open in new window