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