DTS global variable passing issue

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

Import/Export (DTS) and Replication (2000)

DTS global variable passing issue

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-24 : 02:25:26

I am using a DTS package:
I have 2 database and 2 tables
I have to insert data from database1 (Table1) to database2(table2)

conditions:
1. I have to get the max(emp_id) from source table(table1) and assign it to a global variable and then use this query:
select * from table1 where emp_id < (global value)
2.Now I have insert this recordset to the table2 of database2
3.Now Delete the dataset from table1 where emp_id<(global variable value)

Now I need to schedule this process since every now and then there will be entry to table1 and i have to insert the dataset till the maximum id value to the table2 and then delete those from table 1.

I used a execute sql task task for passing the global variable.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-08-24 : 05:36:35

Why do you need to do it in that many steps? Are the databases on the same server? If so then just do...

declare @MaxId int

select @MaxId = select max(emp_id) from database1..table1

Insert into database2..table2
select * from database1..table1 where emp_id < @MaxId

delete from database1..table1

Or you could forget your max statement entirely as you seem to want all the records in table1 anyway...

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-24 : 12:21:13

No its in different servers and I need to run a DTS for this

dhsieh
Starting Member

3 Posts

Posted - 2004-09-10 : 14:28:23

Hi sqllearner,

Have you found a way to do this in DTS? I am trying to create a DTS task that wil ldo the same thing.

- Dhsieh

quote:
Originally posted by sqllearner

I am using a DTS package:
I have 2 database and 2 tables
I have to insert data from database1 (Table1) to database2(table2)

conditions:
1. I have to get the max(emp_id) from source table(table1) and assign it to a global variable and then use this query:
select * from table1 where emp_id < (global value)
2.Now I have insert this recordset to the table2 of database2
3.Now Delete the dataset from table1 where emp_id<(global variable value)

Now I need to schedule this process since every now and then there will be entry to table1 and i have to insert the dataset till the maximum id value to the table2 and then delete those from table 1.

I used a execute sql task task for passing the global variable.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-10 : 23:51:48

Thanks a lot guys.... I got the solution.

dhsieh
Starting Member

3 Posts

Posted - 2004-09-13 : 16:38:47

Would you mind sharing your solution to others?

quote:
Originally posted by sqllearner

Thanks a lot guys.... I got the solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources