sql update queries from within VB
-
I have two databases I open. The first is a list of customer numbers I have to open in an oledb connection. The second is the master customer table with customer number and address info--this is opened with a sqldb connection. The fields in both are the same in respects to the data I'll be accessing. I need to somehow add the address info to the first table from the second. I'm wondering if there is anyway to do something like: update oletable set oletable.street=sqltable.street set oletable.city=sqltable.city set oletable.state=sqltable.state set oletable.zip=sqltable.zip where oletable.cust_no=sqltable.cust_no doubt my syntax is correct but you see what I wish to do. I'm just not sure if this is even possible since the two tables resite in different databases and the first can only be opened via oledb connection. Can anyone help?
-
I have two databases I open. The first is a list of customer numbers I have to open in an oledb connection. The second is the master customer table with customer number and address info--this is opened with a sqldb connection. The fields in both are the same in respects to the data I'll be accessing. I need to somehow add the address info to the first table from the second. I'm wondering if there is anyway to do something like: update oletable set oletable.street=sqltable.street set oletable.city=sqltable.city set oletable.state=sqltable.state set oletable.zip=sqltable.zip where oletable.cust_no=sqltable.cust_no doubt my syntax is correct but you see what I wish to do. I'm just not sure if this is even possible since the two tables resite in different databases and the first can only be opened via oledb connection. Can anyone help?
What is your first data source, the one you are accessing via an OLEDB connection? You might be able to set it up as a linked server in your SQL Server, allowing you to run queries that directly reference both databases. Have a look at this article[^].
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
-
I have two databases I open. The first is a list of customer numbers I have to open in an oledb connection. The second is the master customer table with customer number and address info--this is opened with a sqldb connection. The fields in both are the same in respects to the data I'll be accessing. I need to somehow add the address info to the first table from the second. I'm wondering if there is anyway to do something like: update oletable set oletable.street=sqltable.street set oletable.city=sqltable.city set oletable.state=sqltable.state set oletable.zip=sqltable.zip where oletable.cust_no=sqltable.cust_no doubt my syntax is correct but you see what I wish to do. I'm just not sure if this is even possible since the two tables resite in different databases and the first can only be opened via oledb connection. Can anyone help?
This is only possible when you are having both table in sql server.
Regards Pankaj Joshi
-
This is only possible when you are having both table in sql server.
Regards Pankaj Joshi
-
What is your first data source, the one you are accessing via an OLEDB connection? You might be able to set it up as a linked server in your SQL Server, allowing you to run queries that directly reference both databases. Have a look at this article[^].
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
I toyed with that for a couple hours this AM. It didn't like the name of the sheet in excel but after fixing that I have the table excel sheet added just like the article. Now if only I could get my command to work. update amyexcel...allanis$ set street=addrlist.street set city=addrlist.city set state=addrlist.state set zip=addrlist.zip from masterdb.addrlist where allanis$.phone=addrlist.phone I wish to update the street, city, state and zip fields in the excel spreadsheet/table allanis$ with the same fields from the addrlist table which is in the masterdb database. Matching them up by the phone field. I'm not a sql expert either and if you could help with this I'd appreciate it. select top 5 * from amyexcel...allanis$ works from where I'm sitting in SQL query analyzer. I'm not sure how to specify that addrlist is in the master db and I think this is why I'm not getting things to work. How do I get it to understand that allanis$ and addrlist are in different databases? Thanks.
-
I toyed with that for a couple hours this AM. It didn't like the name of the sheet in excel but after fixing that I have the table excel sheet added just like the article. Now if only I could get my command to work. update amyexcel...allanis$ set street=addrlist.street set city=addrlist.city set state=addrlist.state set zip=addrlist.zip from masterdb.addrlist where allanis$.phone=addrlist.phone I wish to update the street, city, state and zip fields in the excel spreadsheet/table allanis$ with the same fields from the addrlist table which is in the masterdb database. Matching them up by the phone field. I'm not a sql expert either and if you could help with this I'd appreciate it. select top 5 * from amyexcel...allanis$ works from where I'm sitting in SQL query analyzer. I'm not sure how to specify that addrlist is in the master db and I think this is why I'm not getting things to work. How do I get it to understand that allanis$ and addrlist are in different databases? Thanks.
Your update query syntax isn't correct. Your query should look something like this
update amyexcel...allanis$
set street=addresslist.street,
city=addresslist.city,
state=addresslist.state,
zip=addresslist.zip
from masterdb.dbo.addrlist addresslist
where amyexcel...allanis$.phone=addresslist.phoneI have assumed that dbo is the owner of the addrlist table. Try this and let me know how you get on.
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
-
Your update query syntax isn't correct. Your query should look something like this
update amyexcel...allanis$
set street=addresslist.street,
city=addresslist.city,
state=addresslist.state,
zip=addresslist.zip
from masterdb.dbo.addrlist addresslist
where amyexcel...allanis$.phone=addresslist.phoneI have assumed that dbo is the owner of the addrlist table. Try this and let me know how you get on.
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
First I got: Server: Msg 117, Level 15, State 2, Line 8 The number name 'amyexcel...allanis$' contains more than the maximum number of prefixes. The maximum is 3. So I changed it to: update amyexcel...allanis$ set street=addresslist.street, city=addresslist.city, state=addresslist.state, zip=addresslist.zip from masterdb.dbo.addresslist addresslist where allanis$.phone=addresslist.phone but now it gives me: Server: Msg 107, Level 16, State 2, Line 1 The column prefix 'allanis$' does not match with a table name or alias name used in the query.
-
First I got: Server: Msg 117, Level 15, State 2, Line 8 The number name 'amyexcel...allanis$' contains more than the maximum number of prefixes. The maximum is 3. So I changed it to: update amyexcel...allanis$ set street=addresslist.street, city=addresslist.city, state=addresslist.state, zip=addresslist.zip from masterdb.dbo.addresslist addresslist where allanis$.phone=addresslist.phone but now it gives me: Server: Msg 107, Level 16, State 2, Line 1 The column prefix 'allanis$' does not match with a table name or alias name used in the query.
Change your query to
update allanis
set street=addresslist.street,
city=addresslist.city,
state=addresslist.state,
zip=addresslist.zip
from amyexcel...allanis$ allanis
inner join masterdb.dbo.addresslist addresslist
on allanis.phone = addresslist.phoneDoes this work?
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
-
Change your query to
update allanis
set street=addresslist.street,
city=addresslist.city,
state=addresslist.state,
zip=addresslist.zip
from amyexcel...allanis$ allanis
inner join masterdb.dbo.addresslist addresslist
on allanis.phone = addresslist.phoneDoes this work?
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
Yes, Thanks! I was afraid it'd come to joins. Tomorrow maybe I can figure out exactly how that works. Next I think I'll send the results to the person who requested them. I'm sure they will not be happy as only 135 of the 4,000 records had matches. Hey, I can only work with the data I'm given.
-
Yes, Thanks! I was afraid it'd come to joins. Tomorrow maybe I can figure out exactly how that works. Next I think I'll send the results to the person who requested them. I'm sure they will not be happy as only 135 of the 4,000 records had matches. Hey, I can only work with the data I'm given.
JABOSL wrote:
Next I think I'll send the results to the person who requested them. I'm sure they will not be happy as only 135 of the 4,000 records had matches. Hey, I can only work with the data I'm given.
Unfortunately, GIGO is a fact of life for those of us who have to deal with data from end users :)
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
-
JABOSL wrote:
Next I think I'll send the results to the person who requested them. I'm sure they will not be happy as only 135 of the 4,000 records had matches. Hey, I can only work with the data I'm given.
Unfortunately, GIGO is a fact of life for those of us who have to deal with data from end users :)
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush