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
ADVERTISEMENT
Apr 6, 2008
Hi all
I have a table with one nText Field , some times i need to realize whether two records have the same value in that nText Field or not , so how can i compare
these two records based on their ntext Fields.? in the other words i'm looking for some thing like the follows:
declare @a nTextdeclare @b nText
select @a=MynTextField from MyTable where PK=18select @b=MynTextField from MyTable where PK=21
if @a=@b print 'Records Are the same'else print 'Record are not the same'
Thanks in advance.
Regards.
View 3 Replies
View Related
Sep 1, 2000
I need to build a cube for membership over time. Active membership is the gap from the record's CreationDate to SuspendedDate, as compared to a reference date (today's active members have CreationDates before today, and SuspendedDates > today, or NULL).
Any idea how to build a cube so I can count membership over a Time dimension? The only approach I've been able to come up with is to create a table with a record for each active member on each date (brute force).
AtDhVaAnNkCsE!
View 1 Replies
View Related
Jan 17, 2002
I would like to compare 2 fields.. One of which is generated by the application upon insert. The other which is today's date.
In other words I want all records with today's date.
Problem is I get no results because I'm also apparently comparing the time.
Do I use a CONVERT function on my select & GETDATE outputs?
Thanks,
Kelly
View 2 Replies
View Related
May 22, 2002
I have a table with a DateTime field that has Dates in the format of mm/dd/yyyy I want to do a simple count on the table for the previous day but keep getting 0 for the results. Here is the script I've been trying.
SELECT COUNT(*)
FROM mytbl
WHERE Collected_Date = DateAdd (dd, -1, GetDate())
It appears to me that the time portion of the DateAdd function keeps the matches that I am looking for from happening. What am I missing or am I going about this all wrong?
Thanks for any help
View 1 Replies
View Related
Nov 29, 2014
I have two name columns in my table, NAME1 & NAME2 that I want to compare to see if they match. Only problem is that the order of the first, last, middle name can be either same or different between the two fields.
For example
NAME1 = JAY JOHN SMITH
NAME2 = JOHN SMITH JAY or SMITH JOHN JAY
Is there a way to somehow reorder these fields and then compare using SQL?
View 2 Replies
View Related
Jan 28, 2008
I want to do something like this.
Code SnippetSELECT * FROM [scholarship] WHERE ([schlrPrefix] + ' ' + [schlrName] AS ScholarshipName) LIKE 'Ann Buttler'
How do I something like this where it combines two fields and then use that to compare to a parameter?
View 6 Replies
View Related
Apr 9, 2008
My situation is this: I have a requirement table,in the requirement table i have a field which is allow_multiple,if it allows multiple the value is 1 and 0 if it will not allow and also I have an insert storedprocedure for inserting licenses which will be added in the Staffl table.When i insert a new license for the staff,it should check in the requirement table if the new inserted lincense does have 0 or 1 value in the allow multiple field.If it has a value of 0 the new license should not be inserted.What would be the good way to compare the existing licenses of the staff and the newluy added?
Funnyfrog
View 1 Replies
View Related
Jul 20, 2005
I have the following table structure.group1 group2 group1_result group2_result'One' 'Two' 3 2'One' 'Two' 3 1'One' 'Two' 2 5'One' 'Two' 4 1'One' 'Two' 0 5I need to sum up the number of times 'One' is greater than 'Two', andvice-versa. For example, the result I would like to achieve is asfollows.group1 group2 group1_total group2_total'One' 'Two' 3 2I'm using the following SQL statement, but I get 5 rows returned,giving me a '1' or '0' for each row.select group1, group2,sum(CASE WHEN group1_result > group2_result THEN 1 ELSE 0 END),sum(CASE WHEN group2_result > group12_result THEN 1 ELSE 0 END)FROM table1GROUP BY group1, group2Any help would be greatly appreciated.
View 1 Replies
View Related
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 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
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
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
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
Sep 29, 2015
I need to compare records between two tables. There is no ID in the tables to do a simple join between them. So, what I'm looking for is: get the first record from table1 and read all record from table2 and give me back the most similar record. The String Distance is a predefined function.
Select a.table1
,b.table2
from table1 a, table2 b
where StringDistance (''a.table1,'b.table2') >90
View 4 Replies
View Related