Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Executing Insert statements in a batch / group Expand / Collapse
Author
Message
Posted Monday, December 10, 2012 12:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 7:35 AM
Points: 55, Visits: 83
Dear All,

Hi! I need to generate Insert scripts of more than 1 million records & execute the same on another server. The normal frequency is weekly but, but sometimes I have to run it twice / thrice in a week.

I need to know that how we do make batch of these insert statements so that instead of executing individual statements we can execute group of Insert statements. The batch/group can be of 2000 rows or 5000 rows to improve the performance.

I also need to know that will it increase the performance as we are inserting multiple records in a single batch?
Post #1394757
Posted Tuesday, December 11, 2012 2:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 2:55 AM
Points: 1,101, Visits: 5,280
You can try BCP. It has options to inset rows in batches.

BCP is known to be faster than other methods.

Post #1394957
Posted Tuesday, December 11, 2012 2:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 10:02 PM
Points: 60, Visits: 79
I would suggest to create and run the ssis package for loading the data to another server.If that is the requirement.
Post #1395322
Posted Tuesday, December 11, 2012 2:41 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:08 PM
Points: 132, Visits: 848
Last I knew BCP is incompatible with FULL RECOVERY MODE and I don't fly without a parachute.

We got a huge performance increase by sending a large "multi-row" XML string into a stored procedure, parsing it into a table variable and inserting the rows in one shot. It is messy but fast and you get to keep your reliable backups.
Post #1395330
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse