How To Compare Columns Of Two Tables In Two Different Servers

Mar 18, 2008



I have two table with the same name that reside in different databases. The two database have the same name, but reside in two linked servers
TABLE A: [ServerA].[ProdDB].dbo.[Orders]
TABLE B: [ServerB].[ProdDB].dbo.[Orders]


How do i find out if the two tables have the same number of columns or if the a column that exists on TableA does not exist on TableB.

Does any one have a script that could help me with this task. Thanks

I am using SQL Server 2005

View 8 Replies


ADVERTISEMENT

Transact SQL :: How To Compare Columns On 2 Databases On 2 Different Servers

Aug 12, 2015

I need to compare columns in tables on 1 database on one server versus the same on a 2nd server.

I'm looking for added columns in dbase 1.

Is there a system T-SQL script that can be used for this?

View 4 Replies View Related

Compare Tables Of Different Servers

Oct 8, 2014

I have two databases under two different servers.

Server1:
DB1
Table1

Server2:
DB2
Table2

I need to compare Table 1 & 2 using SQL server and how can I achieve this?

View 2 Replies View Related

SQL Script To Compare Two Identical Tables On Two SQL Servers

May 23, 2007

Hi all,
I have two tables A and B on two both tables have similar architectures bout contain some deferent records.

I like to compare them and find and view the differences in records.


Thanks for any help.

Abrahim

View 3 Replies View Related

How To Compare 3 Columns From 2 Tables

Jul 26, 2013

I have requirement like this

1 st column 'A1Ctest' 2 nd column 'diagnoising heart disease' and my 3 rd column is combination of both columns
'A1Ctest for diagnoising heart disease'.Here i need to comapre 'A1Ctest' from 1st column and 'diagnoising heart disease' from 2 nd column

View 5 Replies View Related

SQL 2012 :: Compare Two Columns In Three Tables

Dec 16, 2013

