0

Need help with sql sp. I will be calling this sp from vbscript with two table names

table1
id  f-name l_name phone
1   john   smith  111-111-1111
2   john   doe    222-222-2222
3   peter  Wu     333-333-3333
4   peter  Wang   444-444-4444
table2
id  f-name l_name phone
1   john   smith  123-456-1111
2   john   doe    234-222-2222
3   peter  Wu     345-454-3333
4   peter  Wang   456-444-4444

the stored procedure, first should get top 1 * from both the tables and should compare the first row from table1 and table2 and return all the column names if the data is not same, here the stored procedure should return column "phone". I am naive in sql and need help to write this stored procedure.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Maddy
  • 37
  • 1
  • 12
  • 1
    Which DBMS product are you using? Postgres? Oracle? ("SQL" is a query language, not the name of a specific database product) –  Jun 18 '18 at 18:50
  • Comparing two tables is usually done using a *Full Outer Join* on the Primary Key columns and then a CASE for each column to check if they are equal or not. – dnoeth Jun 18 '18 at 18:53
  • 1
    Why would you need stored procedure for this???? – Eric Jun 18 '18 at 19:14
  • SQL Server. i have googled and found Full Outer Join but even the primary key might be different in my case so cannot join based on primary key. Any column data can be different in those tables. – Maddy Jun 18 '18 at 19:56
  • Hi Eric, I am planning to call this sp from my code and will pass table names as parameters to the stored procedure. i heard the select top 1 * will not return the same row from the both the tables. is it true? – Maddy Jun 18 '18 at 20:14
  • What do you mean you can't join on the primary key? You mean that you have two tables that represent the same data, and the primary keys might be different, and you want to find where the data doesn't match? In your example data, how do you know that Table1's Peter Wu and Table2's Peter Wu aren't different people with the same name? How can you tell if they are the same person and their phone number has changed? How can you tell if someone gets married and changes their name? Without some frame of reference, it's impossible to tell what has happened. – Bacon Bits Jun 18 '18 at 21:40
  • Remember that, by definition and design, a table is an *unordered* series of records. It's a table, not a list. The table doesn't remember the order records were inserted unless you add a timestamp or order value to do that. There is no order internally unless you specify an ORDER BY when you query the table. "compare the first row from table1 and table2" doesn't make any sense unless you specify how that order is determined. – Bacon Bits Jun 18 '18 at 21:42

1 Answers1

0

By "top 1" I assume that you mean by id:

select ( (case when t1.fname <> t2.fname then 'fname;' else '' end) +
         (case when t1.lname <> t2.lname then 'lname;' else '' end) +
         (case when t1.phone <> t2.phone then 'phone;' else '' end)
       ) as diff_columns
from (select top (1) t1.*
      from table1 t1
      order by id
     ) cross join
     (select top (1) t2.*
      from table2 t2
      order by id
     ) ;

I don't see much utility in this stored procedure. My guess is that you have a more complicated problem (comparing two tables). If so, you should ask another question.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • You are right, its complicated than that. I meant top first row in that table. I am trying to create a temp table with the same schema and adding auto increment column to it and then maybe it will be easy. – Maddy Jun 18 '18 at 22:18