Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
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

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

    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);
                  rowsAffected = myCommand.ExecuteNonQuery();
           catch (Exception ex)
           Label1.Text = rowsAffected.ToString();
    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)
                  rowsAffected = myCommand.ExecuteNonQuery()
           Catch ex As Exception
           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