Importing a large csv into sql

use SSIS, or Import\Export wizard, they are faster although they really do a bulk insert.

Try anyone of these two articles,
http://blog.sqlauthority.com/2011/05/12/sql-server-import-csv-file-into-database-table-using-ssis/
or
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

Also, there other constraints like impact the performance like, the number of indexes in the table, because they also need to be updated.

If you are using SQL Server Express your database size (not the CSV file size) cannot grow beyond 2gb. Given you've got a large amount of data, you should probably consider a setup where you can split the file and import them into multiple "databases" in Express or move to MySQL depending on your flexibility.

How large is your database file now?

I just created it so this is the first thing going into it.  I am trying some of the suggestions. Thank you all I will let you know as soon as I test everything.

I am doing the business Intelligence suggestion but I am getting a "Cannot convert between unicode and non-unicode string data types" error  Any suggestions?

I have tried the Bulk insert and I am getting the following error:

Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes.

I also get the message I stated earlier when I use the business intelligence Development studio.  

Any clues?

Here is an article about LOB data types:

http://www.sqlmag.com/article/sql-server/sql-server-2008-lob-data-types

I already have them set as nvarchar(500).  

I ran the package in the bussiness application and I get this messages as errors:


Error: 0xC02020A1 at Data Flow Task, Flat File Source [1]: Data conversion failed. The data conversion for column "Authorized Official Telephone Number" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, Flat File Source [1]: The "output column "Authorized Official Telephone Number" (1967)" failed because truncation occurred, and the truncation row disposition on "output column "Authorized Official Telephone Number" (1967)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Data Flow Task, Flat File Source [1]: An error occurred while processing file "C:\npidata.txt" on data row 2.
Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

You will have to go into the properties of the columns for the destination in the database in the wizard and change them to match exactly the type and size in the file.

Starting with 2005 the import wizards do not accept maximum size and general type for the columns.

Truncated means the table column was not long enough -- increase the length on the table (I'd just change them all to nvarchar(4000), the max w/o using max itself).

SQL doesn't know the exact length of all columns in the input so can't possibly require that you set that.

I changed everything to Nvarchar(4000) and still not working.  I am trying to upload the NPI file from the government. Maybe I should just write an app that copies it and puts it into my temp table.