Bulk insert into production table help

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

SQL Server 2008 Forums

Transact-SQL (2008)

Bulk insert into production table help

Author

Topic

learntsql

524 Posts

Posted - 2012-04-19 : 01:54:20

Hi All,

I have to load every day around 1 million records from staging to live table.
What is the process and steps should i take to load the data.?
TIA.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-19 : 09:07:21

Pseudo-code:


declare @transtime datetime
while exists (select 1 from staging.dbo.mytable where transferred is null)
  begin
    set @transtime = getdate()
    begin try
      update top 100000 staging.dbo.mytable set transferred = @transtime where transferred is null

      insert into live.dbo.mytable (...)
      select ... from staging.dbo.mytable where transferred = @transtime
    end try
    begin catch
      ...
    end catch
    waitfor delay '00:00:05'
  end

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-19 : 10:19:45

and then wrap that puppy in a transaction and I usually do CHECKPOINT to write the stuff to disk.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

learntsql

524 Posts

Posted - 2012-04-20 : 00:55:29

Thanks for your reply.

For inserting if it takes 5-10 mns should all the users wait?
or is there any other way to do it.
My concern is if it has huge number of records and users shuld get quick reply.
What approach should we follow?
TIA.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-20 : 02:42:56

This is exactly why you should do this in a loop, x records at a time, and wait x seconds between each iteration so that other transactions have time to do their stuff as well. You should never have your users wait 5-10 minutes.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-20 : 03:59:24

1 millions rows is nothing.

MERGE	Production.dbo.MyTable AS tgt
USING	(
		SELECT	KeyCol,
			Col1,
			Col2,
			GETDATE() AS Today
		FROM	Staging.dbo.MyTable
	) AS src ON src.KeyCol = tgt.KeyCol
WHEN	MATCHED	AND tgt.LastChangeDate < src.Today
		THEN	UPDATE
			SET	tgt.Col1 = src.Col1,
				tgt.Col2 = src.Col2
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					KeyCol,
					Col1,
					Col2
				)
			VALUES	(
					src.KeyCol,
					src.Col1,
					src.Col2)
				);

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-20 : 04:04:46

quote:
Originally posted by SwePeso

1 millions rows is nothing.

Agreed (generally, we know nothing about where this staging database is for example or the HW specs), but I'd still advocate doing this in batches. The size of each batch however is up to the OP to find out.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-20 : 04:08:44

I am not so sure about that. What if the users alters the data between the loops?
For the merge, I would also add WITH (SERIALIZABLE, UPDLOCK) to be really sure nothing interferes the data transfer from staging to production.

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-20 : 04:19:02

That kind of defies the concept of a staging database doesn't it?

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-20 : 06:03:07

Why not?
It's only the cleansed and rinsed data from staging area to production area that is transferred in the MERGE statement.

The staging area is for washing the data before you store it in the production area.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources