什么是从平面文件中插入数百万条记录到SQL Server的陷阱？ [英] What are the pitfalls of inserting millions of records into SQL Server from flat file?

问题描述

我即将启程编写Windows窗体应用程序将打开一个txt文件，是管道分隔，大小约230 MB。那么这个程序将插入这个数据到一个SQL Server 2005数据库（显然，这需要迅速发生）。我使用C＃3.0和为这个项目.NET 3.5。

我并不是在这里要求的应用程序，只是一些公共意见和潜在的缺陷提出建议。从我所收集到的SQL批量复制是一个先决条件的网站，有什么我应该考虑一下（我认为这只是一个形式的应用程序打开txt文件将是一个很大的努力，也许把它拆成BLOB数据？）<。 / p>

感谢您，我会编辑的问题为清楚起见，如果有人需要它。

解决方案

你必须写一个WinForms应用程序？这可能是更容易，更快地使用SSIS。有可用的特别大容量插入任务一些内置的任务。

另外，值得一试平面文件在SQL Server中批量导入方法速度对比2005年

更新：如果你是新来的SSIS，检查了这些网站，让你在快车道。 1） SSIS控制流基础 2）的入门与SQL Server集成服务

这是另外如何：上的导入Excel文件到SQL 2005 。

I am about to start on a journey writing a windows forms application that will open a txt file that is pipe delimited and about 230 mb in size. This app will then insert this data into a sql server 2005 database (obviously this needs to happen swiftly). I am using c# 3.0 and .net 3.5 for this project.

I am not asking for the app, just some communal advise here and potential pitfalls advise. From the site I have gathered that SQL bulk copy is a prerequisite, is there anything I should think about (I think that just opening the txt file with a forms app will be a large endeavor; maybe break it into blob data?).

Thank you, and I will edit the question for clarity if anyone needs it.

解决方案

Do you have to write a winforms app? It might be much easier and faster to use SSIS. There are some built-in tasks available especially Bulk Insert task.

Also, worth checking Flat File Bulk Import methods speed comparison in SQL Server 2005.

Update: If you are new to SSIS, check out some of these sites to get you on fast track. 1) SSIS Control Flow Basics 2) Getting Started with SQL Server Integration Services

This is another How to: on importing Excel file into SQL 2005.

这篇关于什么是从平面文件中插入数百万条记录到SQL Server的陷阱？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！