InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 22:52:12 GMT20http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxThe SQL mentioned in this post many years ago refers to a way to compare the DATA in two different tables. It would tell you that Field7 has changed and give the old and new values.Fri, 16 Mar 2012 08:17:03 GMTmikeg13http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxCheck the following blog post: [url=http://sqlwithmanoj.wordpress.com/2011/05/16/database-schema-diff/]http://sqlwithmanoj.wordpress.com/2011/05/16/database-schema-diff/[/url]Here I've created a script to list out differences in schema of two different databases.You can add filter WHERE clause for tables, or tweak the script according to your needs.Fri, 16 Mar 2012 00:16:24 GMTmanub22http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxI am not sure I understand. Can you give simple example of what tables you have and what you want?Thu, 15 Mar 2012 07:29:32 GMTmikeg13http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxI Have two tables, first table as to be cross checked the feilds like EMP name & Emp no into second table & include the same in first table.RegardsRajThu, 15 Mar 2012 02:11:12 GMTuprajkumarhttp://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxassalam o alaikumnow i have tried to explain my problem clearly in a seperate thread. please if you can spare some time to help me out. the link of the thread ishttp://www.sqlservercentral.com/Forums/Topic945934-149-1.aspxRegards,KamranThu, 01 Jul 2010 00:16:56 GMTmohammad.kamranpkhttp://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxassalam o alaikumnow i have tried to explain my problem clearly in a seperate thread. please if you can spare some time to help me out. the link of the thread ishttp://www.sqlservercentral.com/Forums/Topic945934-149-1.aspxRegards,KamranThu, 01 Jul 2010 00:15:41 GMTmohammad.kamranpkhttp://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxI would agree about creating a new thread. However, if your problem is as simple as your question, then the following SQL, or something like it, should suffice to get your answer. Note that the SQL is based on your requirement that RecordID and StudentID must match, whereas your example data seems to rely simply on RecordID matching. [code="sql"]insert into Table_C (Sim_RecordID, Sim_StudentID, Sim_Dept, Sim_BookID)select 1, 1 , case when Table_A.Dept = Table_B.Dept then 1 else 0 end , case when Table_A.BookID = Table_B.BookID then 1 else 0 end from Table_A join Table_B on Table_A.RecordID = Table_B.RecordID and Table_A.StudentID = Table_B.StudentID[/code]If your problem requires a more dynamic solution for a table with an unknown structure, then perhaps you could use the technique mentioned in this thread to create an audit of what changed and produce your Table_C from that. Good luck.Wed, 30 Jun 2010 11:45:38 GMTmikeg13http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxMohammed, You posted your question on a thread that is 2 years old. I suggest that you open a new thread with your question.Wed, 30 Jun 2010 09:13:39 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxI have two tables which contain the same records but those records are entered by different users and i want to calculate similairty between same records(by every comapring every single field) entered by different users, and if a field in one table is similar to corresponding field in the second table then i want to store 1 in a column in third table (Table_C). For Example I have two tables Table_A and Table_B as below:Table_A----------------------RecordID StudentID Dept BookID1 123 CS 4562 123 CS 3453 223 TE 190 Table_B----------------------RecordID StudentID Dept BookID1 123 CS 4562 223 TE 3453 223 TE 190and i have another table Table_C in which is store the similarity between the similar fields in Table_A and Table_B. The Sturcutre of the table is as follows:Table_C----------------------Sim_RecordID Sim_StudentID Sim_Dept SimBookID1 1 1 11 0 0 11 1 1 1Note: I want to comapre only those records in Table_A and Table_B where RecordID and StudentID are same in both tables. i.e. [b]i want a query or simple stored procedure to compare all columns of Table_A with corresponding columns of Table_B where Table_A.RecorID = Table_B.RecordID and Table_A.StudentID = Table_B.StudentID and Store 1 if the fields are similar otherwise store 0 in Table_C in the corresponding field[/b]. I hope i have clearly defined my probelm. any help would be appreciated.Wed, 30 Jun 2010 04:47:19 GMTmohammad.kamranpkhttp://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxKudos to whomever allowed me to subscribe to a topic and get an email. :-)Tue, 15 Jun 2010 20:18:02 GMTmikeg13http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxKudos on sticking with a thread that is 2 years old!Tue, 15 Jun 2010 15:54:30 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxIt looks like I forgot to add the dynamic SQL that puts all these pieces together.[code="sql"] -- Piece together everything into a single dynamic INSERT statement SET @SQL = 'INSERT INTO Stage.DATA_CHANGES (Tablename, Action, Key_Fields, Key_Values, Changes) SELECT * FROM (SELECT ''' + @p_changes_tablename + ''' tname, ''UPDATE'' action, ''' + @Key_Fields + ''' kfields, ' + @Key_Values + ' kvalues, ' + @Changes + ' Changes ' + @From + ' WHERE ' + @Join_Keys + ') alias1 WHERE len(replace(changes,''~'','''')) > 0' -- Run the dynamic SQL to populate the staging table EXECUTE (@SQL )[/code]As for the "Tally" table, that is just a table of numbers. The idea for this can be found by searching this forum for "tally table" or try [url=http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/][/url]Hopefully this helps. I would be interested to know if you, or anyone else, gets any use out of this. Good luck.Tue, 15 Jun 2010 15:27:21 GMTmikeg13http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxHi mikeg13. I was going through your sample as I need to implement something similar. I was wondering if you could answer a couple of questions.1. I could not locate anywhere in the code sample where you actually inserted into the staging table. Did I miss something?2. Where does the Admin.Tally table get created and populated?Tue, 15 Jun 2010 15:00:05 GMTviet074http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxThe UNION ALL and EXCEPT method will work and is easier to understand, but it is too time consuming for me. If you use 'select *' with the EXCEPT, you will get the different records, but have no idea what columns changed. However, you could loop through the syscolumns table and create dynamic sql to use this method for each column. Like I said, this is easier to understand and certainly easier and quicker to code. Using this method requires reading the tables once for every column in the table. My method checks everything in one single pass. With some tables with hundreds of columns, I prefer my method (but am always looking for a quicker and easier method).Sat, 07 Jun 2008 19:07:41 GMTmikeg13http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxI was given a similar task a few months ago and my solution was along the lines of the suggestion of glen.greaves -identify the records with mismatches by usingselect table aunion all select table bexceptselect table bunion allselect table aI had a function which took two fields and the name of the field and returned a value if the fields didn't matchfield by field of the records that didn't match I looked for the field(s) with mismatchesoh and the first step was to limit the rows in table a and table b to records that matched on key fieldsFri, 06 Jun 2008 07:04:45 GMTSeggerman-675349http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxJust for completeness, here is the revised code for parsing out the old ane new values into the final table...[code] IF UPPER(@p_details) = 'YES' INSERT INTO Admin.Data_Changes (Table_name, Action, Key_fields, Key_values, Fieldname, Old_Value, New_Value, DW_load_dttm) SELECT Table_name, Action, Key_fields, Key_values, SUBSTRING('~'+ Changes +'~',N+1,CHARINDEX('=','~'+ Changes +'~',N+1)-N-1) AS Fieldname, SUBSTRING(Changes,CHARINDEX('[',Changes,N+1)+1,( CHARINDEX('^^',Changes,N+1)-CHARINDEX('[',Changes,N+1)-1) ) AS Old_Value, SUBSTRING(Changes,CHARINDEX('^^',Changes,N+1)+2,( CHARINDEX(']',Changes,N+1)-CHARINDEX('^^',Changes,N+1)-2) ) AS New_Value, getdate() FROM Admin.Tally a CROSS JOIN Stage.Data_Changes b WHERE a.N < LEN('~'+ Changes +'~') AND SUBSTRING('~'+ Changes +'~',N,1) = '~' AND SUBSTRING('~'+ Changes +'~',N+1,1) <> '~' AND table_name = @Table1 ELSE INSERT INTO Admin.Data_Changes (Table_name, Action, Key_fields, Key_values, Fieldname, Old_Value, New_Value, DW_load_dttm) SELECT Table_name, Action, Key_fields, Key_values, SUBSTRING('~'+ Changes +'~',N+1,CHARINDEX('~','~'+ Changes +'~',N+1)-N-1) AS Fieldname, null AS Old_Value, null AS New_Value, getdate() FROM Admin.Tally a CROSS JOIN Stage.Data_Changes b WHERE a.N < LEN('~'+ Changes +'~') AND SUBSTRING('~'+ Changes +'~',N,1) = '~' AND SUBSTRING('~'+ Changes +'~',N+1,1) <> '~' AND table_name = @Table1 [/code]Wed, 04 Jun 2008 09:56:07 GMTmikeg13http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxOk, I could not let the lack of old and new values wait any longer. Here is the revised code that accounts for whether the old and new values should be included in the results. The @p_details parameter is passed to the procedure and if it is 'YES' then the old and new values are included. If not YES, then it just gives the name of the field that differs. Note that the values are enclosed in [], delimited by ^^ (my original -> gave me problems due to 'xml path' I think). Let me just say that creating dynamic SQL is a PITA.[code] IF UPPER(@p_details) = 'YES' SELECT @Changes = stuff( ( select '+ case when isnull(convert(varchar,A.' + c1.column_name + '),'''') = isnull(convert(varchar,B.' + c1.column_name + '),'''') then ''~'' else ''' + c1.column_name + '=[''+isnull(convert(varchar,A.' + c1.column_name + '),'''') + ''^^'' +isnull(convert(varchar,B.' + c1.column_name + '),'''') + '']~'' end ' from information_schema.columns c1 where c1.column_name not in ('LOAD_DTTM','LASTUPDDTTM','DW_LOAD_DTTM','UW_LOAD_DTTM','AGE') and c1.table_schema = c2.table_schema and c1.table_name = c2.table_name for xml path('') ) , 1,1,'') FROM information_schema.columns c2 WHERE table_schema = @Schema1 and table_name = @Table1 GROUP BY c2.table_schema, table_name [/code][code] ELSE SELECT @Changes = stuff( ( select '+ case when isnull(convert(varchar,A.' + c1.column_name + '),'''') = isnull(convert(varchar,B.' + c1.column_name + '),'''') then ''~'' else ''' + c1.column_name + '~'' end ' from information_schema.columns c1 where c1.column_name not in ('LOAD_DTTM','LASTUPDDTTM','DW_LOAD_DTTM','UW_LOAD_DTTM','AGE') and c1.table_schema = c2.table_schema and c1.table_name = c2.table_name for xml path('') ) , 1,1,'') FROM information_schema.columns c2 WHERE table_schema = @Schema1 and table_name = @Table1 GROUP BY c2.table_schema, table_name [/code]Wed, 04 Jun 2008 08:50:33 GMTmikeg13http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxLet me try to briefly explain the solution I have. I apologize in advance for the lack of supporting info, but I do not have time to pretty it up and make it generic for public consumption. Also keep in mind that I am new to SQL Server, so give some slack on poor coding technique. Any suggestions for improvements, especially for performance, are welcome. More details can be provided if desired.Ultimately I want a table, called Admin.Data_Changes, to be populated with one record for every field that was changed for the base table. It is designed for keeping the old and new values, but time has not permitted me to implement that part yet. This table is used by my users to audit changes from yesterday. So they have reports that query the Data_Changes table and report what they want.[code]CREATE TABLE [Admin].[Data_Changes] ( [table_name] [varchar] ( 256 ) ,[action] [varchar] ( 10 ) ,[key_fields] [varchar] ( 1000 ) ,[key_values] [varchar] ( 1000 ) ,[fieldname] [varchar] ( 256 ) ,[old_value] [varchar] ( 255 ) ,[new_value] [varchar] ( 255 ) ,[dw_load_dttm] [datetime]NULL)[/code]I have a stored procedure which is passed the name of the base table and the comparison table. There are some basic edit checks to insure the table structures match, the tables exist and the base table has a primary key. Then I call another stored procedure which does all the hard work and populates a staging table with one record per modified record. The staging table has the structure ...[code]CREATE TABLE [Stage].[Data_Changes] ( [Table_Name] [varchar] (256) , [Action] [varchar] (10) , [Key_Fields] [varchar] (1000) , [Key_Values] [varchar] (1000) , [Changes] [varchar] (-1)[/code]After the staging table is populated for the base table, the final table is populated. First any previous records for this table are removed and then the staging data is parsed into one record for each changed field. @Table1 is the base table.[code] -- Clear out any previous audit records DELETE FROM Admin.Data_Changes WHERE Table_Name = @Table1 -- Parse the staging records into one record for each field changed INSERT INTO Admin.Data_Changes (Table_name, Action, Key_fields, Key_values, Fieldname, DW_load_dttm) SELECT Table_name, Action, Key_fields, Key_values, SUBSTRING('~'+ Changes +'~',N+1,CHARINDEX('~','~'+ Changes +'~',N+1)-N-1) AS Fieldname, getdate() FROM Admin.Tally a CROSS JOIN Stage.Data_Changes b WHERE a.N < LEN('~'+ Changes +'~') AND SUBSTRING('~'+ Changes +'~',N,1) = '~' AND SUBSTRING('~'+ Changes +'~',N+1,1) <> '~' AND table_name = @Table1[/code]Now for the hard working stored procedure. The setup ...[code]CREATE PROCEDURE [Admin].[Data_Changes_Staging] @p_base_tablename varchar(256), @p_comp_tablename varchar(256), @p_details varchar(256)ASset nocount onBEGINDECLARE @Table1 varchar(256), @Schema1 varchar(256), @Table2 varchar(256), @Schema2 varchar(256), @Key_Fields varchar(max), @Key_Values varchar(max), @Changes varchar(max), @Where varchar(max), @From varchar(max), @SQL varchar(max), @i integer ; BEGIN TRY SELECT @Table1 = PARSENAME(@p_base_tablename, 1), @Schema1 = PARSENAME(@p_base_tablename, 2), @Table2 = PARSENAME(@p_comp_tablename, 1), @Schema2 = PARSENAME(@p_comp_tablename, 2) DELETE FROM Stage.Data_Changes WHERE Table_Name = @Table1 [/code]The final dynamic SQL will be an INSERT statement into the staging table, Stage.Data_Changes. The pieces of that INSERT statement are built dynamically from the columns and constraints system metadata tables. First, piece together the FROM clause.[code] SET @From = ' FROM ' + @p_base_tablename + ' A, ' + @p_comp_tablename + ' B '[/code]Then the table Information_Schema.Table_Constraints is used to get the columns used for the primary key. This info is used to build the WHERE clause that will join the two tables. It is also used to populate two of the fields in the table. @Key_Fields is the primary key field names strung together. @Key_Values is the values of those primary key fields, strung together. Note that the values are converted to varchar so they can be strung together.[code] SELECT @Where = stuff( ( select ' and A.' + k1.column_name + ' = B.' + k1.column_name from information_schema.key_column_usage k1 where k1.constraint_name = c.constraint_name order by ordinal_position for xml path('') ) , 1,4,' WHERE '), @Key_Fields = stuff( ( select '~' + k1.column_name from information_schema.key_column_usage k1 where k1.constraint_name = c.constraint_name order by ordinal_position for xml path('') ) , 1,1,'') ,[/code][code] @Key_Values = stuff( ( select '+ ''~'' + convert(varchar,A.' + k1.column_name + ')' from information_schema.key_column_usage k1 where k1.constraint_name = c.constraint_name order by ordinal_position for xml path('') ) , 1,8,'') FROM INFORMATION_SCHEMA.Table_Constraints c WHERE c.table_schema = @Schema1 AND c.table_name = @Table1 AND c.constraint_type = 'PRIMARY KEY' GROUP BY constraint_name[/code]The only thing left is figuring out all the changes in the fields. The table Information_Schema.Columns is used to dynamically get all the columns for the tables. For each column, a CASE statement is built. When the two columns are equal it simply concatenates the delimiter to the string and if they are not equal, then it concatenates the name of the column and the delimiter. At this time, it does not give the values from each table, but that will be added to this statement, with changes only to the string and not the logic. Note a couple things when stringing @Changes. In order to handle all data types and nulls, the columns are converted to varchar and set to '' if null. Also note that in my example, I have excluded some column names that may appear in my tables that I am not worried about changing. The load dates and times are expected to be different from yesterday and today, so no point checking those and I do not want to know if someone's age changed because today is their birthday. If the Birthdate is changed, then I will pick that up. This is completely optional for this logic to work.[code] SELECT @Changes = stuff( ( select '+ case when isnull(convert(varchar,A.' + c1.column_name + '),'''') = isnull(convert(varchar,B.' + c1.column_name + '),'''') then ''~'' else ''' + c1.column_name + '~'' end ' from information_schema.columns c1 where c1.column_name not in ('LOAD_DTTM','LASTUPDDTTM','DW_LOAD_DTTM','UW_LOAD_DTTM','AGE') and c1.table_schema = c2.table_schema and c1.table_name = c2.table_name for xml path('') ) , 1,1,'') FROM information_schema.columns c2 WHERE table_schema = @Schema1 and table_name = @Table1 GROUP BY c2.table_schema, table_name [/code]That's it! Piece all this together and clean it up and you can find out what fields changed when comparing any two tables.Wed, 04 Jun 2008 07:33:43 GMTmikeg13http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxHow about this...create procedure CompareTables @table1 varchar(100), @table2 varchar(100)as declare @sql varchar(8000) set @sql = 'select ''' + @table1 + ''' as tblName, * from (select * from ' + @table1 + ' except select * from ' + @table2 + ') x union all select ''' + @table2 + ''' as tblName, * from (select * from ' + @table2 + ' except select * from ' + @table1 +') x' exec(@sql)Wed, 04 Jun 2008 03:04:20 GMTRae-Jin Shuihttp://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxHiYou can try like this. i guess it will solve your problemselect distinct a.* from INFORMATION_SCHEMA.COLUMNS awhere a.column_name not in (select column_name from INFORMATION_SCHEMA.COLUMNS bwhere b.table_name in ('stp_rule_dtl') ) ---- Table Aand a.table_name in ('stp_rule_esc_dtl') --- Table Border by a.column_nameThanksjaypee.sThu, 22 May 2008 23:00:41 GMTjaypee_shttp://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxYou may want to search SSC for a solution. I know for a fact that this is not the first time your question has been asked in these forums. You may want to consider a more formal auditing technique if it is feasable. An auditing procedure like what you've suggested will be a very poor performer on large tables as it will have to do a column by column comparison. At the very least, you may want to look at using a timestamp or datetime column to narrow the search down to only those rows where you know for sure changes have been made.Thu, 22 May 2008 13:59:44 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxRather than posting a very long Oracle procedure, I will just describe what the Oracle procedure does. First, let me say my solution is derived from an elegant solution from an article by Sanjay Ray ([url]http://www.oracle.com/technology/oramag/code/tips2004/012504.html[/url]). I was hoping there was an equivalent SQL Server elegant solution that I could adapt. If not, then perhaps this will make a very good article for someone when I figure it out. :)Problem: I have a copy of TableA from yesterday and today. I want to know specifically what changed. The adds and deletes are simple, using the key and the EXCEPT and UNION ALL operators (see [url]http://weblogs.sqlteam.com/jeffs/archive/2007/05/02/60194.aspx[/url]). For modifications, I want to know the key for the modified records and then the fields that were changed as well as the old and new values. This needs to be in a generic stored procedure so I can audit any two tables.Solution: The trick is building a huge dynamic SQL statement. Pass the proc two tablenames, one of which is the "base" table for the comparison. The key structure is determined by looking up the fields in the metadata for indexes for the base table. If there is no index (there should be), the first column is used. The two tables will be joined by the key fields and every other field will be compared.Using the list of columns from the metadata (INFORMATION_SCHEMA.Columns in SS), the query is built for the non-key fields. The SELECT list is built dynamically from the metadata into a concatenated list for each field. Each field will end up looking something like : CASE WHEN A.column_name = B.column_name THEN null ELSE 'column_name=['+A.column_name+'->'+B.column_name+']'ENDThere is a delimiter (~) between each field in the SELECT list. So if the fields are equal the null will result in ~~ and if they are unequal, then the name of the column and the values from each table are put between the delimiters.The dynamic SQL is actually an INSERT statement into another table. This table is then parsed to find the data between the delimiters and report the field names and the old and new values.Voila! I hope any of this made sense. As I work through each of the processes, I may return with more detailed questions. Right now I was just hoping someone else had already published a solution for SQL Server.Thu, 22 May 2008 08:20:34 GMTmikeg13http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxHi,Look at the Article, this will help you in passing the tables as parameters into a stored procedure. http://www.sqlservercentral.com/articles/Stored+Procedures/2977/RajeshThu, 22 May 2008 00:52:01 GMTraja_saminathanhttp://www.sqlservercentral.com/Forums/Topic504916-338-1.aspx[quote] I have two Oracle scripts that handle this, but I need the same functionality in SQL Server 2005. [/quote]Can you please upload Oracle scripts, can have an idea what it is doing and could try to map it to SQL Server 2k5.Wed, 21 May 2008 22:12:18 GMTAnam Vermahttp://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxI believe the columns metadata would be used, but I am not looking for differences in the table structures. I am looking to compare the data in the two tables. But I do need to know the name of the fields where differences occur.Wed, 21 May 2008 21:22:13 GMTmikeg13http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxIn SQL 2005, you can check sys.syscolumns table.Wed, 21 May 2008 20:58:34 GMTSQL ORACLEhttp://www.sqlservercentral.com/Forums/Topic504916-338-1.aspxI do not want to reinvent the wheel, but I need to write a procedure that can be passed two table names and it will compare the tables and list the fields that are different. It can also show the old and new values. The assumptions are that the table structures are the same and there is an index on the base table, which would indicate what fields to join by. I have two Oracle scripts that handle this, but I need the same functionality in SQL Server 2005. Does anyone know of a script that does such a detailed comparison of two tables and does it generically with any two tables passed into the procedure?Wed, 21 May 2008 17:43:43 GMTmikeg13