Problem With Comparing 2 Text Columns In 2 Tables

Jul 20, 2005

Hi,
I have 2 tables and each has a text column. When i compared the 2
columns with the LIKE operator i found something strange.

create table test1( var1 text )
create table test2( var1 text )

insert into test1 values ( '-- [ CustomerType = 1 ]' )
insert into test2 values ( '-- [ CustomerType = 1 ]' )

select * from test1, test2
where test1.var1 like test2.var1

The last query surprisingly did not return any results. However when i
took of the '[' in the text data and had something like this

insert into test1 values ( '-- CustomerType = 1 ]' )
insert into test2 values ( '-- CustomerType = 1 ]' )

the query returned expected results, showing one row.

any helpful would be great.

Regards,
Arun Prakash. B

View 1 Replies


ADVERTISEMENT

Comparing 2 Similar Columns In Different Tables

Mar 5, 2014

I am trying to write an SQL command for my crystal report. I need to compare the same column in 3different tables & get the data from each table for only the matching data.. I understand I need to create a temporary table, get the data into it & then work around.. I am quite new to SQL.

Eg: Considering one customer account
Table 1
Cust.No Name Amt_Counter AmtPaid
123.456 sam 0 0
123.456 sam 1 50

Table 2
Cust.No Name Freq_Counter Frequency
123.456 sam 1 0
123.456 sam 2 15

[code]....

View 3 Replies View Related

Transact SQL :: Comparing Two Tables By NOT NULL Cells In Different Columns

Oct 22, 2015

We have two tables:

Table1
Servers|Databases|Users|Names
Server1|Database1|User1|Name1
Server2|Database2|User2|Name2
Server3|Database3|User3|Name3
Server4|Database4|User4|Name4
Server5|Database5|User5|Name5

Table2
Servers|Databases|Users|Names
NULL    |Database1|NULL |Name1
NULL    |NULL        |User1|Name2
NULL    |NULL        |NULL |Name3
Server5|Database5|NULL |NULL

I need to check Table1 by Table2 only on NOT NULL cells and if all of them in the row match do not return that row as the result. In this case it will be:

Results:
Servers|Databases |Users |Names
Server2|Database2|User2|Name2
Server4|Database4|User4|Name4

I used query like this:

SELECT a.Server, a.Databases, a.Users, a.Names FROM Table1
EXCEPT
SELECT ISNULL(b.Server,c.Server), ISNULL(b.Databases,c.Databases), ISNULL(b.Users,c.Users), ISNULL(b.Names,c.Names) FROM Table2 AS a, Table1 AS c

But for many rows (like 100 000) it takes ages to get results, any better way to work on this? 

View 2 Replies View Related

Magic Tables With Text Columns

Oct 6, 2004

Hi folks,

I need to execute some kind of

select a,b,c into #inserted from inserted

a = int
b = varchar(50)
c = text

Easy so far. But I what I need is to find out whether the audited table contains text(or nText / image) columns or not. If it contains any text columns the statement should automatically be changed to

select a,b into #inserted from inserted

Remember that the trigger must be usable for any table in the database without manually entering the tables structure or the column names.

Best would be fetching the structure fropm the system views (INFORMATIONSCHEMES)

Any suggestions ?

Thanks a lot!

Stefan

View 1 Replies View Related

How To Get List (text) Of All Tables And Columns?

Jul 20, 2005

Is there a way using MS SQL Server and Enterprise Manager to get a textdocument (or perhaps even a Word document) listing all table names,column names, etc of a database?--Sugapablo------------------------------------http://www.sugapablo.com <--musichttp://www.sugapablo.net <--personal

View 2 Replies View Related

Full-Text Search On Multiple Tables && Columns

Sep 19, 2007