I have one database with several tables in it (table 1, table2, table3). In each table is two colums (colum1 = a number (201220) and colum2 = a number (0.50). Now, both tables will have rows with the same data in colum 1, but colum two will have different numbers (different prices). My goal is to run a query that will compare both colums in all three tables, take the lower of the three based on colum 2 and spit out the row. Obviously, this would output all rows (around 175k). The point is to create a least cost spreadsheet (csv) file based on evaluating all three tables.

View 9 Replies View Related

Compare Databases Between 2 SQL Servers

May 17, 2006

Hello,I wish to see if the tables from database A server A1 exist in databaseA on server B1.I setup a linked server from my first server (robertcamarda) to atarget (cognos-dev)This works from robertcamarda:select count(*) from [cognos-dev].ds_v6_source.dbo.stdmasand this works:select * from ds_v6_source.information_schema.tables order bytable_namebut this wont work:select * from [cognos-dev].ds_v6_source.information_schema.tables orderby table_nameError:Msg 117, Level 15, State 1, Line 1The object name 'cognos-dev.ds_v6_source.dbo.information_schema.tables'contains more than the maximum number of prefixes. The maximum is 3.I want to do something like:select * from ds_v6_source.information_schema.tableswhere table_name not in (select table_name from[cognos-dev].ds_v6_source.information_schema.tables order bytable_name)so I can see of the sql server (robertcamarda) has any missing tablesthat exist on the server (cognos-dev)TIARobSQL Server 2005 Enterprise

View 3 Replies View Related

SQL 2012 :: Compare Database Across Servers

May 28, 2014

I want to compare the database structures(columns,datatypes..etc) across same databases located in different servers.

View 9 Replies View Related

SQL 2012 :: How To Compare Settings Of Two DB Servers

Sep 24, 2014

I am trying to reproduce a problem that is probably related to how a particular SQL server is configured. Is there a tool or best practice that is useful to compare all of the configuration settings between two SQL Servers (same version)?

View 2 Replies View Related

When Have 4 Tables How To Compare Table With 4nd Table? ( Need To Join And Compare With Datatime)

Jul 22, 2007

Table MediaImportLog
column ↘ImportIndex     ImportFileTime            ImportSource
value    ↘80507             20060506001100          815
              80511             20061109120011           CRD                       ã€? P.S the values type of ImportFileTime 20060506001100 → 2006 -year 05-month 06-day 00-HH 11-minute 00-second】
Table  BillerChain
column↘BillerInfoCode       ChainCode
value   ↘750                      815
value   ↘81162                  CRD
Table   Biller
column↘CompanyCode         BillerCode
value   ↘999                     750
value   ↘81162                  516
TAble DataBackup
column↘CompanyCode         Keepmonth
value   ↘999                     6
value   ↘81162                 12
 
---------------------------------------------------
 
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth
How can I get the keepmonth? can I use parameters ? 
 
thank you very much 

View 3 Replies View Related

Compare Two Columns With WildCard

Aug 1, 2006

I am trying to compare columns in two tables with a wildcard character.

One table: Other Table:
----------------------
ID Col1 | ID Col2
---------------------
1 1 1 1A
1 1B
2 2 2 2A
3 3 3 3A
4 5 4 5A
4 5B
4 5C
5 7
6 27
7 50 7 50A
-------------------------------
I want to writing something like:

SELECT Table1.ID, Table1.Col1, Table2.ID, Table2.Col2
From Table1, Table2
WHERE (Table1.ID = Table2.ID) AND (Table2.Col2 LIKE Table1.Col1%)

which obviously does not work.

basically "column2 Text%" so if ID = 1, Col1 = 1 => will have the following comparisons turn out true:

1A LIKE '1*'
1B LIKE '1*'

How can I do a comparison like this?

View 4 Replies View Related

Compare Values From Two Columns?

Sep 20, 2013

I am trying to compare values from two columns but I don't know what I am doing, I tried select custom5,skipcount substr( custom5, 1, 4) as 'mynumber' from songs where custom5 > '0000%' where skipcount > cast( substr( custom5, 1, 4) as int ); But that is obviously wrong?

View 4 Replies View Related

Compare Two Columns And Update The Third Column

May 21, 2008

 Hi,I have three columns and want to update the third column based on the comparison of first two columns in the same table.For instance:Col1 - val1, val2, val3, val4, val5......Col2 - NULL, NULL, val1, NULL, val2....Col3 - TRUE, TRUE, FALSE, FALSE, FALSE....Thanks for any help.

View 4 Replies View Related

T-SQL (SS2K8) :: How To Compare Two Rows And Two Different Columns

Oct 22, 2014

I am fairly new to SQL and writing queries so bear with my faults. I am learning on the job, which is good and bad. Below is a query that I have written to obtain some information. The problem arises when we have a patient who goes from Patient Type '1' to Patient Type '2'. This needs to be considered a singular visit and the only way I can think that this may work is if: for any specific medical record a dsch_ts is equal to the Admit TS on the next row.

How to complete something like this and my google searches have been fruitless. I attached a spreadsheet with an example of what I am getting.

SELECT DISTINCT
TPM300_PAT_VISIT.med_rec_no,
TSM040_PERSON_HDR.lst_nm AS 'Last Name',
TSM040_PERSON_HDR.fst_nm AS 'First Name',

[Code] ....

View 6 Replies View Related

MS SQL Compare Columns To Generate Display Name

Sep 17, 2007

Hello, I have the following table with 4 columns....firstname, lastname1, lastname2, EMAILTable has user names and email, I would like to generate a 5th columncalled DisplayName.The email Id is sometimes firstname.lastname1.lastname2@ and othersjust firstname.lastname1@I would like to generate the display name exactly like the email egfirstname.lastname1.lastname2@ displayName = firstname lastname1lastname2......so for james.smith display name = James Smith and forjames.earl.smith displayName = James Earl Smith etc etcIs there a way that I can check/compare email Id (before the @ part)with firstname, lastname1 and lastname2 and generate a display namebased on what was used for the email address?I hope I've explained this well :-)Many thanks in advance for any help/adviseYas

View 5 Replies View Related

Unique Constraint W Nulls, Compare Columns

Jun 20, 2008

Hi Guys.

I have a problem. I have two feilds in a table. Both of them are (int). I want to be able make sure the same number is not put in both columns for each row. Both colums have to be a different number, and also if one is null, the other cannot be. How do I go about enforcing this policy?

For Example:

This is what I want

ID Column1 Column2
--- -------- ---------
1 5 8
2 5 7
3 null 10
4 6 2


This is what I DO NOT want:

ID Column1 Column2
--- -------- ---------
1 5 5
2 5 5
3 7 7
4 null null


I created a unique constraint between the 2 columns, but it still allows dups within the row. Please help me.

View 1 Replies View Related

Compare 2 Rows To Find The Difference In Columns?

Aug 14, 2014

How would I compare 2 rows to find the difference in the columns?

Example:
ID Column1 Column2 Column3
1 Text1 Text4
2 Text1 Text2 Text3

Result:
Column1: Text2 New
Column3: Text4 Old Text3 New

View 6 Replies View Related

SQL Server 2012 :: Compare Two Different Columns Of 2 Different Rows In A Data Set?

Jan 29, 2014

Is there a efficient way to compare two different columns of 2 different rows in a data set as shown below.

For eg: I would like to DateDiff between Date2 of RowID 1 and Date1 of RowID 2 of IDNo 123. After this comparision , if datediff between two dates are <=14 then i want to update 1 else 0 in IsDateDiffLess14 of RowID1 . In below example its 0 because datediff of two dates >=14. So, want to compare the Date2 and Date1 in this sequence for the same IDNo. For RowID 6 there is only 1 row and no other row to compare, in this case IsDateDiffLess14 should be updated with 0.

RowID IDNo Date1 Date2 IsDateDiffLess14
1 123 04/10/2013 04/12/2013 0
2 123 05/10/2013 05/11/2013 1
3 123 05/21/2013 05/25/2013 0
4 112 01/10/2013 01/14/2013 1
5 112 01/27/2013 01/28/2013 0
6 120 03/10/2013 03/12/2013 0

View 4 Replies View Related

SQL Server 2012 :: Compare Dates Between 2 Different Rows And Columns?

Feb 18, 2015

What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.

View 9 Replies View Related

T-SQL (SS2K8) :: How To Compare Data (join) Based On Two Varchar Columns

Mar 15, 2014

-- My first Data

create table #myfirst (id int, city varchar(20))
insert into #myfirst values (500,'Newyork')
insert into #myfirst values (100,'Ediosn')
insert into #myfirst values (200,'Atlanta')
insert into #myfirst values (300,'Greenwoods')
insert into #myfirst values (400,'Hitchcok')
insert into #myfirst values (700,'Walmart')
insert into #myfirst values (800,'Madida')

-- My Second Data

create table #mySecond (id int, city varchar(20),Sector varchar(2))
insert into #mySecond values (1500,'Newyork','MK')
insert into #mySecond values (5500,'Ediosn','HH')
insert into #mySecond values (5060,'The Atlanta','JK')
insert into #mySecond values (7500,'The Greenwoods','DF')
insert into #mySecond values (9500,'Metro','KK')
insert into #mySecond values (3300,'Kilapr','MK')
insert into #mySecond values (9500,'Metro','NH')

--Third Second Data

create table #myThird (id int, city varchar(20),Sector varchar(2))
insert into #myThird values (33,'Walmart','PP')
insert into #myThird values (20,'Ediosn','DD')
select f.*,s.Sector from #myfirst f join #mySecond s on f.city = s.city
/*
idcitySector
500NewyorkMK
100EdiosnHH
*/

i have doubt on two things

1) How Can i compare the City names, by eliminating 'The ' at the beginning (if there is any in second tale city) between first and second

2) after comparing first and second if there is no match found in second them want to compare with third table values for those not found

--i tried below to solve first doubt, it is working but want to know any other wasys to do it

select f.*,s.Sector from #myfirst f join #mySecond s on replace (f.city, 'THE ','')= replace (s.city, 'THE ','')

--Expected results wull be

create table #ExpectResults (id int, city varchar(20),Sector varchar(2))
insert into #ExpectResults values (200,'Atlanta','JK')
insert into #ExpectResults values (100,'Ediosn','HH')
insert into #ExpectResults values (300,'Greenwoods','DF')
insert into #ExpectResults values (500,'Newyork','MK')
insert into #ExpectResults values (700, 'Walmart','PP')
insert into #ExpectResults values (800, 'Madidar','')

[code]....

View 1 Replies View Related

Compare Two Tables

Dec 8, 2000

I need to compare two identical tables and return the rows that are different,they are not having any primary key. Anyone to help with the query?
Thanks.

View 1 Replies View Related

Can I Compare Two Tables

Jan 14, 2000

Is it possible (easily) to compare two identical tables (one from server1 'SQL7', one a download from an Oracle DB into SQL7) to see if the Oracle table has changed, what is the changed row and place said row into another table to do some other process on those changed rows?

Thanks,
Bill

View 2 Replies View Related

