April 6, 2009 at 6:30 pm
I need help with the SQL query to comare two tables in two seperate databases on the same server. The tables have FirstName, LatName and Email as common fields.
April 6, 2009 at 6:57 pm
Hi,
You need to fully qualify your table names when comparing accross databases. i.e. DatabaseName.owner.table
'owner' will probably be dbo.
Write a query similar to the following and see how you go:
select * from database1.dbo.firsttable where LastName in (select LastName from database2.dbo.secondtable)
That query will show all common records according to lastname. Once you have something like that working feel free to expand on what else you need to achieve.
Bevan
April 6, 2009 at 7:06 pm
Awesome, worked like a charm.
April 6, 2009 at 11:11 pm
AFIFM (4/6/2009)
I need help with the SQL query to comare two tables in two seperate databases on the same server. The tables have FirstName, LatName and Email as common fields.
I have to ask, what are you going to do when you find out that they're equal or not?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2009 at 8:00 am
AFIFM (4/6/2009)
I need help with the SQL query to comare two tables in two seperate databases on the same server. The tables have FirstName, LatName and Email as common fields.
When you say compare, you want to find the records which are not in another table or find teh common records or !!
In either case If you use FULL OUTER JOIN it would really help you to understand your data in this case.
April 7, 2009 at 2:12 pm
Table 'ClientsDB.Users' is in 'MyClientsDB' database and table 'MarketingDB.Users' is in 'MyMarketingDB'. Table 'ClientsDB.Users' has 12,000 records that I want to copy to 'MarketingDB.Users'. I got the number of records (12,000) by doing the compare.
Now, I just found that the one of the fields in 'ClientsDB.Users' is [VARCHAR(50)] and the equivalent field in 'MarketingDB.Users' is [int]. So how do I make the change from VARCHAR to int.
April 7, 2009 at 2:17 pm
When you say compare, you want to find the records which are not in another table or find teh common records or !!
In either case If you use FULL OUTER JOIN it would really help you to understand your data in this case.
I would to try different ways so if you can send a sample for FULL OUTER JOIN that would be great. By the way, I was trying to find which records in table A that not in table B so I can copy the what is not in A to B.
April 7, 2009 at 2:19 pm
I have to ask, what are you going to do when you find out that they're equal or not?
I was trying to find which records in table A that not in table B so I can copy those to table B.
April 7, 2009 at 5:35 pm
You need to use the CAST function:
http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx
Something like:
select cast(users as int) from clientsDB.dbo.firsttable where cast(users as int) not in (select users from MarketingDB.dbo.secondtable)
Then to do an insert:
insert MarketingDB.dbo.secondtable (users, nextColumn, otherColumns)
select cast(users as int), nextColumn, otherColumns from clientsDB.dbo.firsttable where cast(users as int) not in (select users from MarketingDB.dbo.secondtable)
The above code is just so you get the idea. You need to ensure all your column names are listed when you select from your source table and that they correspond to the destination table.
Bevan
April 7, 2009 at 5:42 pm
AFIFM (4/7/2009)
Now, I just found that the one of the fields in 'ClientsDB.Users' is [VARCHAR(50)] and the equivalent field in 'MarketingDB.Users' is [int]. So how do I make the change from VARCHAR to int.
EX: ALTER TABLE EMPLOYEE_WORKING_HRS ALTER COLUMN YR VARCHAR(4)
This is the way you can change the datatype. Even if you convert would the data still be the same?? Coz one column is varchar there is a potential that this contains characters and another one is INT..and this strictly contains the numbers. Is this domain type??
April 7, 2009 at 5:49 pm
Do I have to use CAST/CONVERT when I change data type from [VARCHAR] to [int]? My VARCHAR has only values of "1", "2", "3", .... "8". I read something about Implicit and explicit conversion and that I do not have to use CAST or CONVERT in this case, I just need to confirm.
April 7, 2009 at 6:02 pm
Is this domain type??
No it is not. One of the legacy applications (A) uses 'Access_Level' as VARCHAR which only uses "1", "2", "3",....."8". The second application (B) uses the 'Access_Leval' as integer. I need to copy the records from (A) to (B) where is no matching records. The challange is that I need to do that on regular basis until I build a System of Records applications that can be used across the organization for access to company sites. Currently I have 3 sites that each with a seperate sign-in.:w00t:
I just want to add that this is a great board and the help i am getting is unbelievable and it is really appreciated.:-)
April 7, 2009 at 6:05 pm
You are right, it will convert automatically. I believe it will attempt to convert the varchar to an int.
The explicit conversion is useful as it will catch any bad data and reminds anyone looking at the code what it is doing.
April 7, 2009 at 6:40 pm
until I build a System of Records applications
I would like to take this issue into a different direction. As I mentioned above, I am in the planning phase towards building a Single Sign On (SSO) application to handle users’ registration for the organization several web sites. My intention is to build the system using the System of Records (SOR) database which will have some users’ information and a localized database for each site. I am doing this for several reasons, one of which is that the SOR will have to be hosted internally because I am using proprietary software. Another reason is that each web site focuses on different type of information, which is collected from the visitors and I do not see a compelling reason to have all the information in the SOR database as it will be very flat. Having said that, my manager wants to use one internal database and I am trying to convince her with the opposite.
Do you have any input on this? What is the argument for one case over the other? If this question does not belong on this forum, please let me know and I will move it.
April 7, 2009 at 10:45 pm
AFIFM (4/7/2009)
Is this domain type??
No it is not. One of the legacy applications (A) uses 'Access_Level' as VARCHAR which only uses "1", "2", "3",....."8". The second application (B) uses the 'Access_Leval' as integer. I need to copy the records from (A) to (B) where is no matching records. The challange is that I need to do that on regular basis until I build a System of Records applications that can be used across the organization for access to company sites. Currently I have 3 sites that each with a seperate sign-in.:w00t:
I just want to add that this is a great board and the help i am getting is unbelievable and it is really appreciated.:-)
I've not read all the posts on this thread, but why not just use replication for this?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply