bulk sql to sql, sql to csv, csv to sql, sql to flat, flat to sql, excel to sql, sql to excel copy using DTS (Data Transformation Services)

Using DTS wizard we can perform operation easily.
Path "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTSWizard.exe"

In sql server 2008
start>programs>Microsoft SQL Server 2008>Import and Export Data (32-bit)





In c# we can do it easily


<%@ Import Namespace="System.Data"%>

<%@ Import Namespace="System.Data.Odbc" %>

     private void ImportCsvDirectly()
    {
        string folder = Server.MapPath("/myfolder/");
        string strCsvfilename = "mycsv.csv";


        string destination_constr = "Data Source=.;Initial Catalog=mydb;User ID=sa;password=#####;Asynchronous Processing=true";
     
        try
        {
     
            // creates and opens an odbc connection
            string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + folder + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
            string sql_select;
            OdbcConnection conn;
            conn = new OdbcConnection(strConnString.Trim());
            conn.Open();


            // create an odbc command
            sql_select = "select * from [" + strCsvfilename + "]";
            OdbcCommand commandSourceData = new OdbcCommand(sql_select, conn);

            OdbcDataReader dataReader = commandSourceData.ExecuteReader();


            using (SqlBulkCopy bc = new SqlBulkCopy(destination_constr))
            {
                //destination table where record has to be import
                bc.DestinationTableName = "my_table";

                //we can map column one by one if required
                bc.ColumnMappings.Add("col1", "col1");
                bc.ColumnMappings.Add("col2", "col2");
                bc.ColumnMappings.Add("col3", "col3");

                // start the bulk copy.
                bc.WriteToServer(dataReader);

                bc.Close();
            }
            conn.Close();
           
        }
        catch (Exception exc)
        {
            Response.Write(exc.Message );
        }

    }



apart form these we can directly import data in sql using sql syntax

1. we have table

CREATE TABLE ctest
( 
ID INT,
name VARCHAR(40),
address VARCHAR(40),
)

2.we have csv file 'c:\ctest.txt'

1,raj,addr1
2,mohan,addr2
3,mac,york


3.use following syntax

BULK INSERT ctest FROM 'c:\ctest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

4.finally we have all data in ctest
SELECT * FROM ctest