September 1, 2015 at 9:41 am
I need to update the AcquiredFrom table from source_office table. both the tables are from different database and server. we can match them on Code and source code. Initially if we can find out the discrepancies and then how can we fix them. Also there might be some dealerships that would have to be added to acquiredfrom table from the source_office table if they are not there already.
Table 1 (AcquiredFrom) database (Service) Server(FS3)
Select Code, Name, ContactName, AddLine1, AddLine2, city, state, zip, PhoneNumber
FROM [dbo].[AcquiredFrom]
Order by Code
Table 2 (source_office) database (DCP_PROD) Server (DPROSQL)
Select source_code, name, addr1, addr2, city, state_id, zip, phone FROM [dbo].[source_office]
order by source_code
I would really appreciate if someone could help me understand the process I need to follow.
September 1, 2015 at 10:08 am
The simplest (but somehow not recommended) solution would be to create a linked server to DPROSQL in FS3.
Then you could simply issue this:
UPDATE AF
SET AF.SomeColumn = SO.SomeColumn
FROM [dbo].[AcquiredFrom] AS AF
INNER JOIN DPROSQL.DCP_PROD.dbo.source_office AS SO
ON AF.Code = SO.SourceCode
Linked Servers however are not recommended for this kind of operation due to performance issues (the remote table is usually downloaded to tempdb with no index whatsoever, so you can imagine the pain joining big tables).
Another option is to download the contents of the remote table to a table in tempdb that you control (so that you can have indexes and whatever is needed).
Once your data is in the temporary table, you can use the usual JOIN queries to identify missing/nonmatching rows and do the needful.
The process of importing the data can be accomplished with a SSIS package or with Import/Export wizard (which is SSIS under the covers).
If you need more assistance with any of these steps, please let me know.
-- Gianluca Sartori
September 1, 2015 at 10:15 am
That might be complicated, any other one time fix solutions possible?
September 1, 2015 at 10:16 am
Sorry, I hit "post" by mistake and then edited back my answer with more details.
Feel free to ask for clarifications.
-- Gianluca Sartori
September 1, 2015 at 10:40 am
Thanks Sartori, I am new to this and have not done this before, if we go with the second option, are you suggesting to download the contents of the below query to tempdb database, that's on the server DPROSQL. How do I do that? and also do I have to the same thing for the 2nd query below, server FS3. If you could provide me step by step information that would be great
Select source_code, name, addr1, addr2, city, state_id, zip, phone FROM [dbo].[source_office]
order by source_code
Select Code, Name, ContactName, AddLine1, AddLine2, city, state, zip, PhoneNumber
FROM [dbo].[AcquiredFrom]
Order by Code
September 1, 2015 at 10:52 am
If I understand correctly, you have to update the data in AcquiredFrom using the data in source_office on DPROSQL.
In this case, you just need to copy the data of source_office from DPROSQL to FS3.
You can use the Import/Export Wizard. It's really simple. Follow the guide here: https://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/
Once the data is in FS3, join the two tables and find missing/different rows.
-- Gianluca Sartori
September 1, 2015 at 11:17 am
Quick question. Am I importing the table source office into the tempdb databse or the service database where the acquiredfrom table is present?
September 1, 2015 at 11:45 am
The one you prefer. I would use tempdb to avoid clutter.
-- Gianluca Sartori
September 1, 2015 at 1:42 pm
Awsum, I am so grateful to you. So I went ahead and exported the source_office to database Service and Server FS3, naming the new table source_office1.
One thing I was looking here was has any of the names, address, or phone numbers have changed to the ones in new table source_office1. I did that using the below query:
SELECT
AF.Name AS [Name as of Acquired From]
,SO.name AS [Name as of Source Office]
,AF.AddLine1 AS [Address as of Acquired From]
,SO.addr1 AS [Address as of Source Office From]
,AF.PhoneNumber AS [Phone Number as of Acquired From]
,SO.Phone AS [Phone Number as of Source Office]
FROM
AcquiredFrom AS AF
Inner JOIN source_office1 AS SO
ON AF.Code = SO.source_code
Where AF.Name <> SO.name
Also I am looking for all the records source code present in source_office1 and not in acquired_from, so I can insert them.
What would be the query I should use now: I tried this one but I am not sure it is the correct one, can you look at both the query and see If I am missing soemthing
SELECT
SO.source_code
,AF.Name AS [Name as of Acquired From]
,SO.name AS [Name as of Source Office]
,AF.AddLine1 AS [Address as of Acquired From]
,SO.addr1 AS [Address as of Source Office From]
,AF.PhoneNumber AS [Phone Number as of Acquired From]
,SO.Phone AS [Phone Number as of Source Office]
FROM
AcquiredFrom AS AF
---Inner JOIN source_office1 AS SO
Right Outer JOIN source_office1 AS SO
ON AF.Code = SO.source_code
Where AF.Name IS NULL
--Where AF.Name <> SO.name
September 1, 2015 at 1:50 pm
Also what would be a faster way of updating the acquired from table with the new table, instead of doing it manually for each discrepancy.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply