BCP Alternatives
With just about anything in SQL Server there are often multiple ways to accomplish the same thing. Maurice, a regular contributor to our discussions, reminds us that there are SQL Server techniques built into TSQL taking advantage of the BCP Engine capability, and the limited logging of those transactions.
Maurice:
An alternative to BCP, in your scenario, would be to simply create a new table from a Select/Into with appropriate filters, truncate the original table, and then perform insert from an select back into the original table. The temporary table can be anywhere you want, in tempdb or for safety into the same database or another temporary database. The choice of another temporary database could be motivated by the need to have it in simple recovery mode, when it is not possible to put the production database into this recovery mode.
This alternative is also valid cross instances since it is possible to pump data out another instance with select/into or Insert/select using linked servers and name like [linkedServerName].[databaseName].[schemaName].[tableName].
Insert / Select and Select / Into are faster way to move data around. The logging is the same, the data is written at some intermediary place in both cases. Ok it probably more job to dump data into an intermediary heap table than into flat file, but at least there is no networking involved in the operation. About moving across instances with linked servers, there is obviously networking, but the load doesn’t seems to be performed in a row by row fashion as far as the profiler shows a single insert/select and extraction is performed through a single select as Bcp would do.
Error Trapping
I wanted to include a couple responses on Try/Catch from the comments added to the web site, in case you didn’t have a chance to read them. Both Greg and Mark reflect on other useful features of Try/Catch in TSQL as well as the pragmatic usefulness of ERROR_LINE() as it was described earlier.
Greg:
I often look at your hints, and when I saw SQL Error Trapping, I took special note. I have used the TRY CATCH Error Handling extensively in Stored procedures and processes that run on a schedule; Email is sent out with error messages to alert the DBA and the Application Developer of errors. To gracefully exit from errant procedures is just good programming. I often include two other system functions in the error report – ERROR_PROCEDURE() as ErrorProcedure, ERROR_MESSAGE() as ErrorMessage which provide the additional – very beneficial – information to the reader:
ErrorNumber ErrorState ErrorLine ErrorProcedure ErrorMessage
8134 1 7 Test_SP Divide by zero error encountered.
Of course, as you mentioned, seasoned programmers would know these additional system functions; however, if a junior programmer were to use your (excellent) sample, he or she would probably feel a bit more comfortable with the additional messages.
Of course, there’s the entire discussion of using TRY CATCH with a transaction processing or with XACT_STATE; then there’s the NULL value returned when the functions are outside of the CATCH block. These may be left as exercises for the reader.
Mark:
I find ErrorLine is almost useless on all but the simplest queries, as it seems to ignore blank lines and comments (or at least comments). When you have an SP over 1,000 lines long and it’s off by 50-100 lines it takes way too long to find the location.
Thanks for the great feedback folks. Keep them coming by including your comments below, or drop an Email to btaylor@sswug.org if that is easier for you.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
What To Do If You Hate Your Job
Most people hate their jobs because they feel that they do not have any control. They feel that things are being done to them? instead of by them. The first step is to take more control of your own career. Wherever you are right now is just where you are right now. There is nothing permanent in your current situation. Since it is changeable, then you can change it. Here are some ideas.
Featured White Paper(s)
Optimizing analytic workloads using DB2 10.5 with BLU Acceleration
BLU Acceleration is a new collection of technologies for analytic queries that are introduced in DB2 for Linux, UNIX, and Win… (read more)