-
DTS Help
I am transfering the data between the SQL tableand AS400 table. I am using the ActiveX script to transfer the data. The table in SQL have no primary key, but the table in AS400 have a primary key. I need the examples how to handle this. The basic rules are:
If the primary key is exist->Update
If the primary key is not found ->Insert.
Please help me.
Thanks
-
DTS Help (reply)
Try DTS using Data Driven Query Task (DDQT).
I'm not familiar with AS400, but if it is from SQL to SQL I would create a stored proc on my destination SQL server and use it in DDQT. If you have a similar facility to create a custom proc in AS400, you might be able to use it.
good luck..
------------
Sardinka at 10/30/01 10:46:02 AM
I am transfering the data between the SQL tableand AS400 table. I am using the ActiveX script to transfer the data. The table in SQL have no primary key, but the table in AS400 have a primary key. I need the examples how to handle this. The basic rules are:
If the primary key is exist->Update
If the primary key is not found ->Insert.
Please help me.
Thanks
-
DTS Help (reply)
Can you give me the examples.
I created the new dts (DDQT) and I have no idea how to compare the fields and the correct way to write the update and insert based on the compare.
Thanks in advance
------------
WizKid at 10/31/01 11:40:24 AM
Try DTS using Data Driven Query Task (DDQT).
I'm not familiar with AS400, but if it is from SQL to SQL I would create a stored proc on my destination SQL server and use it in DDQT. If you have a similar facility to create a custom proc in AS400, you might be able to use it.
good luck..
------------
Sardinka at 10/30/01 10:46:02 AM
I am transfering the data between the SQL tableand AS400 table. I am using the ActiveX script to transfer the data. The table in SQL have no primary key, but the table in AS400 have a primary key. I need the examples how to handle this. The basic rules are:
If the primary key is exist->Update
If the primary key is not found ->Insert.
Please help me.
Thanks
-
DTS Help (reply)
It is a scenario of moving data beween two SQL server tables
In a new DTS package:
Create source and destination connection objects
Click on "Data Driven Query Task" in the Task controls
Specify you Source and Destination tables in the corresponding tab-pages. ** (see bottom)
Tab over to transformation tab-page. (DTS will create an Activ-X script transformation combining all source columns and Dest columns)
Double-Click on the Transformation Line. It willopenan Active-X script.
Modity the line in the Script that says "Main = DTSTransformStat_InsertQuery" to "Main = DTSTransformStat_UserQuery" and save it.
Tab over to Query Tab-Page.
Select "User" in the "Query Type" drop-down.
Type your Stored Proc in the Text-Box below. *** (see bottom)
Click Parse/Show Parameters. This should list the parameters for the stored proc.
Click OK and run the package.
As an Example:
I have two tables My_table1 and My_table2 with two columns in each col1 and col2
I'm moving data from My_table1 to My_table2. If col1 exists in My_table2, update it; else insert.
Here is my proc (This proc needs to be on the Destination) :
create procedure My_proc
(@col1 int, @col2 varchar(30) )
as
if exists ( select 'x' from my_table2 where col1 = @col1 )
update my_table2 set col2 = @col2 where col1 = @col1
else
insert into my_table2 values ( @col1, @col2 )
** What you select in you source query depends on what your stored proc requires.
"My_Proc" requires two parameters @col1 and @col2.
So my Source query is Select col1, col2 from My_Table1
***The way to call stored-proc in the Query tab-page is "My_proc ?, ?" (This proc needs to be on the Destination)
-- Hope it helps
------------
sardinka at 10/31/01 4:53:56 PM
Can you give me the examples.
I created the new dts (DDQT) and I have no idea how to compare the fields and the correct way to write the update and insert based on the compare.
Thanks in advance
------------
WizKid at 10/31/01 11:40:24 AM
Try DTS using Data Driven Query Task (DDQT).
I'm not familiar with AS400, but if it is from SQL to SQL I would create a stored proc on my destination SQL server and use it in DDQT. If you have a similar facility to create a custom proc in AS400, you might be able to use it.
good luck..
------------
Sardinka at 10/30/01 10:46:02 AM
I am transfering the data between the SQL tableand AS400 table. I am using the ActiveX script to transfer the data. The table in SQL have no primary key, but the table in AS400 have a primary key. I need the examples how to handle this. The basic rules are:
If the primary key is exist->Update
If the primary key is not found ->Insert.
Please help me.
Thanks
-
DTS Help (reply)
Thank for all your help.
I have one more question:
Is it any way to do comparasing statement in ActiveX transformation script?
I already try some,but it's not working so I am not sure if this correct.
If so. Do you have some examples? Thanks again
My Examples:
Dim SQL
Dim AS400
SQL=DTSSourse("columnname1"
Genelco="Select columnname from table where columnname1= '" & SQL & "' "
...
If exists...
Then
update
else
ineert
end if
-
DTS Help (reply)
Wow!! You kept me thinking.. I'm a SQL DBA and not very good at VB Script. So I usually use stored procs. How ever, I tried to do it in Active X script and just got it to work..
Here it is..
Source : MY_Table1 (Col1 int primary key, col2 char(4), col3 char(4))
Destination : My_Table2 (Col1 int primary key, col2 char(4))
Transformation : If-Col1-Exists-Update-else-Insert
My_Table1.Col1 = My_Table1.Col1
My_Table1.Col2 = My_Table1.Col2 + My_Table1.Col3
I used two data sources and a ODBC DSN. (I did NOT use Data Driven Query Task)
Define transformation between the Source and Destination.
Tab-over to "Transformations" tab page. Remove all default transformations.
Highlight all source and destination columns
From "New Transformation" drop-down, select "ActiveX Script" and click NEW.
In AX Script Properties type your ActiveX code.(Below is the code for my example)
Function Main()
Set cmd = CreateObject("ADODB.Command"
Set rs = CreateObject("adodb.recordset"
rs.CursorLocation = 3
cmd.ActiveConnection = "dsn=LocalServer;uid=sa;pwd=password;"
cmd.CommandType = 1
CMD_STR = "select count(*) my_count from My_table2 where col1 = " & DTSSource("col1"
cmd.CommandText = CMD_STR
rs.Open cmd, , 1,1
If rs.fields("my_count".value > 0 then
rs.close
CMD_STR = "update My_Table2 set col2 = '" & DTSSource("col2" & DTSSource("col3" &"' where col1 = " & DTSSource("col1"
cmd.CommandText = CMD_STR
rs.Open cmd, , 1,1
Main = DTSTransformStat_SkipInsert
else
DTSDestination("col1" = DTSSource("col1"
DTSDestination("col2" = DTSSource("col2" & DTSSource("col3"
Main = DTSTransformStat_OK
end if
End Function
.. GOOD LUCK !!!
------------
sardinka at 11/1/01 3:55:03 PM
Thank for all your help.
I have one more question:
Is it any way to do comparasing statement in ActiveX transformation script?
I already try some,but it's not working so I am not sure if this correct.
If so. Do you have some examples? Thanks again
My Examples:
Dim SQL
Dim AS400
SQL=DTSSourse("columnname1"
Genelco="Select columnname from table where columnname1= '" & SQL & "' "
...
If exists...
Then
update
else
ineert
end if
-
DTS Help (reply)
Thanks. I'll try it today. I have one question nothing to do with DTS.
How can I compare 2 table(with PK) not using the join.
Basic idea like this:select distinct from table1, select distinct from table2
Compare the Primary key on both tables.
Thanks
-
DTS Help (reply)
Sorry I lost you. I'm not sure what you are trying to do. If you have a comparable primary keys in both the tables, why not join them? Again, we might need an example here :-)
------------
sardinka at 11/2/01 9:56:34 AM
Thanks. I'll try it today. I have one question nothing to do with DTS.
How can I compare 2 table(with PK) not using the join.
Basic idea like this:select distinct from table1, select distinct from table2
Compare the Primary key on both tables.
Thanks
-
DTS Help (reply)
I need to compare 2 tables. If record exist (based on primary key) in one table and not exist in the second table(based on primary key) delete from table. Both tables need to have the same primary key.Let me know if my explanation fine.
Thanks
-
DTS Help (reply)
delete from My_Table1 where My_Table_Key not in (Select My_Table_Key from My_Table2)
OR
delete from My_table1
where not exists (select 'x' from My_table2 where My_Table1.My_Table_Key = My_table2.My_Table_Key)
The former works for simple primary key only and the later works for composite keys as well. Repeat the same by interchanging the table names.
------------
sardinka at 11/2/01 11:21:11 AM
I need to compare 2 tables. If record exist (based on primary key) in one table and not exist in the second table(based on primary key) delete from table. Both tables need to have the same primary key.Let me know if my explanation fine.
Thanks
-
DTS Help (reply)
Hi...This is my query I am running:
I am trying to print the row number and I can't see where is my err. Help me
declare @row_count int
SET @row_count = 0
SET @row_count = @row_count + 1
delete from table1 where not exists
(select num,id
from table2 where table1.num = table2.num and
table1.id = table2.id
print "row" + convert(varchar,@row_count))
Thanks
-
DTS Help (reply)
Just some more information.
Each tables has around 2 million records.
-
DTS Help (reply)
declare @row_count int
delete from table1 where not exists
(select num,id
from table2 where table1.num = table2.num and
table1.id = table2.id
select @row_count = @@rowcount
print "row" + convert(varchar(12),@row_count))
------------
sardinka at 11/2/01 12:27:59 PM
Hi...This is my query I am running:
I am trying to print the row number and I can't see where is my err. Help me
declare @row_count int
SET @row_count = 0
SET @row_count = @row_count + 1
delete from table1 where not exists
(select num,id
from table2 where table1.num = table2.num and
table1.id = table2.id
print "row" + convert(varchar,@row_count))
Thanks
-
DTS Help (reply)
When I check the syntax I am getting this err:
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near ''.
-
DTS Help (reply)
OOPS!!
declare @row_count int
delete from table1 where not exists
(select 'x'
from table2 where table1.num = table2.num and
table1.id = table2.id )
select @row_count = @@rowcount
print "row" + convert(varchar(12),@row_count)
------------
sardinka at 11/2/01 1:56:10 PM
When I check the syntax I am getting this err:
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near ''.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|