How To Compare 2 Tables ??

Jul 1, 2004

Hi,

Need help with the structuring of the following query:
I've got 2 tables nl :tableA, tableB
I need to do a query to determine wat DISTINCT values of a column(cIdNo) in tableA is not present in column (bIdNo) in tableB.

tableA contains Id numbers of people who still has to pay their bills, and tableB contains Id numbers of people who already paid. So I want a query to determine who still needs to pay.

I've tried the following but doesn't work, not to sure if it's the right wayto do it:


PHP Code:




 SELECT  distinct(cIdno) as IdNumber 
FROM tableA 
LEFT JOIN tableB
ON tableA.cIdNo != tableB.bIdNo 






PLEASE HELP !! Thanks in advance !!!

View 3 Replies View Related

How To Compare Each Row In Two Tables

Jul 10, 2012

I have two the same structure tables. One is the source table (table1), another one is the destination table (table2).

Now I want to compare these two table row by row.

For example,

If table1 row1 is the same as table2 row1, go to next
If table1 row2 is not the same as table2 row2, update table2 row2 to make it the same as table1 row2.
If table1 row3 is not existed in table2 row3, insert table1 row3 into table2 blank new row.

How to create this script?

View 3 Replies View Related

Compare 2 Tables

Jun 8, 2006

I have 2 tables that are exactally the same.

Table 1 - Is named LIVE
Table 2 - Is name EOM (End of Month)

The 2 tables have these fields
MEMBER
STREET

I want to build a query that will return any member who has changed their address (ex: member's eom address is "123 main st", and now their live address is "345 apple st")

But I do not want to display any addresses that are different because that member wasn't a member last month (not in the eom table)

Note: Member Numbers Never Change, only addresses will.

Thanks in advance for any help/suggestions.

View 2 Replies View Related

How To Compare 2 Tables

Dec 17, 2007

need to know how to check both table data and return only data that doesn't match the other table data


select distinct dept_name
from bb_guide_party_dept d, binbox_dept e where d.dept_id = e.dept_id
and e.active_Flag = 1

union
select distinct dept_name
from bb_guide_party_dept a, binbox_dept b where a.dept_id = b.dept_id
and e.dept_name not the same as b.dept_name

View 3 Replies View Related

How To Compare Different Tables

Jan 31, 2008

hi, I'm pretty new to ssis so this may be an absolute beginner question but i couldn't find a solution yet. the task is to compare data from tow tables which are of the same structure. table1 contains data before some operations and table2 contains the results. now i have to find out which datasets have been changed, which one are new and which one are gone.

i've done some googlin and found this TableDifference http://www.sqlbi.eu/Projects/TableDifference/tabid/74/Default.aspx component, but to me it seems to be kind of buggy, so I had the idea of trying to set an sql statement on both ole db sources where i tried to select only the datasets that are not contained in the other table. so i tried something like this:

select * from lib.table_1 except select * from lib.table_2

but this doesn't work either. so i would be really thankful if someone could help me.

View 1 Replies View Related

How To Compare Data In Tables

Sep 13, 2001

I know this sounds simple, but I haven't seen it in bol. I need to compare two tables, and list what rows are unique to each table. Thanks for the help!

rb

View 2 Replies View Related

Compare Two Tables...URGENT!

Dec 7, 2000

Can anyone give me the query that I can use to to compare the data between two similar tables.Data from col1,col2,col3,col4 needs to be compared. Col1 is primary key in both the tables.It should return with zero rows if they are same.
Thanks for any help.

View 1 Replies View Related

Compare/filter 2 Tables...help!!!

May 13, 2004

Would appreciate if someone could help!

I have 2 tables , table 1 and table 2. Exact copies of some records from table 1 also exist in table 2. What i need to do is display records that exist in table 1 but do not exist in table 2.

Thanks for your help!

View 3 Replies View Related

Query To Compare Two Tables

Aug 4, 1999

Hi,

I am trying to write a query to compare the same column in each table with "not equal" expression.

My query is like this:

select tableOne.empl_ser_no from tableOne, tableTwo
where tableOnel.empl_ser_no <>(select empl_ser_no from tableTwo)

I am getting the following message from SQL Server:

Msg 512, Level 16, State 1
Subquery returned more than 1 value. This is illegal when the subquery follows =, !=, <, <= , >, >=, or when the subquery is used as an expression.
Command has been aborted.

View 1 Replies View Related

How Do You Compare The Data In Two Tables

Sep 29, 2005

I have two tables and I want to know if every record from the first table is in the second one and if its data mathes exactly?

Any suggestion for a short way to do this?

Thank you!

View 10 Replies View Related

Compare Each Rows In Two Tables?

Nov 16, 2011

I need to compare two tables in each row. the tables are as follows:-

Table a:

Code:
IDFirst_Name Last_name Birthdate
1Shradha Deshkmukh 1981-12-25 00:00:00
2Shradha Verma 1981-05-11 00:00:00
3Divya Dutta 1982-07-21 00:00:00
4Matthew Palmer 1983-12-28 00:00:00

table d:-

Code:
idfnlndob
1ShradhaTiwari1981-12-25 00:00:00
2DivyaDutta1983-07-21 00:00:00
3SulabhManesar1975-09-11 00:00:00
4MatthewPalmer1983-12-28 00:00:00
5SamuelMaxwell1984-05-22 00:00:00

I want to compare the tables using first name, and I have a log variable which I want to have the value as per the differences in the table that is if the first name matches and second name and dob dont match it shows log value for that FN as 'LN and DOB dont match'.

similarly if First name matches and dob matches then @log is 'LN not match'.

And in case all three match it should show 'match'as log value.The query I use is a s follows:-

Code:
USE testing
GO
DECLARE @NR int
DECLARE @log varchar(200)
SELECT @NR = COUNT(*) FROM a
WHILE @NR>0

[code]...

the result I am getting is :-

Code:
fnlndob (No column name)
ShradhaTiwari1981-12-25 00:00:00match
ShradhaTiwari1981-12-25 00:00:00match
DivyaDutta1983-07-21 00:00:00match
MatthewPalmer1983-12-28 00:00:00match

I have tried using CASE but that doesnt work either.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved