Post reply

to find count of rows

  • March 30, 2010 at 2:44 am

    #231268

    Hello there,

    Looking for some break through on the below scenario.

    I am inserting rows into a table using below statement.

    Select * into newTable from oldTable (nolock)

    the oldTable is having approx a million records, and the above cmd is runing for a long time.

    is there a way to find out how many records have been inserted into newTable so far.

    as we all knew that neither

    select count(*) from newTable (nolock)

    nor

    EXEC sp_spaceused 'newTable'

    works in this case.

    Thanks

    Naresh

  • March 30, 2010 at 3:17 am

    #1142030

    this may not be much help, but I would use an SSIS package to do what you are doing,

    are you setting up a staging table (create table) from a source table and then putting data in it,

    i have attached a package that may help, try inserting your example to match this one, have attached a couple of examples for you, hope this helps

  • March 30, 2010 at 10:10 am

    #1142412

    Naresh

    you could try

    declare @rows int

    select * into dbo.users_new from dbo.users

    select @rows = @@rowcount

    Print 'Rows copied = ' + cast(@rows as varchar(10))

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • March 30, 2010 at 11:44 am

    #1142509

    naresh1253 (3/30/2010)

    Select * into newTable from oldTable (nolock)

    If the database is currently using the FULL recovery model, the whole operation will be fully logged.

    Switch the database into BULK_LOGGED mode before you issue the statement, to take advantage of minimal logging.

    It will be much, much faster 🙂

    You should take a log backup immediately before switching the recovery model, and again immediately after switching back to FULL.

    Use TABLOCK instead of NOLOCK, and use the WITH (TABLOCK) form while you are at it.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • March 31, 2010 at 9:57 am

    #1143225

    Oh..thats a better approach. Thanks for the advice.

  • March 31, 2010 at 9:59 am

    #1143229

    naresh1253 (3/31/2010)

    Oh..thats a better approach. Thanks for the advice.

    No worries.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply