sql server bulk insert

i want to insert csv file with special character how can csv file to sql server database wi开发者_如何学Cth special character

Sql bulk insert

You can use the BULK INSERT T-SQL command (it also has a command line version):

http://msdn.microsoft.com/de-de/library/ms188365.aspx

Besides SQL BULK insert, you could also check out the SQL Server Integration Services, if you have to do this just once.

In SQL Server Management Studio, go to the Object Explorer, find your database, right click on it, and then choose "Tasks > Import Data". This brings up the SSIS wizard, which allows you to pick a source (your CSV file), a target (your SQL Server database+table), and it supports picking just certain fields, renaming them and much more.

For "do it once" operations, this is quite a nice and useful wizard.

If you are programming in .NET you could use CsvHelper and the BulkInserter class that I wrote.

Use CsvHelper to read the csv lines as CLR objects and BulkInserter will pump them into the database for you. Here's the BulkInserter usage.

You can either create a procedure in SQL which can be called every time you want to bulk insert or you can hardcode it.

The hardcoded BULK INSERT would be:

BULK   
INSERT *TableName*   
FROM *'FilePath'*   
WITH   
(   
FIELDTERMINATOR = ',',   
ROWTERMINATOR = '\n',   
CODEPAGE = 'ACP'   
)   
GO

I have used this many times. I think it is better to use it as a procedure if you are going to use it quite a lot, but this means that you will need to create the procedure and call it in each query.

Sql Bulk insert