Multiple Batch Insert

Question

Multiple Batch Insert

James Tech

Right there with Babe

Points: 762
More actions
February 15, 2009 at 8:14 pm

#206608

We have an application that generates batch files. Now each batch file contains a header and series of records and each record could be for any table.
The construction of batch files is not in my control, so it is possible that it will be on different file format (XML,Text,SIL, and etc.) and it will be generated anytime.
The batch file could be in any size but most likely it will go like about 20 MB so far and it can go higher...
My job is to insert the data safely and speedy from each batch file to a different tables on our maintenance database.
Please advise as to how I would achieve this. I need to design a detailed implementation plan.
What I have in my box is:
- MS Visual Studio 2008
- MS SQL 2005 Express Edition
- Running on Windows XP

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 1

[font="Verdana"]Please tell me you are not designing a high volume data solution using SQL Server Express? I believe there's a limitation on the size of database it supports, for starters.

Upgrade your installation to the developer version. Then have a serious look at SSIS (SQL Server Integration Services). It should allow you to do what you need.

[/font]

James Tech Right there with Babe Points: 762 More actions · Answer 2

I know that the best solution to this kind of dilemma is SSIS but upper personnel is a bit strict in terms of licensing because it cost a lot of money. I was hoping if we could get other ideas/solution without using SSIS and can show technical advantage over it.

Any alternative ideas?

Please help. Thanks.

er.kalidass SSC Veteran Points: 281 More actions · Answer 3

I believe the SQL Express would allow maximum storage upto 4GB.. For batch execution for ur case (multiple table and size>10MB ) needs loads of Transactions.. This will grow the Database and Transaction rapidly .. one solution is to upgrade ur express edition to standard edition.

Thanks

dass

James Tech Right there with Babe Points: 762 More actions · Answer 4

er.kalidass (2/16/2009)
I believe the SQL Express would allow maximum storage upto 4GB.. For batch execution for ur case (multiple table and size>10MB ) needs loads of Transactions.. This will grow the Database and Transaction rapidly .. one solution is to upgrade ur express edition to standard edition.
Thanks
dass

So does it mean that it has no alternative solution for this kind of situation? Because no matter how I defend the matter of upgrading express to standard edition, still I can't get it through because of the budget and I hope its understandable. I was hoping to seek for answers as an alternative solution in this kind of situation and I hope someone can provide me in this forum.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

Since you cannot upgrade to a higher edition, you are choices are limited of using T-SQL functions such as BULK INSERT, OPENROWSET, OPENXML etc...

--Ramesh

er.kalidass SSC Veteran Points: 281 More actions · Answer 6

U can reduce the block time and transaction Log by using single insert statement in Batch instead of multiple insert statements. u can modify the batch such that it reduce to single insert for 1 table. suppose u have 5 tables involved in that batch u should have only 5 insert statements. this will reduce blocking and Log rapidly becoz for multiple insert statements SQL have to make implicit transaction for each batches. this will limit the speed

Let me know u have any issues with this..

Also do u have any explicit transactions in the batch?.

thanks

Kalidass

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 7

James (2/16/2009)
So does it mean that it has no alternative solution for this kind of situation? Because no matter how I defend the matter of upgrading express to standard edition, still I can't get it through because of the budget and I hope its understandable. I was hoping to seek for answers as an alternative solution in this kind of situation and I hope someone can provide me in this forum.

[font="Verdana"]It sounds to me that the real issue here is justifying the upgrade, not "what is the best way..."

The best way is SSIS. It's not the only way.

You can use bcp, or bulk insert, or OPENROWSET, or OPENQUERY. SQL Server Books Online (yes, you can download that for free) has reasonable discussions on how to use all of these.

Of course, you may also be able to do some estimation on how you will need to juggle your database sizes and the limitations of SQL Server Express (including no scheduled jobs!) and how much time it will take you to work around these, then put your hourly rate against your time and compare that to the cost of a license.

You can also look at the practicality of basing a solution like this on SQL Server Express, and show whether they will actually be able to do what they want. Because if they can't do what they want, then they need to justify the license costs.

Good luck!

[/font]

James Tech Right there with Babe Points: 762 More actions · Answer 8

er.kalidass (2/16/2009)
U can reduce the block time and transaction Log by using single insert statement in Batch instead of multiple insert statements. u can modify the batch such that it reduce to single insert for 1 table. suppose u have 5 tables involved in that batch u should have only 5 insert statements. this will reduce blocking and Log rapidly becoz for multiple insert statements SQL have to make implicit transaction for each batches. this will limit the speed
Let me know u have any issues with this..
Also do u have any explicit transactions in the batch?.
thanks
Kalidass

Thanks for the input Kalidass. No, we do not have explicit transactions in the batch. What do you suggest is best if I were in command of the format of each batch file? and what do you mean by block time?

If I use BULK INSERT, is there a way that I can insert it to Multiple table at one time? or how do I manipulate the data in one batch file to be inserted to multiple tables?

Thanks for the help.

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 9

James (2/16/2009)
If I use BULK INSERT, is there a way that I can insert it to Multiple table at one time? or how do I manipulate the data in one batch file to be inserted to multiple tables?

[font="Verdana"]The simple answer is "no". You can do this with SSIS. You can't do it with any other approach that I am aware of.

How most people get around this is to either load the same file multiple times (which then has the issue of how do you match between parent and child relationships) or load the file as-is into a loading table, then create a stored procedure or equivalent to unpack the data from the load table into the multiple tables (which then has the issue of generating more logging).

I did have a discussion on here recently about using OPENROWSET() to try and reduce logging, but that was a variant of the first approach (loading the file multiple times.)

Another approach would be to look at some of the opensource ETL/ELT systems. I haven't looked at these, so I can't recommend any. However, they may do what you want.

[/font]

James Tech Right there with Babe Points: 762 More actions · Answer 10

Thank you for the input Bruce, this is really helpful. I will try to research as much as I can and hope it will lead me to right direction.

If somebody has some more detailed ideas regarding the matter, please just reply to this thread.

Thank you so much for the help.