• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

Insert into sql table by batches

Hi,
Can someone give me some idea how to perform batch insert in sql2005 by using t-sql ? My query below doesnt run in batch if I try with "top" ? will that work ? the date_time below contain aroun 30 millions of records, that is why I plan to run the insert per 100000 batches

      INSERT into  DBTestA.dbo.LoggingA
      SELECT *
      FROM DBProdA.dbo.LoggingA with (nolock)  where date_time>'2010-12-01 23:59:59.999'
 
     and date_time>'2010-12-31 23:59:59.999'

   
0
motioneye
Asked:
motioneye
2 Solutions
 
derekkrommCommented:
You may want to look at SSIS to handle this type of functionality.

As a side-note, if you're trying to insert just Dec 2010, the last line should be <= rather than >
0
 
motioneyeAuthor Commented:
How about if I use bcp ? will that help ?
0
 
derekkrommCommented:
It may, but I won't be much help there as I don't have any experience with it.
0
 
deightonCommented:
is there a unique key there?  if you order by key, then you can select the top 10000 with a key greater than the greatest key in the DBTestA.dbo.LoggingA table
0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
using the UniqueKey as my UniqueKey I would suggest the folowing


INSERT into  DBTestA.dbo.LoggingA
      SELECT top 100000 *
      FROM DBProdA.dbo.LoggingA
     with (nolock)  
     where date_time>'2010-12-01 23:59:59.999'
     and date_time>'2010-12-31 23:59:59.999'
     and UniqueKey not in (select UniqueKey from  DBTestA.dbo.LoggingA )

Every Run You insert a Batch of 100000 Records That are not already in the Logging Tabel

regards

Poor Beggar

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now