SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert my syntax - Oracle SQL to MS SQL Server


Convert my syntax - Oracle SQL to MS SQL Server

Author
Message
SRI-385740
SRI-385740
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 2

Oracle Sql statement:

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




Regards,

Srinidhi Rao

GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225523 Visits: 46321

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


SRI-385740
SRI-385740
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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

ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6709 Visits: 1865

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"
GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225523 Visits: 46321

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


SRI-385740
SRI-385740
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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

GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225523 Visits: 46321

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


SRI-385740
SRI-385740
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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

GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225523 Visits: 46321

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


SRI-385740
SRI-385740
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search