DSXchange

Hi everyone ,

My job is to truncate the target table and then has to insert the rows.My target here is Sql Server. is there any solution?I think we can call the stored procedure to do the truncate option but i want to know is there any other way to truncate the table first and insert rows.

thanks in advance.

Delete the table in Before Sql and then do the insert

anbu wrote:Delete the table in Before Sql and then do the insert

My job is to load all the data from the source but have to load the data after i truncate the target only if there is any data in the target table if not just load.So i dont want to delete the table instead just need to truncate the table.

Thanks in advance.

What stage are you using? Most would have an update action similiar to "Clear table then insert rows" which would do the truncate for you.

chulett wrote:What stage are you using? Most would have an update action similiar to "Clear table then insert rows" which would do the truncate for you. ...

I am using ODBC stage , so you mean the update action "Clear table then insert rows" is to do the truncate and insert the rows.Because i was wondering as the Oracle stage has both the update action options which is "Clear table then insert rows" and "Truncate table then insert rows ."If both the update actions have the same functionality then why did they provided two options in update action in Oracle stage.

Thanks in advance

Clear table then insert rows - Delete the rows from the table
Truncate table then insert rows - Drop the table.

anbu wrote:Clear table then insert rows - Delete the rows from the table
Truncate table then insert rows - Drop the table.

Hi anbu,

I am confused, do u mean that "Truncate table then insert rows" means there wont be any table as you said which is Drop the table i.e. table is going to be deleted. From my point of view , Both Truncate and delete has the same functionality but the Delete option has a where clause but not for truncate.i.e. It is going to delete all the rows which are exisisted in the table.So from your point "Clear table then insert rows" means it is going to delete all the rows and insert the new rows?

thanks in advance

Sorry for the confusion. You are right.

From the documentation

Clear table then insert rows - Deletes the contents of the table and adds the new rows, with slower performance because of transaction logging. This is the default value.

Truncate table then insert rows - Truncates the table with no transaction logging and faster performance. For IBM DB2 and Informix, this option is the same as Clear table then insert rows.

So, finally you mean "Clear table then insert rows" is same as "Truncate table then insert rows ". But i see both the options in Oracle stage and i see only one of the option "Clear table then insert rows" in remaining stages like Informix,DB@,ODBC etc.If both are the same i.e. having same functionality ,why did they provided both the options in Oracle stage.

Thanks in advance

Read what was posted a little more carefully.

chulett wrote:Read what was posted a little more carefully. ...

I do understood what anbu has said previously.but i am not asking about the one which he has posted.As he said,"Clear table then insert rows" and "Truncate table then insert rows" are same in DB2,Informix. And there is only option available in DB2,Informix are "Clear table then insert rows".I see both the options available in Oracle stage which means that Are both the options same for Oracle too are different ?If yes then they could have provided only the default option as in DB2,Informix etc.....


Thanks in advance

Difference is in the performance and transaction logging

As noted in the documentation, those two Update Actions perform two different functions in Oracle. They only behave "the same" for DB2 and Informix.

As per the Documentation , it has the same functionality for DB2, Informix but Oracle has different functionality.So i want to know how does it differ for SQL Server .Does it provides the same functionality for the SQL Server too or provides different functionality as my target database is SQLServer.And if it provides different functionality then "Clear table then insert rows" for the update action in SQL Server is going to delete all the rows or drop the table and then insert rows.

Thanks in advance

Neither one "drops" the table. And you have the documentation, I don't, so why not simply look it up and tell us?