Convert my syntax - Oracle SQL to MS SQL Server

  • Oracle Sql statement:

    select * from table1 where (f1, f2) in (select f1, f2 from table2 )

     


    Regards,

    Srinidhi Rao

  • Assuming that f1 and f2 are fields in table1 and that the in works the same way in oracle as sql...

    Select * from table1 inner join table2 on table1.f1=table2.f1 and table1.f2=table2.f2

    HTH

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • thanks for the reply...

    but what is the sql statement if it is NOT IN instead of IN

    select * from table1 where (f1, f2) not in (select f1, f2 from table2 )

     

     


    Regards,

    Srinidhi Rao

  • in mssql also its "NOT IN" but it can be used only for a single field.

    select * from table1 where (f1) not in (select f1 from table2 )

    "Keep Trying"

  • It looks a little wierd, but this is what you want. A not in for 2 fields.

    Select table1.* from table1 LEFT OUTER join table2 on table1.f1=table2.f1 and table1.f2=table2.f2

    WHERE Table2.f1 is null and table2.f2 is null

    This is an alternative that will also work

    Select * from table1 WHERE NOT EXISTS (Select 1 FROM table2 where table1.f1=table2.f1 and table1.f2=table2.f2)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • there is no merge statement in ms sql server, i have a merge statement like this (see below). Is there a (new) statement which works like merge of oracle sql.

    MERGE INTO table2 B

    USING (

      SELECT f1, f2

      FROM table1

      WHERE f1 =20) A

    ON (B.f1 = A.f1)

    WHEN MATCHED THEN

      UPDATE SET B.f1 = A.f2 * 0.1

    WHEN NOT MATCHED THEN

      INSERT (B.f1, B.f2)

      VALUES (A.f1, A.f2 * 0.05);

     


    Regards,

    Srinidhi Rao

  • No merge statement. You'll have to write seperate update and insert statements. Something like this

    Update table2 set f1 = table1.f2*0.1

    FROM table1 where table2.f1=table1.f1 and table1.f1=20

    INSERT Into table2 (f1, f2)

    SELECT f1, f2*0.05 FROM table1 where not exists (select 1 from table1 where table2.f1=table1.f1 and table2.f2=table1.f1 and f1=20)

    those should be equivalent, but they are untested and I'm not completely familiar with how oracle's merge works

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • thanks!

    Oracle merge inserts or updates the rows into the table2 (i.e. merge table) based on the equijoin condition (i.e. (B.f1 = A.f1))

    ------------------------------------------------------------ 

    I have 2 tables (more than 10 million rows each).

    table1 has 3 columns

    a - datatype - varchar, b - varchar, c - float

    there is no primary key or index

    there is data in column a,b & all the rows of column c have null value

    table2 has same 3 columns

    a - varchar, b - varchar, c - varchar

    there is no primary key or index

    there is data in column a,b,c...

    I have a simple procedure to put the c value of table2 into c value of table1.

    The procedure looks like this

    ------------------------------------------------------------

    create procedure my_procedure as

    begin

     declare @a nvarchar(255),@b nvarchar(255)

     declare @C nvarchar(255)

     declare c1 cursor dynamic

     for select a,b,c from table1

     open c1

     fetch next from c1 into @a,@b,@c

     while(@@fetch_status = 0)

     begin

      update table2

      set c = @C

      where a = @a and

      b = @b-2

      print @C

      print @a

      fetch next from c1 into @a,@b,@c

      if @@error <> 0

       print 'exception 1 ' + @@error 

     end

     close c1

     deallocate c1

    end

    ------------------------------------------------------------

    It takes arround 4 hrs to execute this procedure, is there a way to optimize this procedure code? (I dont have the rights to add keys to table or index..etc. only code changes) Its actually data transfer of column c from table1 to table2


    Regards,

    Srinidhi Rao

  • Absolutely, yes. Cursors are massively inefficient.

    Again this is untested, but I think it should do the same as the cursor. I'd be very interested in how long it runs.

    UPDATE

    table2 SET c = CAST(table1.c AS VARCHAR(255))

    FROM table1 WHERE table1.a = table2.a and table1.b=table2.b

    btw, if you have tables with 10 million + rows with no indexes, you're going to have terrible performance. Is there anyone you can speak to about putting indexes on these tables? Not for this, but for all your other queries

    Also, you said about copying the values of column c from table2 into table1, but your code has it the other way round.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  •  

    UPDATE table2 SET c = CAST(table1.c AS VARCHAR(255))

    FROM table1 WHERE table1.a = table2.a and table1.b=table2.b

    this actually takes more time (compared to the procedure)!

    Is there a bulk update! in ms sql server?


    Regards,

    Srinidhi Rao

  • No bulk update, and I would hazard a guess that it's not the logging that's slowing your proc down.

    Is there any chance of getting someone to add an index? on a and b in both tables should really help.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Assuming that f1 and f2 are varchar, the most direct equivalent in SQL Server would be:

    select * from table1 where f1+f2 in (select f1+f2 from table 2)

     

  • That works, the problem with it is that it prevents any index usage, if you have an index on f1 or f2.

    Reason been it's seen as a function on a column, which instantly makes the expression non-SARGable

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply