Copy a table using ADO.Net

[Dave DeCoursey]

OK, I think the answer is no, but I need to ask.



I have a table structure defined in my Access database (SQL Server is not
available), I would like to take that table and create a new table with a
unique name. Can I do this using ADO.NET?


Thanks,
Dave

 

[W.G. Ryan - MVP]

Are you asking about creating the DDL or populating it? If you are asking
about populating it, then just use the same schema and the new table name.
Configure to OleDbDataAdapters, one for the source, one for the destination.
Set the AcceptChangesDuringFill property of the first adapter to false, fill
your datatable, then use the next adapter and call update. If you need only
the schema, then loop through the schema table and build it from there.

HTH,

Bill

 

[Dave DeCoursey]

Thanks,

No, the table is new; I won't know the name of it until I import the data. I'll
have the basic structure in Table-A, but Table xxx hasn't been created yet,
and that's where I want to store the data.



My other option is to make the "xxx" part of the key for Table A and just
use it that way, but I think it would be cleaner if I could copy the
structure of A to create xxx.



Thanks,

Dave

 

[William \(Bill\) Vaughn]

I'm a bit late on this conversation, but has anyone suggested BCP or DTS to
move the data?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Sahil Malik [MVP]]

I think the issue is deciding the name of the new MS ACCESS table ..

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Sahil Malik [MVP]]

Dave,

I think you will have to sink to the level of interop to load up the Access
object model first.

Then load up the database you are working with
Then iterate over CurrentDB.TableDefs

That way you can find out the names of the current existing tables, which
will let you decide on a new table name.

Now to add a new table, you can simply create a new TableDef directly in
COM, then add Fields, and add it to the TableDefs, or simply issue a Create
Table through ADO.NET instead.

Once you've done that, you can then move your data into the newly created
table.

HTH ;-)

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Cor Ligthert [MVP]]

Dave,

Filling a dataset is easy

Set that all as added is simple (using the
xxxDataAdapter.AcceptChangesDuringFill = false before the fill) as Bill
already stated

Creating a new table in your database you have to do with the SQL command
Create Table etc. and process that with XXXCommand.ExecuteNonQuery

If your table contains not more than 50 columns than it is probably easier
(which depends from your knowledge from both) to write that direct than
trying to create that SQL string from your schema from the old table.

I hope this gives an idea.

Cor

 

[shivaadabala]

Hi Dave,

Creation of a Table is possible with the Clone Method of the DataSet, which creates the Schema of the Table with out the Structure

 

[Patrice]

Another option would be perhaps to issue a SQL Statement such as SELECT INTO
to copy the existing table into a new table...

 

[Dave DeCoursey]

Thanks everyone for the response,

I think Cor has the soulution for me, using SQL to create the new table, it
only has about 10 columns.

Dave

 

[W.G. Ryan - MVP]

I absolutely agree with you here, the only reason I didn't mention it is b/c
he doesn't have access to Sql Server. But using ADo.NET as a transfer
technology when you have DTS, BCP is defintely the long hard road to the
same place.