Committing imports in SQL Server a little at a time

by Arthur Fuller | Jul 31, 2006 5:22:00 PM

Tags: Databases, Arthur Fuller, Microsoft SQL Server, DTS, Commit Final Batch, text-file, Informix Software, server, SQL Server Newsletter

3 comment(s)

Takeaway: Learn how Arthur Fuller helped a colleague, who was working on an Informix database and needed to search TEXT columns using wildcards. Check out the simple solution to this seemingly perplexing problem.

A colleague working on an Informix database needed to search TEXT columns using wildcards. While Informix supports wildcards in LIKE and MATCH predicates, this support does not include TEXT columns. The obvious solution�to export the data to SQL Server�does support such searches.

To accomplish this, my colleague had Informix export the data of interest to a text file. Then, a SQL Server DTS package imported the text file into a local instance of SQL Server, where he could process the TEXT column using wildcards.

Unfortunately, there were two problems: 1) The connection was slow and frequently timed out before the import was complete; 2) If he decided to cancel the job halfway into it, SQL Server discarded all rows processed up to that point. He needed to find some way of committing every n record before continuing.

While trying to find a solution for him, I was wandering around in the DTS wizard, and lo and behold, the solution was already built into DTS.

To illustrate how this works, I built a DTS package that exported the Northwind Customers database to a text file. Then, I built a new package to import this text file into a copy of Northwind called Northwind_New. From within the DTS wizard, the result looks like Figure A. Now, I placed the cursor over the pipe, right-clicked and then selected Properties. The Transform Data Task Properties tabbed dialog box appeared. The tab of interest in this case is Options, as illustrated in Figure B.

The options of interest are Always Commit Final Batch and Insert Batch Size. The first option is self-explanatory; the latter lets you control the number of rows inserted in the batch. You can adjust this setting to suit your requirements and environment. The default setting of zero causes the problem experienced by my colleague�loss of the whole batch if there is a failure. A setting of 1 forces a COMMIT after each insertion. A setting of 100 or 1000 forces the COMMIT every 100 or 1000 rows.

Now that my colleague's problem is solved, he is merrily searching his TEXT columns using wildcards.

Miss a tip?

Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

3 comment(s)