Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to copy a SQL Server Database table to another database in ASP.NET

Author: Richard Wand

This article will explain how we can copy SQL Server database table to another database using SELECT INTO statement in ASP.NET.

 
SQL provides SELECT INTO statement to copy one database table to another. This statement creates another table with same schema and copy the data into that table. This statement can be used with WHERE clause as well and we can also copy particular columns of a table to another database. This article will explain that how we can copy database table in ASP.NET code by using this statement.
 
 
Follow below steps to learn how to copy one database table to another.

 

  1. Create new website in Visual Studio 2010
  2. Add a Button and a Label control in aspx page of a web form

    <h3>Copy SQL Server Database Table to anthor Database</h3>
    <asp:Button ID="Button1" runat="server" Text="Copy Table"
           onclick="Button1_Click" />
    <br />
    <asp:Label ID="Label1" runat="server" ></asp:Label>
     
  3. Following namespace used in the code

    C#
     
    using System.Data.SqlClient;
     
    VB.NET
     
    Imports System.Data.SqlClient
  4. Now add a Button Click event and write code below in it

    C#
     
    protected void Button1_Click(object sender, EventArgs e)
    {
           int rowsAffected = 0;
           string myConnectionString = "Data Source=Local;Initial Catalog=Test;Integrated Security=True;Pooling=False";
           SqlConnection myConnection = new SqlConnection(myConnectionString);
     
           string myQuery = "SELECT * INTO Test2.dbo.Table2 FROM Test.dbo.Table1";
           SqlCommand myCommand = new SqlCommand(myQuery, myConnection);
     
           try
           {
                  myConnection.Open();
                  rowsAffected = myCommand.ExecuteNonQuery();
           }
           catch (Exception ex)
           {
                  Response.Write(ex.Message);
           }
           finally
           {
                  myConnection.Close();
           }
     
           Label1.Text = rowsAffected.ToString();
    }
     
    VB.NET
     
    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
           Dim rowsAffected As Integer = 0
           Dim myConnectionString As String = "Data Source=Local;Initial Catalog=Test;Integrated Security=True;Pooling=False"
           Dim myConnection As New SqlConnection(myConnectionString)
     
           Dim myQuery As String = "SELECT * INTO Test2.dbo.Table2 FROM Test.dbo.Table1"
           Dim myCommand As New SqlCommand(myQuery, myConnection)
     
           Try
                  myConnection.Open()
                  rowsAffected = myCommand.ExecuteNonQuery()
           Catch ex As Exception
                  Response.Write(ex.Message)
           Finally
                  myConnection.Close()
           End Try
     
           Label1.Text = rowsAffected.ToString()
    End Sub

    In above code, we have used a SELECT INTO statement that selects Table1 of Test database, creates a new Teble2 in Test2 database and copies the data into the new created table. It will return number of rows affected and we have called ExecuteNonQuery() method to execute command and get number of rows affected.



  5. We can also write SELECT INTO statement as below

    Select particular columns
     
    SELECT Column1, Column2 INTO Test2dbo.Table2 FROM Test.dbo.Table1
     
    Using WHERE Clause
     
    SELECT * INTO Test2dbo.Table2 FROM Test.dbo.Table1 WHERE Column2 = SomeValue

 

 
   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to implement search in a GridView in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net