SQL Server Performance Forum – Threads Archive
SQL Help Pls
Hello All, Quick Question I have an SP that takes in table names and column list for 2 tables and compares the data giving me o/p in the following format. The number of columns are dynamic and depends on the i/p parameter to the SP column list. I am using dynamic SQL to union the two tables and retrieve the rows that are different in the following format. Current O/p From my SP———————-
TableNameIDColumn1Column2Column3
——————————–
T1I1ABC49
T2I1XYZ46
T1I2MNO50
T2I2MNO80
T1I3PQR68
T2I3PQR78
T1I4DEF96
T2I4DEF910 What I need to do is to replace common data between the rows with NULL. Example below.
For ID I1 has Column2 that is equal so replace it with NULL.
Desired O/P
———–
TableNameIDColumn1Column2Column3
——————————–
T1I1ABCNULL9
T2I1XYZNULL6
T1I2NULL5NULL
T2I2NULL8NULL
T1I3NULL6NULL
T2I3NULL7NULL
T1I4NULLNULL6
T2I4NULLNULL10
The fact is that I can put the o/p to a temp table and run a loop. but I need it in one single TSQL step.
Any ideas ? Thanks,
Shinoj R
Your example data for expected results is puzzling to me. I assumed that you wanted to ensure that you had a common number of columns regardless of input and that you simply wanted to return NULLS for Column3 for instance if the input would only return other columns. But in your data you are showing NULL’s in random columns, which is confusing since it sounds like you are running a single dynamic sql command. Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.
Can you post the SP and any dependencies? Since you need it in 1 TSQL step, it may be easier to implement a solution within your stored proc. I am thinking that you can try doing an update statement with a self-join aggregating using a COUNT(*) > 1 to find duplicates.
Thanks for the response guys. Here is the Dynamic SQL used to retrieve the current result set EXEC (
‘SELECT Max(TableName) as TableName,’ + @Table1ColList + ‘ FROM (‘
+ ‘SELECT ”’ + @Table1 + ”’ AS TableName,’ + @Table1ColList + ‘ FROM ‘ + @Table1
+ ‘ UNION ALL ‘
+ ‘SELECT ”’ + @Table2 + ”’ AS TableName,’ + @Table2ColList + ‘ FROM ‘ + @Table2
+ ‘) A GROUP BY ‘ + @Table1ColList + ‘ HAVING COUNT(*) = 1’
) Here @Table1, @Table1ColList, @Table2, @Table2ColList are i/p parameters to the SP.
This SQL returns me the list of different rows between the tables as I have shown in Current O/P.
I do have preliminary checks for number of cols etc. Dalton – If you check the desired results in rows of two, u will find that the common data between two rows for Id columns ‘I1’ is NULLed out (Column2)
More Explanation:
For rows with ID=I1 Column (Column2 [4 and 4]) data is identical hence null both the rows column2 data.
Ignoring the variableness of the column lists you have the following will do what you are looking for for the 3 columns you gave as an example (and should get you started down the path to making it totally dynamic. I think you’d need to parse out the @column list that comes in into individual values so that you can loop them and create the proper code, but I think you are ultimately looking for something like the following .. the key is obviously the CASE WHEN clause:
select TableName, Id, Col1, Col2, Col3 from (
select ‘Table1’ as TableName, #t1.id as ID,
case when (#t1.val1 = #t2.val1) then NULL else #t1.val1 end as Col1,
case when (#t1.val2 = #t2.val2) then NULL else #t1.val2 end as Col2,
case when (#t1.val3 = #t2.val3) then NULL else #t1.val3 end as Col3
from #t1 inner join #t2 on #t1.id = #t2.id
UNION ALL
select ‘Table2’ as TableName, #t1.id as ID,
case when #t1.val1 = #t2.val1 then NULL else #t2.val1 end as Col1,
case when #t1.val2 = #t2.val2 then NULL else #t2.val2 end as Col2,
case when #t1.val3 = #t2.val3 then NULL else #t2.val3 end as Col3
from #t1 inner join #t2 on #t1.id = #t2.id) Results
order by Id, TableName Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.
]]>