SQL Server: How to import data file into a table?

Ideas

1. Create a setting table having temporary import table name, format file name, stored proc name, upload folder name
2. Create Windows service to run at a certain of time, this service will look at the upload folder and import the data, after import the data, it will call stored proc to deal with this kind of data

Use BULK INSERT

• Create format file
  [sql]
  –Create format file from prompt
  C:\Users\pauln>bcp PaulDB.dbo.[Table Name] format nul -c -f C:\format.fmt -t, -T -S [Server Name]
  [/sql]
• Run the following query
  [sql]BULK INSERT [database name] ..[Table Name] FROM ‘[data file path]’ WITH (FIRSTROW = [interger number], MAXERRORS = 0, FORMATFILE='[format file path]’)[/sql]

Use Microsoft SQL Server Management Studio

• Log in to your database using SQL Server Management Studio.
• Right click the database and select Tasks -> Import Data…
• Click the Next > button.
• For Data Source, select Flat File Source. Then use the Browse button to select the CSV file. Spend some time configuring the data import before clicking the Next > button.
• For Destination, select the correct database provider (e.g. for SQL Server 2012, you can use SQL Server Native Client 11.0). Enter the Server name; check Use SQL Server Authentication, enter the User name, Password, and Database before clicking the Next > button.
• In the Select Source Tables and Views window, you can Edit Mappings before clicking the Next > button.
• Check Run immediately and click the Next > button.
• Click the Finish button to run the package.

Error: SSIS Text was truncated with status value 4
[html]
At the step of “Choose a Data Source”, select Advanced and change “OutputColumnWidth” with bigger value
[/html]

What is the difference between Bulk Copy and BCP

• BULK INSERT is a SQL command and BCP is a separate utility outside SSMS and you need to run BCP from DOS prompt (command prompt).
• BULK INSERT can copy data from flat file to SQL Server’s table whereas BCP is for import and export both. You can copy data from flat file to SQL Server and from SQL Server to Flat file with the help of BCP.
• In BULK INSERT there is no support for down level version input whereas it is possible in BCP.
• BCP has less parsing efforts and cost than BULK INSERT.
• You can use INOUTQUERYOUT argument with BCP to import, export and conditional import and export which in not possible in BULK INSERT.
• Apart from above differences, both are almost same and give almost same performance moreover, both are single threaded, no parallel operation allowed.

ERROR: ODBC SQL Server Driver – SQL Server – Could not find stored procedure ‘sp_dboption’.

This is because ‘sp_dboption’ has been removed since SQL Server 2012
When using BULKCOPY the following option needs to be set ON:
[sql]
EXEC sp_dboption ‘BR’, ‘select into/bulkcopy’, ‘TRUE’;
–EXEC sp_dboption ‘BR’, ‘select into/bulkcopy’, ‘FALSE’;
[/sql]
The replacement for this is ALTER DATABASE command
[sql]
ALTER DATABASE BR SET RECOVERY BULK_LOGGED
–ALTER DATABASE BR SET RECOVERY SIMPLE
[/sql]