Popular Posts

There was an error in this gadget

Mar 19, 2013

Copy data from one table to another







SqlConnection conn = new SqlConnection(connString);
        conn.Open();
        String copyData = "INSERT INTO t_VAT (VAT_Month, VAT_Date, orderType) SELECT VAT_Month, VAT_Date, orderType FROM t_VAT_TEMP";
        SqlCommand cmd = new SqlCommand(copyData, conn);
        int returnValue = cmd.ExecuteNonQuery();
        conn.Close();

Using BULK INSERT









string connString = ConfigurationManager.ConnectionStrings["db_connection_string"].ConnectionString;         SqlConnection conn = new SqlConnection(connString);
        conn.Open();
        Label1.Text = "In Try OPEn()";
       
        SqlCommand comm = new SqlCommand(@"
                                    BULK INSERT t_CWR_TEMP
                                    FROM '" + fileName + "'" +
                            @" WITH
                                    (
                                        FIELDTERMINATOR = ',',
                                        ROWTERMINATOR = '\n'
                                    )", conn);
        comm.ExecuteNonQuery();
        Label1.Text = "In Try Close";
        conn.Close();

Mar 4, 2013

Import data from CSV to sqlserver with additional field






<tr><td>CWR File: </td>
            <td><asp:FileUpload ID="JDE_CWR_FileUpload" runat="server" />
                                    <asp:Label ID="Label1" runat="server"></asp:Label></td></tr>
            





string fileExt = System.IO.Path.GetExtension(JDE_CWR_FileUpload.FileName);  //Get extension

                    if (fileExt == ".csv")   //check to see if its a .csv file
                    {

                        path = Server.MapPath("App_Data/upload/");
                        JDE_CWR_FileUpload.SaveAs(path + JDE_CWR_FileUpload.FileName);

                        JDE_CWR_FileUpload.SaveAs("C:\\temp\\" + JDE_CWR_FileUpload.FileName);
                        string sourceConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;
                                        Data Source=C:\temp\;
                                        Extended Properties=text;";
                        DataTable sourceData = new DataTable();
                        using (OleDbConnection conn =
                                   new OleDbConnection(sourceConnString))
                        {
                            conn.Open();
                            // Get the data from the source table as a SqlDataReader.

 OleDbCommand command = new OleDbCommand(@"SELECT *,'" + ARCRIVE_INSERTED_ID + "' as EXCELCUSTOM from [" + JDE_CWR_FileUpload.FileName + "]", conn);
 OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                            adapter.Fill(sourceData);
                            conn.Close();
                        }

                        CopyDataCWR(sourceData);
                        




public static void CopyDataCWR(DataTable sourceData)
    {
        string destConnString = ConfigurationManager.ConnectionStrings["db_connection_string"].ConnectionString;
        // Set up the bulk copy object.
        using (SqlBulkCopy bulkCopy =
                          new SqlBulkCopy(destConnString))
        {
            bulkCopy.DestinationTableName = "dbo.t_CWR";
            // Guarantee that columns are mapped correctly by
            // defining the column mappings for the order.
            bulkCopy.ColumnMappings.Add("EXCELCUSTOM", "CWR_VAT_ARCRIVE_ID");
            bulkCopy.ColumnMappings.Add("Order Number", "orderNumber");
            bulkCopy.ColumnMappings.Add("Or Ty", "orTy");
            bulkCopy.ColumnMappings.Add("Line Number", "lineNumber");
            bulkCopy.ColumnMappings.Add("Zone", "zone");
           
            // Write from the source to the destination.
            bulkCopy.WriteToServer(sourceData);
        }
    }