September 28, 2020 at 10:52 pm
Hi all, new here.
I was using Access and almost finished creating what I needed to accomplish and realized I should decided to use SQL. No knowledge, but have managed to get around. I played around with importing access db and running queries that I created in Access in a DB.
Realizing I should start building DBs & Tables as I will be structuring things, I hit a wall. My queries were working until I decided to create separate DBs and Tables. I have read and read and i probably don't grasp the logic quite yet as I did in Access.
I'm pretty sure you have to specify the DBs & tables that you want to query, but I am going in circles and looking for someone to help me move forward.
So here is my query
UPDATE [Mohawk Prod]
SET [Mohawk prod].backing_facet = [mapping_backing_facet].map_to
FROM [Mohawk Prod]
INNER JOIN [MAPPING_BACKING_FACET]
ON [Mohawk Prod].backing = [mapping_backing_facet].map_from
I have a DB named named Mohawk_Production and a table named dbo.Mohawk Prod
I have a Mapping DB named MAPPING and a a table named mapping_backing_facet
The above query worked when all these tables were in the same DB.
Can someone help and point out the obvious......Thanks all
September 29, 2020 at 4:44 am
Where are your tables? Are they both in SQL Server or is one still in Access? If one is still in Access, where are you running this query from?
You may be able to use linked tables in Access, create the query there, and execute it.
Just as a sanity check, I would create a plain SELECT query between these two tables and see if it returns the results you expect. If it does than something else is wrong.
September 29, 2020 at 10:03 am
Tables are all in SQL.
I need to use the JOIN if you have an example of how I showed wrote query that would be great.
Thanks.
September 29, 2020 at 3:56 pm
Try not to have spaces in object names.
Use three part naming convention.
MERGE will stop indeterminate updates by throwing an exception if there is more than one value.
MERGE Mohawk_Production.dbo.[Mohawk Prod] P
USING MAPPING.dbo.mapping_backing_facet M ON P.backing = M.map_from
WHEN MATCHED THEN
UPDATE SET backing_facet = M.map_to;
September 29, 2020 at 6:54 pm
Not sure why you think you need separate databases. You most likely can resolve your issues by using separate schemas instead of creating separate databases.
And - as was stated previously, you really should not create objects in SQL Server with spaces or other 'special' characters. If you want the object name to be separated - use an underscore instead of a space.
Even if you decide to keep separate databases, consider using schemas and synonyms. For example, in the [Mohawk Production] database you would create the schema [Mapping] and then create the synonym: CREATE SYNONYM Mapping.mapping_backing_facet FOR Mapping.dbo.mapping_backing_facet;
In your code - you then use Mapping.mapping_backing_facet instead of the 3-part name. In the future, if you decide to move that database to a separate server you can just update the synonym(s) and your code doesn't have to be edited. And...if you decide to move everything back into the same database you can move those tables/objects into the [Mapping] schema directly (dropping the synonyms) and no code changes will be necessary.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 1, 2020 at 1:50 pm
Hi,
Thanks for the tip and I will keep it in mind as it looks easier. As I said, I'm new and for some odd reason SQL compared to what I did in ACCESS is a foreign language. I understand what you are saying but I think there will be many growing pains.
I managed to figure out the query as it is what I though, but just didn't know how to do at the time.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply