SQL Task in DTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Task in DTS

Author

Topic

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-01-03 : 23:24:47

May I create a procedure in the SQL Task of DTS? That includes the variables declaration, if...then blocks, conditional update and insert statements

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-03 : 23:35:14

You can do that.
But why do you want to do that? What exactly is your requirements?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-04 : 00:00:53

Refer www.sqldts.com

Madhivanan

Failing to plan is Planning to fail

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-01-04 : 01:03:54

The DTS will copy supplier information from Oracle server and update the supplier master table on SQL Server.

In the SQL Task, I use a cursor to loop the result set retrieved from Oracle, and check if the record exists in the table on SQL Server. Update the information when found, else insert the supplier record.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-04 : 01:17:01

Dont use Cursor
Use the logic suggested here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=59772

Madhivanan

Failing to plan is Planning to fail

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-01-04 : 01:30:24

The logic you recommended seems to handle single record, but I have a list of suppliers to check and update

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-01-04 : 01:33:23

My SQL Task code is similar to the following:


DECLARE cur_supplier CURSOR FOR
  select ....
    from OracleTable

OPEN cur_supplier 
FETCH NEXT FROM cur_supplier INTO @supcod, @..., @...

WHILE @@fetch_status = 0
begin 

  select @rec_cnt = count(1)
    from SQLServerTable
    where SupplierID = @supcod

  if @rec_cnt = 1 
    BEGIN 
      update SQLServerTable
        set ....
        where SupplierID = @supcod
    END 
  else 
    if @rec_cnt = 0
      BEGIN
        insert into SQLServerTable ....
      END
  

  COMMIT 

  FETCH NEXT FROM cur_supplier INTO @supcod, @..., @...

END

CLOSE cur_supplier 
DEALLOCATE cur_supplier 
GO

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-01-04 : 02:15:02

oops.... I missed something at the beginning my code :p

CREATE PROCEDURE upd_supplier AS

DECLARE @supcod   varchar(10)
DECLARE @....

DECLARE cur_supplier SCROLL CURSOR FOR
  select ....
    from OracleTable

OPEN cur_supplier 
FETCH NEXT FROM cur_supplier INTO @supcod, @..., @...

WHILE @@fetch_status = 0
begin 

  select @rec_cnt = count(1)
    from SQLServerTable
    where SupplierID = @supcod

  if @rec_cnt = 1 
    BEGIN 
      update SQLServerTable
        set ....
        where SupplierID = @supcod
    END 
  else 
    if @rec_cnt = 0
      BEGIN
        insert into SQLServerTable ....
      END
  

  COMMIT 

  FETCH NEXT FROM cur_supplier INTO @supcod, @..., @...

END

CLOSE cur_supplier 
DEALLOCATE cur_supplier 
GO

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-01-04 : 02:17:03

actually, I don't want to add "Create Procedure...", but it prompts error when I executed the DTS

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-04 : 03:47:44

first update all the matching records then do an insert for non existing records

update SQLServerTable
set ....
from OracleTable ot
where SQLServerTable.SupplierID = ot.SupplierID

insert into sqlservertable
select * from oracletable where supplierid not in( select supplierid from sqlservertable )

where

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-01-04 : 23:06:11

Yeah

I did it!!

Thanks for all your help

But why I can't use cursor to do that? Is there any limitation

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-04 : 23:51:12

>>But why I can't use cursor to do that? Is there any limitation

It is slower than Set based approach

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources