print number of update records through sql job

I have update statement which updates column name (auditor_details) in a 
churn of 5000 records at a time of auditor table which contain 3.2 millions 
records. I have schedule this through sqljob. I'm looking over sql example to 
check for update records and gives a output and quit the jobs if no update 
records.

Thanks in advance

0
Utf
9/3/2010 2:47:03 AM
sqlserver.programming 1873 articles. 0 followers. Follow

1 Replies
669 Views

Similar Articles

[PageSpeed] 41

sqlnovice (sqlnovice@discussions.microsoft.com) writes:
> I have update statement which updates column name (auditor_details) in a
> churn of 5000 records at a time of auditor table which contain 3.2
> millions records. I have schedule this through sqljob. I'm looking over
> sql example to check for update records and gives a output and quit the
> jobs if no update records. 
 
There are two ways to go, either a loop over the clustered index:

SELECT @id = min(id) FROM tbl
SELECT @incr = 50000
WHILE EXISTS (SELECT * FROM tbl WHERE id >= @id
BEGIN
   UPDATE tbl
   SET    col = ...
   WHERE  id BETWEEN @id AND @id + incr - 1

   SELECT @id = @id + @incr
END

Here I've assumed that id is an integer, but it can also work with dates,
in which case you need to find a good interval. Maybe 30 days.

The other where to go is

WHILE 1 = 1
BEGIN
   UPDATE TOP(@batchsize)
   SET    col = ...
   WHERE  ...

   SELECT @@rowcount= @rowc

   IF @rowc <> @batchsize
      BREAK
END


This presumes that can identify which rows you have processed.

The first method is likely to be faster, since SQL Server does not
have to scan the table each iteration.

Then again, for only 3.2 million rows I might consider doing it all
at once, unless it's a LOB column you are updating.

  

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
9/3/2010 8:50:48 PM
Reply:

Similar Artilces:

Printing titles
I'm using Excel XP and I am unable to use the Print title Repeat rows it is greyed out all the time. I have tried several machines and different files. Help please!! Are you getting there by using menu, File, Print, Page Setup, Sheet Tab? "Martin" <anonymous@discussions.microsoft.com> wrote in message news:021101c3de76$0de69bb0$a001280a@phx.gbl... > I'm using Excel XP and I am unable to use the Print title > Repeat rows it is greyed out all the time. I have tried > several machines and different files. Help please!! Martin, This may be because you'v...

SRM 3.0 for SBS
Trying to "test" install CRM 3.0 for SBS on a SBS 2003 Premium R2 server. Installation gives option to install a sql Report Server or use existing. The one it tries to install is SQL 2000 and the screen says it is not compatible with other versions of SQL. So will the install allow you to connect to the SBS 2003's SQL 2005 Workgroup Report Server? Can;t find this answer anywhere! Thanks, KBB In answer to your first question, yes. You have to install SQL 2005 Reporting Services, then connect to this during CRM setup "KBB" <kbbnospam@dontspamme123.org> w...

Print Preview B&W
My print preview is also in B&W. Before installing Pub 07 it was fine. My color/grayscale icon is "non-clickable." ANY IDEAS? Yes. I assume you have an HP printer. this is a known bug and I reported it back in January. -- JoAnn Paules Microsoft MVP - Publisher How to ask a question http://support.microsoft.com/kb/555375 "Cybertyper" <Cybertyper@discussions.microsoft.com> wrote in message news:15E331BA-70BD-4C6B-98B9-9D69E2B81108@microsoft.com... > My print preview is also in B&W. Before installing Pub 07 it was fine. > My > color/grays...

Delete doesnt seem to work in SQL
Can someone possibly tell me why my delete statement in sql is not deleting anything. Table1: CurUser | Inventory John Stalls, Machine John Stalls, Pinball John Stalls, Golf Harry Simpson, Tires Harry Simpson, Boats //--------------------------------------------------------------// String FileName = System.IO.Directory.GetCurrentDirectory() + "\\test.db"; string ConnectionString = string.Format(@"Data Source={0};Version=3;New=False", FileName); SQLiteConnection conn = new SQLiteConnection(ConnectionString); conn.Open(); SQLiteCommand cmd = conn.C...

1099-MISC updates
Until this year, you have always provided the prior year 1099-MISC form updates as a seperate Hot Fix or seperate Download. This year, the 2006 1099-MISC form updates were included with the 2006 Year End Updates for Payroll. We do not use the payroll for Great Plains so downloading this update did not make sense for us. Also, the Payroll update required Service Pack 5 be installed, and we were on Service Pack 3. We have a great deal of custom Great Plains applications & there was no way I was going to update without having the time to test our custom applications just to get the...

Only printing header in Windows Live Mail
My canon printer has suddenly stopped printing the email content - only the header. Printing in word, excel etc not affected. Using W7 (+ Firefox if that relavent). Any suggestions please Does you have the same problem if you try printing from IE? WLM depends on IE for printing services. Take a look at this: Print and Print Preview May Display Only Text at Top of Page http://support.microsoft.com/?kbid=272518 It's a similar problem for a different version but the fix may apply. -- Mike - http://TechHelp.Santovec.us "Jem" <Jem@discussions.mic...

Printing changes layout
I am printing a booklet on Pub 2002 - when I print one page, it prints a page where everything has changed - resized, cut off the edges, etc. The worst part - when done printing, it goes back to normal view and it actually changes my layout to match what is printed - permanently. Any ideas what is going on? I printed on both my Lexmark and HP printers, does the same thing. I have Windows XP. Help! Thanks! Look at your page layout, has it changed? I know this will happen to me occasionally when I change printers. If I change back to the original printer the publication will revert...

How do I get a filter to "UPDATE" the rows selected?
Hey there! I am using Excel 2002 and I have a speadsheet that has prices in it. If I have the filter set to (the filter criteria is much more complex than this though) prices for items that cost >$5.00. Of I change an item to 4.59, it should not be seen. How can I get it to re-update the rows shown, with out releaseing the filter and resetting it? Is there something like refresh.filter or anything like that? Thanks Phil AFAIK you must release and reset but a macro with a worksheet_change event could trigger this for you so that it would happen automatically. -- Don Guillett SalesAid...

Auto-numbering of Opportunities
Hello all, I have one question: considering that Opportunities are not meant to be auto-numbered by design, what is the best practice to implement auto-numbering on such entities? Thank you in advance, Hrvoje Try this blog: http://blogs.msdn.com/crm/archive/2008/05/13/auto-numbers-in-microsoft-dynamics-crm.aspx Greetings Patrick H "Hrvoje Vrbanc" wrote: > Hello all, > > I have one question: considering that Opportunities are not meant to be > auto-numbered by design, what is the best practice to implement > auto-numbering on such entities? > > Tha...

Number of wsheets in an excel workbook???
hey guys, how many wsheets does one excel workbook have? I need to have about 66 - is that possible? Thanks. Maria -- mariasa ------------------------------------------------------------------------ mariasa's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31726 View this thread: http://www.excelforum.com/showthread.php?threadid=521656 Yes I have created a workbook with more sheets in it that that. obviously depending on the amount of data on each sheet your workbook could be getting quite large and possibly unmanageable! I guess you will have to try it! D...

Switching companies using SQL Passthrough
I have an application that uses SQL_Passthrough. As part of the code you must execute a statement that uses the appropriate database. The code looks like this: set SQL_Statements to "use MYDB"; status = SQL_Execute(SQL_connection, field SQL_Statements); This works fine, but my application can be used for any number of databases. At first, I modified it to use the Dex.ini file, which works. Here is the modification: dbname = Defaults_Read("SQLDB"); dbopencommand = "use " + dbname; set SQL_Statements to dbopencommand; status = SQL_Execute(SQL_connection,...

Printing checks #6
When I print to blank stock in the pay to the order of line the Account name is printed rather than the Payee name. This is the test version of Money which I would like to buy and use as it seem to have features which I like but the print glitch is a problem. Is there something I am not doing correctly or is this a "Feature?" Thanks, Dave. ...

how to write a number with 0
Hi how to write number starting with 0 for eg. i want to write number 021495566 i had tried but it gives 21495566 pls do the needfu -- NITESH ----------------------------------------------------------------------- NITESH G's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1759 View this thread: http://www.excelforum.com/showthread.php?threadid=48272 Hi Nitesh, Try using the custom format: 000000000 --- Regards, Norman "NITESH G" <NITESH.G.1y475y_1131346800.4507@excelforum-nospam.com> wrote in message news:NITESH.G.1y475y_1131346800.45...

One Record Per Page Printing-Access 2007
Report for daily appointments for senior transportation drivers. Report is grouped by Driver. Keep together is selected. Detail band: Keep together-Yes; Force New Page-Before Section; Can Grow-Yes; Can Shrink-Yes Goal: Report generated so each driver has his/her own appointments. Orientation: Landscape, paper size 8.5 x 14, column width: 13.5729"; column height: 0.3069" When previewing in Print Preview one record per page is appearing rather than all appointments for a driver for a given date. I have the bands as narrow as I can make them. I am missing somethi...

Jet SQL force write
Is there a way to force JET to write its buffer data to the MDB? I am using SQL statements to update the table and want to make sure the data is written. On Sat, 03 Nov 2007 14:31:41 -0700, Pete <pete.beatty@gmail.com> wrote: DBEngine.Idle dbFreeLocks -Tom. >Is there a way to force JET to write its buffer data to the MDB? I am >using SQL statements to update the table and want to make sure the >data is written. ...

Portrait only printing in Excel
I have an interesting problem. I have a workstation (Dell Pentium IV XP PRO) that has Office 2k on it. When I/we try to print to a networked printer, the document will only print in portrait no matter what changes I make to page settings or printer settings. We are on Active Directory, so as long as the documents are submitted from a different computer, it will print fine. Any suggestions? This could be a complete waste of your time, but the first thing I'd try is to reinstall the printer driver. (maybe even get a fresh driver from the printer manufacturer's web site???) Howa...

how print page out of pages, w/o header?
Thanks for any help. I have a workbook, with a header for some columns, but the last page of the workbook (the lowest rows of worksheet), don't need the headers, but I don't see any functions for stopping headers on a certain page. Is this possible? Not a big deal, it's just a row of labels with no info underneath them, but I would like to get rid of this header for this one last page if possible. Of course, I could change from headers to putting it into the worksheet itself, but I was wondering if I could do it this way. Thanks again. Check your print range in File | Page ...

printing row headings
I'd like to print the row headings on each page without printing the column headings. the rat wrote: > I'd like to print the row headings on each page without printing the column > headings. To: The Rat File | Page Setup | Sheet Tab On this tab, for "Print Area" select all but the row with the headings for "Rows to repeat at top" select the row with the headings Check results with "Print Preview" button Texas Handly ...

Bold text appears in print preview but does not print bold
I am working on a menu in Publisher. In print preview, all text in bold appears bold, but when I actually print, portions of the bold text does not print in bold. This problem applies to all text within specific text boxes. For some text boxes the bold prints and for some it doesn't. Any suggestions would be enormously appreciated. What version Publisher? Don't know if that makes a difference however. If you look in the font directory in the control panel you will see a separate font for bold, italic, condensed and so on for fonts like Times New Roman and Arial. If you use ...

Restricting SubForm to One Record Entry Per Parent: Access 2007
Hello All, I have a one-to-one relationship between two table. I have successfully added the subform, but I get the option to add additional child records in the Datasheet View. This means that when a user tabs to the next row and enters data they get an error--and they should. Question: How do I restrict the subform to show only one row in Datasheet View? I hope I was clear. Feel free to ask for clarification. Thank you in advance for your assistance. Alcide, You have two options here. If the records are already added to the source of the subform you could set the &qu...

Barcode printing to serial port
I have an old thermal label printer and its connected via a serial port how do i change the font on a serial port or send an object to the printer so that the barcode will print ...

Tab Control Records
I have placed a Tab Control on my form. The Form is linked to a master table that assignes an ID. I have 6 tabs and each tab has a subform (The subforms link to the Score Table and the score table links back to the Master Table) with option buttons for the user to choose. The problem I am having is that when the user clicks on the next tab it creats a new record in the Score Table. I would like to have one record per Master table ID. any help would be greatful. Vincent ...

Unable to update
Outlook 2002 / XP I know this appears to be a sproadic problem for many but I can't find the solution. Error message - 'Unable to update public free/busy data.' Trying not to gum up this ng, I have searched MS KB, web sites, and this ng history but the solutions I have found do not help. These include using the clear(?) switch when opening Outlook Checking the Calendar Options|Free/Busy options (no check marks) I found one reference to registry changes but no guidance This problem did not occur until I switched the Default pst (I use 2) used when opening Outlook. Any ideas ...

Want to see only specific number of records on a form
Hi I have a form with default view as "Continuous Forms". When I openthis form I only want to see the records equal to Recordcount i.e. ifI have 3 records I should only see three records. I dont want to seean extra one record which is blank. The easy way is simply to set the allow additions to "no".(you will still be able to add records via a code, or perhaps a "add" button you provide.Or, perhaps you don't even need add record ability...Anyway...give the above a try..it will remove that extra "new record" entry....-- Albert D. Kallal (Access MVP)...

Printing Structures
Does Anyone Have A Solution For Printing The Structure Of A Table To The Printer Or Even Text File...dbase used to just let you list structure to print... On Wed, 2 Jun 2010 07:41:01 -0700, dmills <dmills@discussions.microsoft.com> wrote: Check out Database Documenter in the menu. -Tom. Microsoft Access MVP >Does Anyone Have A Solution For Printing The Structure Of A Table To The >Printer Or Even Text File...dbase used to just let you list structure to >print... ...