SQL ServerʹÓÃBulk Insert°ÑÒ»¸öÎı¾µ¼Èëµ½Êý¾Ý¿â
This is very common request recently – How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.
CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.
Create TestTable
USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO
Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202
Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.
BULK
INSERT CSVTest
from 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
from CSVTest
GO
--Drop the table to clean up database.
SELECT *
from CSVTest
GO
Ïà¹ØÎĵµ£º
sql server2000ÖÐʹÓÃconvertÀ´È¡µÃdatetimeÊý¾ÝÀàÐÍÑùʽ£¨È«£©
ÈÕÆÚÊý¾Ý¸ñʽµÄ´¦Àí£¬Á½¸öʾÀý£º
CONVERT(varchar(16), ʱ¼äÒ», 20) ½á¹û£º2007-02-01 08:02/*ʱ¼äÒ»°ãΪgetdate()º¯Êý»òÊý¾Ý±íÀïµÄ×Ö¶Î*/
CONVERT(varchar(10), ʱ¼äÒ», 23) ½á¹û£º2007-02-01 /*varchar(10)±íʾÈÕÆÚÊä³öµÄ¸ñʽ£¬Èç¹û²»¹»³¤»á·¢Éú½ØÈ¡*/ ......
[C#]
public void RunSqlTransaction(string myConnString)
{
SqlConnection myConnection = new SqlConnection(myConnString);
myConnection.Open();
SqlCommand myCommand = myConnection.CreateCommand();
SqlTransaction m ......
ÎÒÃÇÔÚ±àдMISϵͳºÍWebÓ¦ÓóÌÐòµÈϵͳʱ£¬¶¼Éæ¼°µ½ÓëÊý¾Ý¿âµÄ½»»¥£¬Èç¹ûÊý¾Ý¿âÖÐÊý¾ÝÁ¿ºÜ´óµÄ»°£¬Ò»´Î¼ìË÷ËùÓеļǼ£¬»áÕ¼ÓÃϵͳºÜ´óµÄ×ÊÔ´£¬Òò´ËÎÒÃdz£³£²ÉÓã¬ÐèÒª¶àÉÙÊý¾Ý¾ÍÖ»´ÓÊý¾Ý¿âÖÐÈ¡¶àÉÙÌõ¼Ç¼£¬¼´²ÉÓ÷ÖÒ³Óï¾ä¡£¸ù¾Ý×Ô¼ºÊ¹ÓùýµÄÄÚÈÝ£¬°Ñ³£¼ûÊý¾Ý¿âSQL Server,OracleºÍMySQLµÄ·ÖÒ³Óï¾ä£¬´ÓÊý¾Ý¿â±íÖÐµÄµÚ ......
參¿¼: http://brightsky006.blog.163.com/blog/static/22583668200962195059485/
°ÑSQL 2005µÄ±¸·ÝÎļþµ¼Èëµ½SQL 2000
ѧϰÕ䲨 2009-07-21 09:50 ÔĶÁ616 ÆÀÂÛ0
×ֺţº ´ó´ó ÖÐÖРСС
µçÄÔÉϰ²×°ÁËSQL Server 2005£¬ÏÖÔÚÏë»»»ØSQL2000À´£¬ ......
ÏÖÔÚ´ó¶àÊýÃèÊöSQL Server 2005ÐÂÌØÐÔµÄÎÄÕ¶¼¹Ø×¢ÓÚ»ª¶ø²»ÊµµÄÌØÐÔ£¬ÈçSQLCLR»òXMLÊý¾ÝÀàÐÍ£¬¶ø¶ÔÐí¶àºÜºÃµÄÀϵÄT-SQLÓïÑԵĸĽøÃ»Óеõ½Ó¦ÓеĹØ×¢¡£ÎÒÔø¾´ÓÐí¶àDBA¿ÚÖÐÌýµ½ÁîËûÃǸüÐ˷ܵÄÊÇ¿´µ½T-SQLÓïÑԵĸĽø,¶ø²»ÊÇÄÇЩгöÏֺͷ¢²¼µÄ¹¦ÄÜ¡£¶ÔÓÚSQLÓïÑÔµÄÒ»¸öºÜÓÐÓõÄʵ¼Ê¸Ä½øÊÇOUTPUT×Ӿ䣬ËüÔÊÐí²éѯһ¸öÊý¾ ......