Query Status - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Query Status

Author

Topic

indupriyav
Starting Member

13 Posts

Posted - 2012-05-15 : 07:27:17

There is a job which is inserting 4000000 records in a table. Its been executing for 30 hours. Usually this particular table takes 20 to 27 hours.

Is there any stored procedure to know how many records have been inserted into the table.

I can issue a select statement. But Im afraid it will slow down the huge insert.

Thanks!!!

indupriyav

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-15 : 07:37:31

You can use:

SELECT
	row_count
FROM
	sys.dm_db_partition_stats
WHERE
	OBJECT_NAME(OBJECT_ID) = 'YourTableName'

indupriyav
Starting Member

13 Posts

Posted - 2012-05-15 : 07:45:30

This will give all records in the table.

Im inserting only for a particular month which has 4000000 rows. So i want to know how much of 4000000 rows have been inserted.

indupriyav

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-15 : 08:24:06

Which is your clustered index key?
It should be over an increasing column, such as a datetime or similar column. If the clustered key is something else, moving around millions of rows take a LONG time.
Also, have you tried to disable the indexes on the table before inserting?

Simply said, we know way to little about your configuration to even have an estimated guess.

N 56°04'39.26"
E 12°55'05.63"

Sachin.Nand

2937 Posts

Posted - 2012-05-15 : 13:49:21

4000000 records in one go ???

What happened to batch inserts.Create a view which selects say 10000 records and schedule an insert statement which executes say every 5 minutes.


Create view My_View
as
Select top 10000 from basetable where somecolumn not exists(Select somecolumn from targettable) order by somecolumn

Insert into TargetTable
Select * from My_View

After Monday and Tuesday even the calendar says W T F ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources