copy a table

[jscorpion]

All I want to do is copy the contents of one table with one connection into that of another table at a different connection. I have tried to populate a dataset then copy it to a new dataset and use the adapter to update, but it is not updating the new data. Is there an easy way to copy contents from one table to another especially if the tables are the exact same structure. I just want to move the data. I hope someone can help.

thank you,

jscorpion

 

[Glowworm27]

Try this to copy one dataset to another.

Public Class WebForm1
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region
Dim dsOriginal As Data.DataSet
Dim dsCopy As Data.DataSet

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub

Private Sub CopyTable()
Dim iCounter As Integer = 0


Dim myTable As DataTable
Dim myRow As DataRow
Dim myColumn As DataColumn
Dim blanktable As DataTable = New DataTable
'This will create the columns in the new datatable identical to the existing dataset table

For Each myColumn In dsOriginal.Tables(0).Columns
blanktable.Columns.Add(myColumn.ColumnName.ToString)
Next

'add the new table to the blank dataset.
dsCopy.Tables.Add(blanktable)

For Each myRow In dsOriginal.Tables(0).Rows
Dim drv As DataRow = dsCopy.Tables(0).NewRow

For Each myColumn In dsOriginal.Tables(0).Columns
'copy the contents of the original table to the copy

drv(myColumn.ColumnName) = myRow(myColumn.ColumnName)

Next myColumn

dsCopy.Tables(0).Rows.InsertAt(drv, iCounter) 'insert the copied row into the table
iCounter += 1
Next myRow

' then do what ever you want with the dataset like bind it to a new grid

End Sub

End Class



hope this helps. check out my siganture line there is a valuable resource for .Net Programming and its cheap too!


George Oakes
Goakes@TiresPlus.com = Programmer
George@1-Specialday.com = Mobile DJ
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[jscorpion]

thanks for the tip, but I have a solution. I just thought there may be a better way. I ended up doing it like so:

OleDbConnection dbConn2 = new OleDbConnection(MainSource);
dbConn2.Open();
string test2=&quot;DELETE FROM CURRENTTABLE&quot;;
string test3=&quot;SELECT * FROM CURRENTTABLE&quot;;
OleDbCommand dbComm2 = new OleDbCommand(test2, dbConn2);
dbComm2.ExecuteNonQuery();

DataSet ds2 = new DataSet();
OleDbDataAdapter dbAdap2 = new OleDbDataAdapter(test3,dbConn2);
OleDbCommandBuilder cb = new OleDbCommandBuilder(dbAdap2);

ds2=ds.Clone();
foreach ( DataRow aRow in ds.Tables[0].Rows )
{
DataRow newRow = ds2.Tables[0].NewRow();
newRow[&quot;EMPID&quot;] = aRow[&quot;EMPID&quot;];
newRow[&quot;PROCESS&quot;] = aRow[&quot;PROCESS&quot;];
newRow[&quot;INFOIN&quot;] = aRow[&quot;INFOIN&quot;];
newRow[&quot;INFOOUT&quot;] = aRow[&quot;INFOOUT&quot;];
newRow[&quot;TIMESTAMPIN&quot;] = aRow[&quot;TIMESTAMPIN&quot;];
newRow[&quot;TIMESTAMPOUT&quot;] = aRow[&quot;TIMESTAMPOUT&quot;];
ds2.Tables[0].Rows.Add(newRow);
}


dbAdap2.Update(ds2);
dbConn2.Close();


but I know there is an sql statement you can use in some talking programs that goes something like this:
&quot;COPY 1.TABLE TO 2.TABLE&quot; where 1 is an open curser and two is another open curser. I tried two connection strings and used those in place of 1 and 2 and then ran a command.executenonquery routine. This failed but I still think there is a way similar to this but I can't figure it out hope someone out there can help.

thanks for you post glowworm27!

 

[Glowworm27]

maybe you should try your post in the sql forum.

I have used this sql in a stored proc to create a new table on the fly as I select data from another table.

SELECT StoreNo, customerid, extprice, salestax as InvTotal into temptop1500p1
From Invoices
Order by storeno, customerid

or if you just want to copy data from one table to another you can try this.

insert into Newtable
SELECT * FROM Oldtable

George Oakes
Goakes@TiresPlus.com = Programmer
George@1-Specialday.com = Mobile DJ
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips