Truncating a SQL Server Table

smoffa · Post by **smoffa** » Tue Dec 28, 2010 8:59 am

I am new to Parallel jobs an was wondering how to Truncate a SQL server table before I insert. I'm using the enterprise SQL Server stage. I have tried searching for this answer but could not find this topic.

chulett · Post by **chulett** » Tue Dec 28, 2010 9:23 am

Don't have any docs or DataStage in front of me at the moment... usually there is an Update Action akin to "Clear table then Insert" and in most cases that "clear" means truncate. Do you see any such thing in the stage?

smoffa · Post by **smoffa** » Tue Dec 28, 2010 10:24 am

I can set the "Write Method" property = Write, then the stage allows me to set the "Write Mode" = Truncate. However, then the SQL property goes away so I'm not able to enter my insert statement. I must be missing something.

I don't see any before/after SQL tabs/properties either.

chulett · Post by **chulett** » Tue Dec 28, 2010 10:32 am

When you set those properties, there's really no reason for you to be inserting any kind of sql statement - the stage should handle all that for you automagically. Why not just give it a try and see if that works or not? And then let us know, too.

smoffa · Post by **smoffa** » Tue Dec 28, 2010 12:56 pm

Thanks Craig. I got some other error message (Query execute failed) but the insert seemed to work. I guess I still need to get used to parallel jobs.

chulett · Post by **chulett** » Tue Dec 28, 2010 2:19 pm

Can you copy/paste your new error message here? Is the truncation part working for you in addition to the inserts?

smoffa · Post by **smoffa** » Tue Dec 28, 2010 2:42 pm

Yes, the truncate and the insert seems to be working. However, I get a fatal error but the job continues and completes.

The exact error is:

HRO_LOCATIONS: Query execute failed

Where HRO_LOCATIONS is the name of my sql server target stage.