Hi,
I have tried this code from http://jtkane.spaces.live.com/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry for full-text search on multiple tables & columns.
Here's my code:
SELECT * from [tStaffDir] AS e, [tStaffDir_PrevEmp] t,CONTAINSTABLE([tStaffDir], *, @Name) as AwhereA.[KEY] = e.[ID] andt.[ID] = e.[ID]
I have FT the both the tables above and I am able to get results from the  [tStaffDir] table but not the [tStaffDir_PrevEmp] table.The [tStaffDir_PrevEmp] table does have a column (which is [ID]) that is indexed, unique and non-Nullable.Please advise what I should do and look out for.
Many Thanks.

View 2 Replies View Related

Comparing Text In WHERE

Sep 30, 2006

Hi everyone,

I am compare to text fields like this:

CREATE PROCEDURE dbo.PCAttByVal
(
@Value text
)
AS
BEGIN

SET NOCOUNT ON
SELECT ID FROM PCAtt WHERE Value=@Value

END

But the environment (I use VS2005) says that the data type is incompatible with the equal operator. Then I tried:

CREATE PROCEDURE dbo.PCAttByVal
(
@Value text
)
AS
BEGIN

SET NOCOUNT ON
SELECT ID FROM PCAtt WHERE Value IN '('+@Value+')'

END

But nada (nothing). Any ideas? I can't change the data type since this field will hold values of different sizes.

Thank you for your input in advance!

View 1 Replies View Related

Comparing Columns In Excel

Jan 2, 2008

I have two spreadsheets in excell. There are 3000 numbers on one sheet, and there are 900 numbers on the other sheet. I want to know which numbers overlap and either somehow flag the overlaping numbers on both sheets or create a third sheet that just contains the numbers that exist in both. Is there some straightforward set of function comands in excel to do this?

Thanks for any help.

View 1 Replies View Related

Comparing Text DataType???

Sep 9, 2005

I'm trying to do a simple search for any record that has an empty 'dsc' field. There isn't a NULL character in there, just a SPACE character. The 'dsc' field is a description field so it's datatype is TEXT.

Code:


SELECT id FROM leads WHERE dsc<>''


Normally I can do a VARCHAR(50) like that and get the results I'm looking for. When I do this on a TEXT datatype I get this error:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I I tried a couple of things, but it's not getting me the correct results.

Code:


SELECT id FROM leads WHERE dsc NOT LIKE '% %'
SELECT id FROM leads WHERE dsc NOT LIKE '% '
SELECT id FROM leads WHERE dsc NOT LIKE ' %'


Please shed some light on this.. thanks!

Edited @ 12:08 PM
After some research I found an unconventional way of beating the system. If you have a more conventional way I would still like to see it. A quick fix is using DATALENGTH() function to compare the data. Basically if there are any characters besides NULL and space then the data length will be more than zero.

Code:


SELECT id FROM leads WHERE DATALENGTH(dsc)=0


http://msdn.microsoft.com/library/d..._da-db_4ep4.asp

View 3 Replies View Related

Comparing 2 Columns Containing Null Values

Sep 28, 2006

Hi All.

     I'm having some issues with what seems to be a simple update statement but is giving me grief when one or both of the columns I'm comparing are null. My statement (simplified) is as follows:-

 

UPDATE
 TAB_A
SET
 TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
 TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
 TADS.DBO.UNCLBRAMDEPT TAB_A
ON
 TAB_B.BRANCODE = TAB_A.BRANCODE
AND
 TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
 TAB_B.TRADCODE <> TAB_A.TRADCODE
)

 

If either of the TRADCODE fields (or both) are null then the comparison fails to return the row to update. I've tried setting the ANSI_NULLS setting to off, this has no effect, presumably because it will only work when comparing a column to a variable or evaluating if the column is null itself.

I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.

 

I'd be grateful for any pointers!

 

Thanks in advance

 

View 4 Replies View Related

T-SQL (SS2K8) :: Comparing Columns In Unpivot Table

Nov 21, 2014

SET NOCOUNT ON;

IF OBJECT_ID('dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;

CREATE TABLE dbo.TBL_SAMPLE_DATA
(
ROW_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,Airline VARCHAR(50) NOT NULL

[Code] ....

I have to compare the Aircraft1 and Aircraft1_unsub based on condition as below:

[case
when Aircraft1='N' or Aircraft1_unsub='Y' then 0
else 1
end ]

Based on the comparision output i have to update the main table with the outputvalue for each Aircraft based on the Airline

update t set t.Aircraft1=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline

update t set t.Aircraft2=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline

View 7 Replies View Related

Comparing Tables..

May 3, 2001

Hi ,
I have three tables T1 , T2 AND T3. T3 is having fields as a combination of T1 and T2 fields.How can I compare T1 and T2 field values with T3 FIELD VALUES.

View 4 Replies View Related

Sql For Comparing Two Tables

Aug 2, 2004

Hi everyone,

I have 2 seperate databases and I need to check for rows that are different from each other in a table.

I used access to link the tables in a database and am using queries to check the tables. However, I am having trouble formulating the SQL. What I want to do is not just check for the ID field to see if it exists, but to make sure the whole row exists. How can I form an SQL statement for this?

I tried something like:

Select * from table1 where Column1 NOT IN (Select Column1 from Table2) AND Column2 NOT IN (Select Column2 from Table2). However, I do not think this is correct. I want to make sure that the rows are compared, not individual values.

Any help would be appreciated.

Thanks,
Pankaj

View 5 Replies View Related

Comparing Two Tables

Oct 29, 2004

IS there an easy way to compare two tables in sqlserver? I only need to display items that are different between table a and b.

thanks in advance for your help!

View 9 Replies View Related

Comparing Two Tables.

Aug 24, 2007

I'm having a bit of a brain melt.

I've got two identical tables (except for the names) and I need to run a query that outputs the rows that aren't in the primary table.

To clarify table one has 38,450 records and table two has 30,703. I need to output the records that are in table one but not table two.

Everything I've tried keeps returning the records that are in it. They have a ID as primary key and a userid field which is what I want to be able to list.

Any ideas?

Thanks.

View 12 Replies View Related

Comparing Tables

Dec 24, 2007

How can I select records that have changed or are new when comparing a previous copy of a table with the live version of the table? There is no datetime stamp in these tables. Many thanks in advance.

View 3 Replies View Related

Comparing Tables Again

Dec 28, 2007

I need to compare two tables and update the records that have changed.
The following code works great for that:
SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
FROM
SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...
FROM A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...
FROM B
) tmp
GROUP BY ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID

However, I also need to compare the two tables and see if there are any new records or deleted records. What would those queries look like? Can they all somehow be combined into one query?

View 5 Replies View Related

PLEASE HELP: Comparing 2 Tables

Mar 13, 2008

Is it possible to compare a table in an sql database with a database connected through odbc - in my case it's a dbf file.

If so, any tips? Please help as im stuck on this for the past week.

thanks,
Anthony.

View 1 Replies View Related

How To Get A Specialdata When Comparing 2 Tables

Oct 17, 2004

hi there,

im currently doin my proj on asp.net, so i need some help on this.

i need to get a special price which muz be constant throughout all the pages which requires to show the product and the special price.

i haf 2 table which are products and promotion. and in products there is a column name price,productid, etc and in promotion, it displays the productid of the product which is having promotion and also a column name specialPrice.

i need to get the special price from the promotion tables.

so how do i go about retrieving wat i need for my databind specialprice.

ive tried using join, but there is cartesian, and i tried to use in, but its not constant throuh all the pages.

any help would greatly be appreciated.

beginner,
17|17

View 1 Replies View Related

Comparing Two Tables!..URGENT!!

Aug 7, 2000

Is there a way to compare two tables?I need to compare two table which are suppossed to be same in all respects including the data.any help?
Thanks.

View 2 Replies View Related

Comparing Fields In Tables

Aug 4, 2005

I am working with the article that MAK wrote on SecurityLogs http://www.databasejournal.com/features/mssql/article.php/3515886

I have completed this, but I have made some changes to the database (for normalization to 3NF purposes). I now have problems with a query.

I am trying to "Insert a new record in a table if it does not already exist in the table". To try to clarify I perform the following query:

INSERT INTO Tmp_Event
SELECT DISTINCT EventID, EventType, EventTypeName from Tmp

Which gives me the Tmp_Event table consisting of EventID's etc. (no duplicates). What I then want to do, is compare the 'Tmp_event' table and an already existing 'Event' table. These two tables are in fact identical. I would like to insert any records from 'Tmp_Event' into 'Event' if they do not already exist in 'Event'.

This query gives me all records that do not exist in 'Event'

SELECT EventID, EventType, EventTYpeName from Tmp_Event
WHERE EventID NOT IN (SELECT EventID from Event)

How can I change this query into performing an INSERT INTO Event as well?

Hope this makes sense :)

-Silia

View 2 Replies View Related

Comparing Tables In SQL Server 2k

Dec 29, 2004

I have two tables that share (supposedly) 2 fields (PartID and RaceID) and those two tables should be identical as far as those two fields are concerned. That is, there should be the same number of rows in both tables and if listed in the same sort order in reference to these two fields, they should be identical. The problem is, they are not. There are in excess of 3000 records in each field and I need to write a query that will allow me to compare them row-by-row.

I am using sql server 2000 and I am (kind of)familiar with the SQL Query Analyzer. What I really need to know is how to write the select statement that will allow me to compare the two tables line-by-line to find the discrepancies or, better yet, simply show the discrepancies so I can focus on them.

Thanks!

View 13 Replies View Related

Comparing Data Between Two Tables...

Jul 23, 2005

I would like to compare data across two tables. I have partinformation in a table. I get a new set of information periodically.I would like to compare my new info to my old info. I recognize thatdoing a compare of every attribute of every part will take FOREVER. Isthere some way I can do a "diff" based on the columns that I careabout?Thanks!--gloria

View 2 Replies View Related

Comparing Two Tables And Spitting Out An XLS

Sep 3, 2006

Hi,I'm trying to figure out a way to compare two tables, table one has moreentries than table two, I want SQL to compare table one to table two andspit out and XLS of the enries that exist in table one, but not in tabletwo.So far I can't even get my query right...hehselect * from table1 aleft join tabl2 b on a.column=b.columnwhere a.column exists not b.columnam I missing an "in" in the select portion on my query?thanks alot for any help.

View 1 Replies View Related

Comparing Data In 2 Tables

Jan 15, 2008



I need to compare two tables in two different databases and get data that is only in one table.

Table 1 in DB1 and Table A in DB_A

I need all distinct IDs in Table 1 in DB1 that are NOT in Table A in DB_A
and copy those IDs into Table A.

Is the sql below correct in obtaining data?
Can I add the copy part also to this?



select ID

from DB1.dbo.Table1 aaa

where not exists ( select empID


from DB_A.dbo.TableA bbb
where aaa.ID = bbb.empID)

View 5 Replies View Related

Comparing Two Tables In Two Different Databases

Jul 19, 2007

I want to compare the records in a table on my live database server with the same table that is my test database server. How can I do this?

Note that both databases are mirrors of each other but contain slightly different data and are on different servers.

View 4 Replies View Related

Comparing Large Tables

Oct 19, 2007



I've successfully created SSIS packages where I compare two tables in different databases on different servers. However, this is good enough to compare hundreds of thousands of records quickly. The process becomes a huge performance problem when trying to compare table differences when I'm looking at tables that each contain tens of millions of records.


One database is on a SQL 2005 box and the other DB is SQL 7.0 so the lookup component fails for this type of SQL Server. I've been implementing merge joins and conditional components to do my standard table comparisons.

Is there another way to implement this process or maybe partition it somehow to take pieces of the table at a time and compare them? I'm open to ideas.

View 11 Replies View Related

Comparing Two Tables And Updating As Needed.

Jan 5, 2004

