SQL Server 2012 :: Compare Column In Two Tables?

Apr 20, 2015

i have two tables.

Table A
IdName
101Dante
102Henry
103Harold
104Arnold

Table B
NumberName
102Dante
107Gilbert
109Harold
110Arnold
106Susan
112Marian

I want the result in table 3 like below, if value exists in Table A and not exists in Table B then the record should enter in table 3 with table name in new column, and vice versa.

Table C
Col1Col2
HenryTable A
Gilbert Table B
Susan Table B
Marian Table B

using below logic to get the values from tables..

select
t1.columnA
, t2.*
from
table1 t1
join table2 t2 on t2.columnB = t1.columnA

View 9 Replies


ADVERTISEMENT

SQL Server 2012 :: Compare Tables With Count?

May 6, 2015

using below script to compare two tables and get the values.

how to get the count of 'Table A' , 'Table B' , 'Table A & Table B' using below script.

Ex:
'Table A' -- 150
'Table B' -- 300
'Table A & Table B' -- 150
SELECT
Col1 = ISNULL(a.name,b.name),
Col2 =
CASE
WHEN ISNULL(a.name,'') = '' THEN 'Table B'
WHEN ISNULL(b.name,'') = '' THEN 'Table A'
ELSE 'Table A & Table B'
END
FROM #tableA a
FULL JOIN #tableB b
ON a.name = b.name;

View 1 Replies View Related

SQL Server 2012 :: Compare Row Count Between Two Tables With 10k Rows?

Dec 18, 2014

I run the script below once a day to keep track of row count over time. I would like to compare the results from today and yesterday to see if anyone deleted more than 20% of data from any given table. How would I do this? I really don't need the data anymore than a day just to compare the results.

Mon - Run script to collect row count
Tues - Run script to collect current row into temp table
,compare all row count in both tables
,purge records from Monday and insert current
Wed - Run script to collect current row into temp table
,compare all row count in both tables

[code]....

View 4 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

SQL 2012 :: Compare Two Tables A And B - Copy Matching Values To Table C

Mar 31, 2014

I have 3 table

table_A
table_B
table_C

TABLE_A
SNO NAME ID
1 RAJU 070491
2 VAMSHI 089767
3 ARUNA 068908

TABLE_B

SNO NAME ID
2 RAJU 070491
4 JKLKJ 098766

I WANT COMPARE TWO TABLES(TABLE_A & TABLE_B) SELECT TABLE_A MATCHING VALUES COPY IN TABLE_C

EX-

SNO NAME ID
1 RAJU 070491

View 3 Replies View Related

T-SQL (SS2K8) :: Compare Multiple Column Between Two Tables

Aug 14, 2014

I want to display records from @table1 only when combination of col2,col3 and col4 are present in @table2.In Below case I want output as: below two records only.

'test1', 'need this record', 25, {d '1901-01-01'}
'test3', 'some longer value', 23, {d '1900-01-01'}
declare @table1 table (
col1 varchar(10) not null,
col2 varchar(200) null,
col3 int not null,

[code]....

View 2 Replies View Related

Compare Column Names From Two Tables In Same Database

May 17, 2008

I'm not a full-time DBA, so excuse my style of expressing my question.I have a database which has 2 tables in SQL 2005. Both these tables have similar column names, EXCEPT for new extra columns in FY2007_DATA. I can visually see the difference in columns in Database Diagrams. My goal is to :- I want to compare FY2007 tbl column names to FY2006 column names and display only those columns as results that do not match.Tbl 1 :- FY2006_DATA Tbl 2:-



FY2007_DATA

With online reading and help I have managed to get this script to do exactly opposite of what i want. Below is the query

/* This query compares the column names from two tables and displays the ones that have an exact match. It does not care for case-sensitiveness */

Select a.Table_Name, a.Column_Name, (b.Table_Name), (b.column_name)
From [2006-2011].INFORMATION_SCHEMA.Columns AS a
Join [2006-2011].INFORMATION_SCHEMA.Columns AS b on a.Column_Name = b.Column_Name
Where a.TABLE_NAME = 'FY2006_DATA'
And b.TABLE_NAME = 'FY2007_DATA'
AND a.Column_Name IN
(Select Column_Name = LEFT(c.column_name, 20) FROM
[H1B_2006-2011].INFORMATION_SCHEMA.Columns AS c WHERE c.TABLE_NAME = 'FY2007_DATA' )


When I change "AND a.Column_Name IN.." to "AND a.Column_Name NOT IN.." so that the results will (should) display the extra columns in FY2007, in fact I do not see any results, but query executes perfect.

How can I achieve my goal.?

Thank you

View 1 Replies View Related

SQL 2012 :: Database Project Schema Compare Fails To Pull In CDC Tables

Jul 11, 2014

I have a database project where objects have been pulled in from the database using schema compare.

Unfortunately CDC tables which are referenced in stored procedures on the database have not been pulled in by the schema compare & hence I cannot build the project and deploy changes back to the database.

How to get these tables included in the project .

View 1 Replies View Related

SQL Server 2012 :: Count From Multiple Tables For Same Column Exists In Database?

May 19, 2014

i have database which has 25 tables. all tables have productid column. i need to find total records for product id = 20003 from all the tables in database.

View 9 Replies View Related

SQL Server 2012 :: Compare Two XML Variables?

Mar 19, 2015

I'm rewriting a huge FOR XML EXPLICIT procedure to use FOR XML PATH, and need to compare previous output to the refactored one, so i didn't mess up XML structure.

The thing is, i'm not sure that SQL Server will always generate exactly same xml **string**, so i'd rather not compare by:

WHERE CAST(@xml_old AS NVARCHAR(MAX)) = CAST(@xml_new AS NVARCHAR(MAX))

nor do i want to manually validate every node, since the generated xml-structure is quite complex.

compare xmls by their "semantic value" ?

View 8 Replies View Related

SQL Server 2012 :: How To Compare A List Of Values

Aug 3, 2015

how would I compare a list of concrete values?

---table with items

SET NOCOUNT ON;
DECLARE @items TABLE (ITEM_ID INT, ITEM_NAME VARCHAR(10))
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 10,'ITEM 1'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 11,'ITEM 2'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 12,'ITEM 3'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 13,'ITEM 4'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 14,'ITEM 5'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 15,'ITEM 6'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 16,'ITEM 7'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 17,'ITEM 8'
SELECT * FROM @items

-- table with categories

SET NOCOUNT ON;
DECLARE @categories TABLE (CAT_ID INT, CAT_NAME VARCHAR(10))
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 100,'WHITE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 101,'BLACK'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 102,'BLUE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 103,'GREEN'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 104,'YELLOW'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 105,'CIRCLE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 106,'SQUARE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 107,'TRIANGLE'
SELECT * FROM @categories

--table where categories are assigned to master categories

SET NOCOUNT ON;
DECLARE @master_categories TABLE (MASTERCAT_ID INT, CAT_ID INT)
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,100
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,101
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,102
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,103
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,104
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,105
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,106
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,107
SELECT * FROM @master_categories

-- items-categories assignment table

SET NOCOUNT ON;
DECLARE @item_categories TABLE (CAT_ID INT, ITEM_ID INT)
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,10
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,10
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,11
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,11

[code]....

So now I need to query the table @t4 in and to determine the items that are assigned to category 'WHITE' in master category 1 and to 'CIRCLE' in master category 2.The important thing is to return items that are assigned solely to 'WHITE' in master cat 1 and solely to 'CIRCLE' in master cat 2.In the above example it would be only the ITEM 1 (id=10) that is returned:

1. ITEM 2 (id=11) is not returned because it has the assignment to category 'SQUARE' in master cat 2 additionally

2. ITEM 3 (id=12) is not returned because it has the assignment to category 'BLACK' in master cat 1 additionally

3. ITEM 4 (id=13) is not returned as it does not have assignment to category 'CIRCLE' in master cat 2 but only to 'WHITE' in master cat 1

3. ITEM 5 (id=14) is not returned as it does not have assignment to category 'WHITE' in master cat 1 but only to 'CIRCLE' in master cat 2

View 3 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

SQL Server 2012 :: Data Compare To Identify Change

Mar 3, 2015

I am in process to develop TSql code to identify change in data.

I read about Binary_checksum and hashbyte. Some people say hashbyte is better than binay_checksum as chances of collision are less.

But if we may consider following, chances exist in hashbyte too. My question is what is the best way to compare data to identify change (I can't configure CDC) ?

select HASHBYTES('SHA','121'+'34'), HASHBYTES('SHA','12'+'134'),BINARY_CHECKSUM('121','34'),BINARY_CHECKSUM('12','134');

View 2 Replies View Related

SQL Server 2012 :: Compare A Table With Physical Path

Mar 31, 2015

I have a table with two columns

id | filepath
--------------------------------------------------
1| D:Doc filesThe BestHHT.JPG
2| D:Doc filesThe Bestsealed_pack.txt
3| D:Doc filesThe Bestlsbom.JPG
4| D:Doc filesThe Bestmoc.png
5| D:Doc filesThe Beststock.txt
6| D:Doc filesThe Bestdepot.JPG

And in a physical system there are more files than the table.

D:Doc filesThe BestHHT.JPG
D:Doc filesThe Bestsealed_pack.txt
D:Doc filesThe BestJKSlsbom.JPG
D:Doc filesThe Bestmoc.png
D:Doc filesThe Beststock.txt
D:Doc filesThe BestGDNdepot.JPG

D:Doc filesThe BestCASA.JPG
D:Doc filesThe BestSO.txt
D:Doc filesThe BestBA.JPG

I want to compare the filepath column in table with physical drive files and get the details of files which in table and not in physical and viceversa...

View 3 Replies View Related

SQL Server 2012 :: Compare Characters Between 2 Strings And Eliminate Similarities

Oct 13, 2015

I am trying to write a function to compare the characters between 2 strings and eliminate the similarities to be able to return at the end the number of differences between them.

Having in mind i need the bigger number of differences to be returned also if a character is repeated in one of the 2 words it will be eliminated once because it exist only one time in other string.

I will give an example below to be more clear

--Start
declare @string1 as varchar(50)='imos'
declare @string2 as varchar(50)='nasos';
WITH n (n) AS (
SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)

[Code] ....

The differences in first string from second one are 2 (i,m) while the differences in second string from first one are 3(nas).
So the function should return 3 in previous example.

View 4 Replies View Related

SQL Server 2008 :: How To Compare The Date Column

Mar 30, 2015

I need to compare the date column. The date column is 2012-01-22 in this format.

So I need to convert getdate() into this format yyyy-mm-dd.

What is the style number I have to use?

I used the link [URL] but I didn't see this format.

View 5 Replies View Related

SQL Server 2012 :: How To Compare List Of Numbers Kind Of Like Lottery Results

Feb 5, 2015

Say you have a table that has records with numbers sort of like lottery winning numbers, say:

TableWinners
num1, num2, num3, num4, num5, num6
33 52 47 23 17 28
... more records with similar structure.

Then you have another table with chosen numbers, same structure as above, TableGuesses.

How could you do the following comparisons between TableGuesses and TableWinners:

1. Compare a single record in TableGuesses to a single record in TableWinners to get a count of the number of numbers that match (kind of a typical lottery type of thing).

2. Compare a single record in TableGuessess to ALL records in TableWinners to see which record in TableWinners is the closest match to the selected record in TableGuesses.

View 8 Replies View Related

SQL Server 2008 :: Join Two Tables - Compare Amounts (Rent)

Jun 20, 2015

Refer to my query thread on msdn SQL site, how I can achieve the result.

[URL] .....

View 0 Replies View Related

SQL 2012 :: Single Query To Update Same Column Name In Different Tables With Where Condition?

Sep 25, 2014

I have two tables table1 and table2 and having a common column "col1"

When i ran the following query

UPDATE table1, table2 SET col1=FALSE WHERE id = 1;

getting the following error

Error Code: 1052 Column 'col1' in field list is ambiguous

id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.

how to achieve this in single query?

View 4 Replies View Related

SQL 2012 :: Memory Optimized Tables And Updatable Column Stored Index

Aug 26, 2014

We are planning to upgrade. We are using Sql 2008R2 now. Which is the better option migrating to SQL 2012 or migrating to 2014?I am thinking 2014 has memory optimized tables and updatable column stored index. So it is better option.

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

SQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables

Oct 7, 2015

I am using the following select statement to get the row count from SQL linked server table.

SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')

MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?

View 1 Replies View Related

SQL Server 2012 :: Subtract / Exclude Value Items From A Column And Add It To Another Column In Same Table

May 26, 2014

I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.

View 1 Replies View Related

SQL Server 2012 :: Format Value Of A Column And Insert It Into Another Column Of The Same Table?

Sep 18, 2014

A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.

I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.

The text before ;# (35106) should be inserted in to another column of the same table.

Below is the table structure:

create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)

View 2 Replies View Related

SQL Server 2012 :: Compare Two Table Data And Insert Changed Field To Third Table

Aug 12, 2014

I want Compare two Table data and insert changed field to the third table ...

View 9 Replies View Related

SQL Server 2012 :: Group Column Based On Another Column

Jul 11, 2014

I have Table Like this

t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Update A Column Using Value Of Another Column

Sep 9, 2015

I have a student table like this studentid, schoolID, previousschoolid, gradelevel.

I would like to load this table every day from student system.

During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.

My question in my merge statement something like below

Merge into student st
using (select * from InputStudent ins)
on st.id=ins.studentid

When matched then update

set st.schoolid=ins.schoolid
, st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid
else st.previouschoolid
end
, st.grade_level=ins.grade_level
;

My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?

View 4 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







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