How to copy the contents from one table to another table and how to delete the source table in ado.net?

Editorial / Best Answer

samiksc  

  • Member Since Oct-2005 | Jul 12th, 2007


The question can mean 2 tasks -
1. Copy one 'datatable' to another and delete the source 'datatable' OR
2. Copy one database table (say a table in sql server database) to another database table, and delete the source table which is in the SQL server database. This operation is to be performed using ADO.Net

Answers:
1. DataTable newOne = originalOne.Copy(); originalOne.Dispose(); -- Note that originalOne.Clear() will simply delete all rows from the table, but the table object with its structure will remain there.
2. Run DDL commands using 'ExecuteNonQuery' method of DataCommand object.
e.g. string cmdText1 = "create table newOne as select * from originalOne" (copy table to another)
string cmdText2 = "drop table originalOne";
DataCommand dataCmd = new SqlDataCommand(cmdText1, conn);
conn.Open();
dataCmd.ExecuteNonQuery();
dataCmd.CommandText = cmdText2;
dataCmd.ExecuteNonQuery();
conn.Close();

Showing Answers 1 - 46 of 46 Answers

jagadeesh

  • Sep 12th, 2005
 

cleart and fill

  Was this answer useful?  Yes

Puli

  • Mar 10th, 2006
 

May be there is a better way, but you can generating a datatable from source and load it to destination if the process is successful, execute the drop table query.

  Was this answer useful?  Yes

S Kumar

  • May 4th, 2006
 

To Copy you use: NewTable = DS(tableName).copy

To Delete Use .Clear

  Was this answer useful?  Yes

Sathyavathi

  • May 11th, 2006
 

Hi,

  You can do it in ado.net 2.0 using SqlBulkCopy.

Regards

Sathyavathi

 

  Was this answer useful?  Yes

Vivek

  • Jun 12th, 2006
 

Hi,

You have to use dataset as datasource. and when you retrieve data in dataset use clone() method to make copy of one table to another.

Vivek B.

  Was this answer useful?  Yes

rahul kulshrestha

  • Jun 22nd, 2006
 

Hi

I use reader to hold the data. and this reader is passed to sqlbulkcopy to write on the destination server.

if u want further information then mai me on my id.

 

strSql = " SELECT * FROM authers "

CommandSourceData = New SqlCommand(StrSql, SourceConn)

reader = CommandSourceData.ExecuteReader()

Using bcp = _

New SqlClient.SqlBulkCopy(DesConnString)

bcp.DestinationTableName = _

"authers"

' Write from the source to the destination.

bcp.WriteToServer(reader)

End Using

reader.Close()

  Was this answer useful?  Yes

areef

  • Jul 27th, 2006
 

ThanQ

  Was this answer useful?  Yes

BalaSM

  • Aug 2nd, 2006
 

hi

it is possible

DataSet ds;

sqlAdap.Fill(ds);

Datatable dt = ds.Tables[0].copy();

//now the structure and data are copied into 'dt'

ds.Tables.remove(ds.Table[0]);

//now the source is removed from the 'ds'

Suji

  • Apr 23rd, 2007
 

you have to use ds.copy command to do this operation

  Was this answer useful?  Yes

tapti

  • Jun 8th, 2007
 

create table <new_tablename>  as select * from  <source_tablename>

  Was this answer useful?  Yes

shekhar

  • Jun 16th, 2007
 

FileCopy("C:a.mdb", "d:a.mdb")

  Was this answer useful?  Yes

vikas

  • Jun 24th, 2007
 

Clone method is used to generate only schema of the table in the dataset and not to copy the content of the table.

  Was this answer useful?  Yes

samiksc

  • Jul 12th, 2007
 

The question can mean 2 tasks -
1. Copy one 'datatable' to another and delete the source 'datatable' OR
2. Copy one database table (say a table in sql server database) to another database table, and delete the source table which is in the SQL server database. This operation is to be performed using ADO.Net

Answers:
1. DataTable newOne = originalOne.Copy(); originalOne.Dispose(); -- Note that originalOne.Clear() will simply delete all rows from the table, but the table object with its structure will remain there.
2. Run DDL commands using 'ExecuteNonQuery' method of DataCommand object.
e.g. string cmdText1 = "create table newOne as select * from originalOne" (copy table to another)
string cmdText2 = "drop table originalOne";
DataCommand dataCmd = new SqlDataCommand(cmdText1, conn);
conn.Open();
dataCmd.ExecuteNonQuery();
dataCmd.CommandText = cmdText2;
dataCmd.ExecuteNonQuery();
conn.Close();

sudhanshu

  • Jul 30th, 2007
 

At 1st create a datatable for the components which have to copy. Then copy the table by using copy() method, to delete we use clear().. 

  Was this answer useful?  Yes

gopinath412

  • Oct 17th, 2007
 


string sql=select columnname1,columnname2 from orginaltable into coppying table

sqldataadpter da=new dataadpter(sql,con);

da.fill(ds.table["emp"]);

  Was this answer useful?  Yes

Pendurti

  • Sep 11th, 2008
 

Create table <NewTableName> as select * from <OldTableName>;

This statement creates the new table with name NewTableName and copies the data from table OldtableName into NewtableName. 

To just create a copy of the table but not copy the contents, you can simply say
Create table <NewTableName> as select * from <OldTableName> where 1=2;



  Was this answer useful?  Yes

Dharmesh279

  • Oct 18th, 2008
 

We can copy the contents from 1 table to another table by using Bulk Copy and delete a table by simply write the query of drop a table when we create sqlcommand object.

  Was this answer useful?  Yes

sathin

  • Oct 29th, 2009
 

This is definitely possible.
I tried making it simple by talking two gridviews in the design part and writing
the below code
when you try creating a datatable do avoid the new keyword

Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal
sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim cn As New SqlConnection
Dim st As String
Dim cnstr As String
cnstr = "user id=sa;password=mellonit;database = reg;Data
Source=MELLONIT-044SATISHSQL2005"
cn.ConnectionString = cnstr
st = "select * from Account"
Dim da As New SqlDataAdapter(st, cn)
Dim ds As New DataSet da.Fill(ds, "d")
GridView1.DataSource = ds
GridView1.DataBind()
Dim dt As DataTable
dt = ds.Tables(0).Copy
ds.Tables(0).Clear()
GridView2.DataSource = dt
GridView2.DataBind()
End Sub
End Class

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions