SQL Server – How many rows have been inserted into a table while a query is running?

I’m running a cleansing script that has 10 unions and results in, more or less, 1.2 billion rows (I’m re-executing this script including some missing fields, so I know and expect this number at the end of the execution).

I’m inserting this cleaned data into a new table (X). I’ve made some researches and I found this query from MS site:

    -- Do not lock anything, and do not get held up by any locks.
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    -- Quickly get row counts.
    SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
        , OBJECT_NAME(p.object_id) AS [Table]
        , i.name AS [Index]
        , p.partition_number
        , p.rows AS [Row Count]
        , i.type_desc AS [Index Type]
    FROM sys.partitions p
    INNER JOIN sys.indexes i ON p.object_id = i.object_id
                             AND p.index_id = i.index_id
    WHERE OBJECT_SCHEMA_NAME(p.object_id) != 'sys'
    ORDER BY [Schema], [Table], [Index]

And I found out that my Row Count is 0 and Index Type is HEAP for this X table. My query is running around 7 hours. I think it could be done in a day. I also tried to select top 1, select count(*), count(1), writing with (nolock) and none of these queries ran.

Is this right? SQL should have loaded some data into this new table, right? Anyone has any idea to help me figure it out if something went wrong during this query execution?

I just want to know if this query is really working, and I thought of looking data from this new table.

——edit——–

My select statement is a cleansing from a table named BSEG (from SAP). This table has more than 100 columns.

It is like this:

Select BUKRS, convert(datetime, BUDAT), convert(decimal(18,2), field3)...
into table a
from BSEG1

union

Select BUKRS, convert(datetime, BUDAT), convert(decimal(18,2), field3)...
from BSEG2

union (...)

I imported these data from a txt file (nvarchar 4000 – BCP). Maybe this could be slowing down the process?

Answer

why you dont split your select into in 10 insert into?

first create the result table:

Select BUKRS, convert(datetime, BUDAT), convert(decimal(18,2), field3)...
into MyTable
from BSEG1
WHERE 1=0 -- this will do the trick

next

insert into MyTable
Select BUKRS, convert(datetime, BUDAT), convert(decimal(18,2), field3)...
from BSEG1

PRINT @@ROWCOUNT

insert into MyTable
Select BUKRS, convert(datetime, BUDAT), convert(decimal(18,2), field3)...
from BSEG2

PRINT @@ROWCOUNT

insert into (...)

in this way you should simply read the records in MyTable using:

select count(*) from MyTable with (nolock)

you can also put all the insert statements into a stored procedure if you like.