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

Import/Export (DTS) and Replication (2000)

DTS issue

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-23 : 15:57:25

this the query I am trying to execute through DTS.Can I get the max value using MAX(DTS("Source"))

1>select max(emp_id) from tbl_emp_details (from Database1)
2>insert data into tbl_emp_details of database 2 from tbl_emp_details of database1 where emp_id>=<<value fetched from step 1>>
Use this query:
insert into emp_details
(emp_id, emp_name, process,date, status)
select emp_id,'John', date(),'NEW' union all

delete from database1 where emp_id>= <<value fetched from step 1>>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-23 : 16:24:59

Could you explain what you are trying to do?

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-23 : 16:31:30

I want to get the max emp_id from emp_detail table in database1 and then use that as a criteria for inserting data into database2.I have to 2 connections source and destination .code something like this

Dim emp_id int

Function Main()

emp_id=Max (DTSSource("emp_id"))

If emp_id >= DTSDestination("emp_id") then

DTSDestination("emp_id") =DTSSource("emp_id")
DTSDestination("name") = DTSSource("name")
DTSDestination("process") =0
DTSDestination("date")=date
DTSDestination("status)="NEW"

Main = DTSTransformStat_OK
end if
End Function

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-23 : 16:33:07

I'm not following. Could you provide an example with data?

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-23 : 16:42:27

emp_details from database 1

emp_id emp_name process date status
4 john 0 12/12/2004 NEW

5 Raj 0 12/12/2004 NEW

6 Mike 0 12/12/2004 NEW

Database 2

emp_id emp_name process date status
1 Henr 0 12/12/2004 NEW

2 Vinu 0 12/12/2004 NEW

3 AAj 0 12/12/2004 NEW

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-23 : 16:58:41

We still need more information. Using the data that you posted, what should occur when the queries execute?

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-23 : 17:05:37

Intially I need to get the max(emp_id from the emp_detail table) of database 1 so that I can compare with the emp_id in destination table of the database 2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-23 : 17:09:39

Compare it to the max in the other database?

DECLARE @max1 INT
DECLARE @max2 INT

SELECT @max1 = MAX(emp_id) FROM SomeTable
SELECT @max2 = MAX(emp_id) FROM SomeOtherTable

IF @max1 <> @max2
...

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-23 : 17:13:36

How can I use this in AtiveX acript in DTS.will this work

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-23 : 17:16:39

What exactly is your DTS package doing? I just don't see a need for DTS yet.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-23 : 17:22:34

No I need a DTS it is trying to copy the data according to the max emp_id from one database to the other.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-23 : 17:25:45

You don't need DTS to copy data though. If you explained in words what the entire goal of the DTS package is, then we can provide a solution for you. Right now, you are only giving us bits and pieces of it so it is very hard to help.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-23 : 17:59:35

Basically Just even showing me a modal could be fine.I want to pump data from table1 of database1 to table 2 of database 2.Here I want to pump the data from table 1 of database1 with criteria as
first I have to get the max emp_id and then check that emp_id with respect to the table 2 of database2 and if the
emp_id (table1)>=emp_id(table2) then I have to pump the data and then
delete the corressponding dataset from the table 2 of database2.

My main concern is How i can get the max emp_id in Active Script

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-23 : 18:15:53

Here is where we are going with this:

INSERT INTO DB2.dbo.Table2(ColumnList...)
SELECT ColumnList...
FROM DB1.dbo.Table1
WHERE...

But let's look at your data example:

emp_details from database 1

emp_id emp_name process date status
4 john 0 12/12/2004 NEW

5 Raj 0 12/12/2004 NEW

6 Mike 0 12/12/2004 NEW

Database 2

emp_id emp_name process date status
1 Henr 0 12/12/2004 NEW

2 Vinu 0 12/12/2004 NEW

3 AAj 0 12/12/2004 NEW

In the above, max id in DB1 is 6. So now what? Move 6 over to Table2? Or move 4, 5, and 6 over to Table2?

And let's forget DTS for now. We are just moving data around inside SQL Server, so we just need T-SQL commands.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-23 : 18:23:39

Move 6 over to table 2 and then delete the row with value 6 from table 2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-23 : 18:25:16

I don't understand. Move 6 over to table2, then delete 6 from table2? Wouldn't that be deleting what you just inserted? Or do you mean delete it form table1?

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-23 : 18:39:06

sorry from table1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-23 : 18:42:10

DECLARE @max_empid int

SELECT @max_empid = MAX(emp_id)
FROM DB1.dbo.Table1

INSERT INTO DB2.dbo.Table2 (emp_id, emp_name, process, [date], status)
SELECT emp_id, emp_name, process, [date], status
FROM DB1.dbo.Table1
WHERE emp_id = @max_empid

DELETE FROM DB1.dbo.Table1 WHERE emp_id = @max_empid

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-23 : 20:19:32

In the insert statement above I want the records till @max_empid not where emp_id=@max_emp_id.eg:
if the @max_emp_id=4 then the records with emp_id 1,2,3 and 4 should be inserted to the destination

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 11:55:08

Then use <=

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-08-24 : 12:20:23

Now the qyueries are fine for me but implement using a DTS is a problem.I need to pass the max activation_id as a global variable

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources