/*------------------------------------------------------------------------------------------------------------

--how_to_input_text_file_to_sql_table_using_bulk_insert_0033.sql


How do I input text file to SQL server table?

This is a common request when working with databases.  In SQL server you have many options to load text or csv data from a file into a 
SQL table.  The obvious choice is to use SSIS or Import Export wizard to do just that.  What if you wanted to use and more automated approach? 
Using BCP or Bulk Insert commands can solve this problem.

Today we are going to look at Bulk Insert command which can easily be programmed into your store procedures for an automated load of data.  
In our scenario we have a text file containing customer data that we would like to put into the customer_data table.  This text file can 
be fined on our website at this location:

http://www.learningsqlserver2008.com/sql-server-2008/files/Customers_data.txt


The official syntax for Bulk Insert from MSDN is as follows:



BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
        ( 
   [ [ , ] BATCHSIZE = batch_size ] 
      [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
      [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] KEEPIDENTITY ] 
   [ [ , ] KEEPNULLS ] 
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
   [ [ , ] LASTROW = last_row ] 
   [ [ , ] MAXERRORS = max_errors ] 
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] 
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
   [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
   [ [ , ] TABLOCK ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
        )] 


In our example, we are going to first create a table then load up the data using bulk insert command and finally display the data.
Notice the follwowing:

-Fields are seperated by ~
-using FIRSTROW=2 as first row has field names

------------------------------------------------------------------------------------------------------------*/



USE dba
GO


--Create Table

CREATE TABLE [dbo].[Customer_Data](
	[CustomerID] [nchar](5) NOT NULL,
	[CompanyName] [nvarchar](40) NOT NULL,
	[ContactName] [nvarchar](30) NULL,
	[ContactTitle] [nvarchar](30) NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[Phone] [nvarchar](24) NULL,
	[Fax] [nvarchar](24) NULL
) ON [PRIMARY]



--Load data


BULK INSERT Customer_Data
FROM 'C:\temp\Customers_data.txt' --use your drive location
WITH 
(FIELDTERMINATOR='~',
ROWTERMINATOR='\n',
FIRSTROW=2
)


--display the data


SELECT * FROM Customer_Data



/*
   

Compiled by http://www.learningsqlserver2008.com/, Kash Data Consulting LLC 

Disclaimer:

All sample code and scripts are compiled by Kash Data Consulting LLC for illustrative purposes only. Kash Data Consulting LLC, 
therefore, cannot guarantee or imply reliability, or function of this code or scripts.

All code contained herein are provided to you "AS IS" without any warranties of any kind. Please test all code and 
scripts in test environment before deployment in production systems.

*/