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.
| Author |
Topic |
|
nsrao_1975
Starting Member
13 Posts |
 Posted - 2004-02-14 : 01:59:18
|
| source table state yr2002 yr2003 ------------------------ Andhra 1000 1500 Karnataka 2000 1800 Tnadu 3000 1000 Kerala 2000 3000 I want destination table this State year noofcontracts ---------------------------- Andhra 2002 1000 Andhra 2003 1800 How can i transform it through activex scripting ? pls help me |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-15 : 22:18:51
|
| Why not use an execute sql task?If they are on different servers you can create a liked server or use a query as the source of a transform data task.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
nsrao_1975
Starting Member
13 Posts |
Posted - 2004-02-16 : 00:10:55
|
| hi, thanks 4 ur reply. as u said i can do it by using sqltask itself by using this query select state,2002 as year,yr2002 as noofcontracts union ; select state,2003 as year,yr2003 as noofcontracts as base table and pump into destination table but here two problems are there.. 1. No of years to be compared are be dynamic..may be for 2 or more 2. My source table contains around 1.5 million records (here i gave only example) .. using union clause will result in full table scan twise which considerably will eat time.. is there any other better solution for thisthanks in advance |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-16 : 04:35:35
|
| You can do it as two tasks - one for 2002 one for 2003.Note that union all would be better than union here - that may solve your problem.If it is OK then use the task as below to set the queryTo get round the years being dynamic you can use an activex task to get the years then loop round the transformation task setting the source query before each run.If it is indexed you might want to include the state in the loop to make each transfer smaller.Here's how to get data from a database in an activex script.For setting the source query have a look athttp://www.nigelrivett.net/SetDTSRunTimeValues.htmlWhich sets the server and database for connections.Changing the query is similar - have a look at the object model or load a package into VB to see the entry - SourceSQLStatement.There are a lot of scripts on www.sqldts.com which would be worth a lot.I think there is something there about looping round tasks - the thing to be careful about is that you have to requeue the step after setting the status.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
dts problem urgent pl !!