I have two tables in seperate dbs that I need to match up users and update the passwords in the second db table. I know how to match up the data and I'm sure I can do this using a cursor but I was wondering if there is a way I can do this with an update statement. Any suggestions(including telling me I'm stuck using a cursor) would be great.

Thanks ahead of time


EDIT: Nevermind, figured it out :D Thanks anyway.

View 1 Replies View Related

Comparing Data In Tables/views.

May 13, 2008

I am looking for an efficient mechanism to compare data between 2 tables/views.

Rationale:
I use a proprietary tool to data transfer between 2 databases. The tool itself uses Microsoft SSIS (integration service) to transfer data. I want to make sure that the data is transfered properly. Both the source and target database are not live database. The source and target database are in seperate servers.

View 2 Replies View Related

Comparing Data And Integrity Between Two Tables

Jun 4, 2008

Hi everyone,

I have recently converted my DTS packages to SSIS and deployed them to the new server. I have the 2000 and 2005 server running concurrently, all that is left for me to do is compare the the tables generated by the DTS and SSIS packages to see if they are the same.


How do I go about comparing the tables, which are from two different servers using SQL server 2005?



Thank you inadvance:)

Comparing data and integrity between two tables

View 2 Replies View Related

Comparing Two Tables Or Views Easily

Jan 15, 2003

Based on the discussion at:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23038

and BSmith's idea (no relation), I came up with this silly stored proc to compare any two tables or views. Supply the table names and the columns you wish to compare and have fun!

The key to the GROUP BY approach is that it handles NULLS quite well which JOINS have trouble with.


CREATE PROCEDURE CompareTables(@table1 varchar(100),
@table2 Varchar(100), @T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS

-- Table1, Table2 are the tables or views to compare.
-- T1ColumnList is the list of columns to compare, from table1.
-- Just list them comma-separated, like in a GROUP BY clause.
-- If T2ColumnList is not specified, it is assumed to be the same
-- as T1ColumnList. Otherwise, list the columns of Table2 in
-- the same order as the columns in table1 that you wish to compare.
--
-- The result is all records from either table that do NOT match
-- the other table, along with which table the record is from.

declare @SQL varchar(8000);

IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList

set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +
@t2ColumnList + ' FROM ' + @Table2

set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'

exec ( @SQL)


- Jeff

View 13 Replies View Related

Update/Delete/Add Comparing 2 Tables

Mar 12, 2008

Hi,

Im pretty new to SQL Server, but was able to create a dsn conenction to our dbf database, and then use sql script to import the table into sql server using this script below:

use cameodata;

/* Drop stock Table */
IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'stock'
AND TABLE_SCHEMA = 'dbo'
AND TABLE_TYPE = 'BASE TABLE'
)
BEGIN
DROP TABLE stock
END

/* Import stock Table */
select * into stock
from openrowset ('msdasql','dsn=stock',
'select * from stock')


So basically everytime i run this script, it deletes the table in sql server and then imports the latest copy.

The problem is that this table is huge and it takes a while. What Id rather have is a script that i could run that would compare the dsn connected table with the table currently on sql server and if there are any updates then update sql server, if there are items on the sql server that are not the the latest copy, then delete that record on the sql server table and also if there are new items on the lastest copy then add the new record to the sql server table.

Is this possible?

thanks,
Anthony.

View 2 Replies View Related

Comparing Tables In Different Servers (EXCEPT/INTERSECT)

Nov 14, 2007

Hi people!!!

First I'll introduce my situation.

I have the folowing tables:

1.- Table "Codes", DataBase "COD", server 1001

2.- Table "Codes", DataBase "COD", server 1002

Both servers run SQL Server 2000 Edition.

What i need to perform is a check that compares the data stored in both tables in order to know if there is any difference between them. Of course, the structure of both tables are the same.

I use the SP sp_addlinkedserver to link the servers, but the problem is that EXCEPT and INTERSECT didn't work.

Anyone can help me?

Thanks,

Bob

P.S.: Please!!!!!!!!!

View 6 Replies View Related







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