I need to copy one table of database to other database programmatically? : The Official Microsoft ASP.NET Forums

Reply

I need to copy one table of database to other database programmatically?

Apr 12, 2012 10:36 AM|LINK

I need to copy one table of database to other database and i have written below program for that

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
       <div>
        <table align="center" style="background-color:Silver; margin-top:20px;">
            <tr>
                <td width="50%">
                    <fieldset>
                        <legend>Source</legend>
                        <table>
                            <tr>
                                <td>
                                    ServerName:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtservername"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    DataBaseName:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtdbname"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    UserName:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtuserid"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    Password:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtpwd"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                            <td>
                            Table Name:
                            </td>
                            <td>
                            <asp:TextBox runat="server" ID="txtTableName"></asp:TextBox>
                            </td>
                            </tr>
                            <tr>
                                <td colspan="2">
                                <center>
                                    <asp:Button runat="server" ID="btngetcon" Text="Copy" OnClick="get_connection" />
                                </center>
                                </td>
                            </tr>
                        </table>
                    </fieldset>
                </td>
                <td>
                    <fieldset>
                        <legend>Destination</legend>
                        <table>
                            <tr>
                                <td>
                                    ServerName:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtservername1"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    DataBaseName:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtdbname1"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    UserName:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtuserid1"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    Password:
                                </td>
                                <td>
                                    <asp:TextBox runat="server" ID="txtpwd1"></asp:TextBox>
                                </td>
                            </tr>
                            <tr>
                                <td colspan="2">
                                    
                                </td>
                            </tr>
                        </table>
                    </fieldset>
                </td>
            </tr>
        </table>
    </div>
 
    </form>
</body>
</html>

and code behind

using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Web; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Data; 
using System.Data.SqlClient; 
 
public partial class Default3 : System.Web.UI.Page 
{ 
    protected void Page_Load(object sender, EventArgs e) 
    { 
        
    } 
 
    protected void get_connection(object sender, EventArgs e) 
    { 
        string _sourceConnectionString = "Data Source=" + txtservername.Text.Trim() + ";Initial Catalog=" + txtdbname.Text.Trim() + ";User ID=" + txtuserid.Text.Trim() + ";Password=" + txtpwd.Text.Trim() + ";"; 
        string _destinationConnectionString = "Data Source=" + txtservername1.Text.Trim() + ";Initial Catalog=" + txtdbname1.Text.Trim() + ";User ID=" + txtuserid1.Text.Trim() + ";Password=" + txtpwd1.Text.Trim() + ";"; 
        string table = txtTableName.Text; 
        using (SqlConnection source = new SqlConnection(_sourceConnectionString))  
        {  
            string sql = string.Format("SELECT * FROM [{0}]",table); 
            SqlCommand command = new SqlCommand(sql, source); 
            source.Open();  
            IDataReader dr = command.ExecuteReader(); 
            using (SqlBulkCopy copy = new SqlBulkCopy(_destinationConnectionString))  
            { 
                 
                copy.DestinationTableName = table;  
                copy.WriteToServer(dr);  
            } 
        }  
    } 
}

it is working if there is a table already exist in the destination database, but i want copying table structure also done through program.

Please suggest improvements this program.

Thanks...

Ch Mahesh Kumar
www.jkc-forum.in
Mark this post as answer if this helps.It can save others time.

Moved by bbcompent1 on Apr 12, 2012 10:37 AM

Reply

Re: I need to copy one table of database to other database programmatically?

Apr 12, 2012 12:19 PM|LINK

You're going to have to write some DDL scripts to first check for the existance of the table and if not exist create the table.

Here's some psuedo:

if not exists (select * from systables where tablename = 'myTable')

CREATE TABLE myTable(id int identity(1,1), Fname varchar(50))

else

select * from myTable

Reply

Re: I need to copy one table of database to other database programmatically?

Apr 17, 2012 12:03 PM|LINK

if table not exist in designation database

execute the following query

CREATE TABLE new_table AS (SELECT * FROM old_table);

Manivannan M
MCTS

Reply

Re: I need to copy one table of database to other database programmatically?

Apr 17, 2012 12:12 PM|LINK

hello manivannanm,

he is talking about from one database to another not from one table to another from same database. It will work if you are using same database but not for different databases...

Reply

Re: I need to copy one table of database to other database programmatically?

Apr 17, 2012 12:16 PM|LINK

Hi MaheshKumarCh,

use this,

USE db2;

CREATE TABLE table2 LIKE db1.table1;

INSERT INTO table2  
   SELECT * FROM db1.table1;

i think it will work

-------------------------------------------------------------

mark as answer if it helps you

Reply

Re: I need to copy one table of database to other database programmatically?

Apr 19, 2012 03:30 AM|LINK

Hey there. For copying from one database to another you have a few options dependin on your situation. If both databases are on the same db server you can simply do a SELECT INTO procedure to insert the data from the table in the 1st db to the table in the 2nd db.

If they are on separate servers (as they should be if you are using 2 seperate databases) you can either get the data from database 1, pass it to your app and insert into database 2 from within the app (although inefficient to do this way), OR you can use a distributed query to the 2nd database. Linking servers may also be an option. These solutions are all assuming you are doing this on a live server and do not wish to disturb its activity or that this procedure is a part of your apps functionality.

If you are simply wishing to migrate data from one database to another because you are changing which database will be the primary database, a bulk insert operation might be best. You could copy the tables records to a formatted file (csv file or formatted text doc, xml, etc) and do an sql import to insert the data to the new database.

Just some tips.

When the going get's tough, the tough outsource and take a vacation... lol I wish :(

Edited by magicmike2011 on Apr 19, 2012 03:31 AM
Edited by magicmike2011 on Apr 19, 2012 03:31 AM
Marked as answer by Qi Wu - MSFT on Apr 24, 2012 04:16 PM

I need to copy one table of database to other database programmatically? RSS

5 replies

MaheshKumarC...

I need to copy one table of database to other database programmatically?

adamturner34

Re: I need to copy one table of database to other database programmatically?

manivannanm

Re: I need to copy one table of database to other database programmatically?

keval.trived...

Re: I need to copy one table of database to other database programmatically?

keval.trived...

Re: I need to copy one table of database to other database programmatically?

magicmike201...

Re: I need to copy one table of database to other database programmatically?

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft