Any ideas?... - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Any ideas?...

Author

Topic

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2005-04-12 : 15:40:03

I am building a large DTS package for some data importing, manipulation and moving. I have a problem I was hoping someone could take a look at and see if I am missing something. I have a table (TABLE1) with a batch ID field in it. I have another table (TABLE2) with a tmp Batch ID in it. What I need to do is find the last Batch ID used in TABLE1, add 1 to that. Than I need to change all the tmpBatchIds in TABLE2 that are equal to 1 to that, add one to the Batch ID and change alll tmpBatchIDs that are 2 to that, etc up to I get done with records with a tmpBatchID of 7. I was thinking of using a stored procedure, then thought I would need a Global Variable and didn't know how to set one in there. Any ideas?

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-12 : 15:47:15

huh? First I would NOT use DTS. Just do it all in a sproc.....

Brett

8-)

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2005-04-12 : 15:50:04

Can you do an import from an excel document in a stored procedure?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-12 : 15:54:44

how 'bout this?

declare @diff int
select @diff = max(batchid) from table1
update table2 set batchid = batchid + @diff
ie:

--================================================


set nocount on
declare @table1 table (batchid int)
declare @table2 table (batchid int)

insert @table1
Select 100 union
select 101 union
select 102 

insert @table2
Select 1 union
select 2 union
select 3  

declare @diff int
select @diff = max(batchid) from @table1
update @table2 set batchid = batchid + @diff

select * from @table1
select * from @table2

Be One with the Optimizer
TG

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2005-04-12 : 16:15:52

Fantastic, worked very well. Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources