Hi I am new to sql and stored procs. I found this example online , but I'm not sure how all the syntax should look. Could someone please help me out? After the AS...how do i list the columns. do i set it = to T1Columlist or just do select col1, col2?
just not sure exactly what he means and how i should do it?
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 = @T1ColumnListset @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList + ' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' + @t2ColumnList + ' FROM ' + @Table2set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList + ' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList + ' HAVING COUNT(*) = 1'exec ( @SQL)
Start Free Trial
