Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Convert my syntax - Oracle SQL to MS SQL Server Expand / Collapse
Author
Message
Posted Wednesday, November 29, 2006 12:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 17, 2007 9:05 AM
Points: 26, Visits: 2

Oracle Sql statement:

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

 




Regards,

Srinidhi Rao

Post #326295
Posted Wednesday, November 29, 2006 12:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 43,042, Visits: 36,197

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 2008, MVP
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

Post #326296
Posted Wednesday, November 29, 2006 12:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 17, 2007 9:05 AM
Points: 26, Visits: 2

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

Post #326299
Posted Wednesday, November 29, 2006 1:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 1, 2014 6:55 AM
Points: 2,366, Visits: 1,845

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"
Post #326304
Posted Wednesday, November 29, 2006 1:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 43,042, Visits: 36,197

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 2008, MVP
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

Post #326310
Posted Wednesday, November 29, 2006 3:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 17, 2007 9:05 AM
Points: 26, Visits: 2

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

Post #326324
Posted Wednesday, November 29, 2006 4:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 43,042, Visits: 36,197

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 2008, MVP
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

Post #326326
Posted Wednesday, November 29, 2006 4:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 17, 2007 9:05 AM
Points: 26, Visits: 2

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
  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

Post #326327
Posted Wednesday, November 29, 2006 5:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 43,042, Visits: 36,197

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 2008, MVP
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

Post #326328
Posted Wednesday, November 29, 2006 6:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 17, 2007 9:05 AM
Points: 26, Visits: 2
 

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

Post #326341
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse