[RESOLVED] How to duplicate a table of a mdb to the same mdb

[Paulo Lib]

Hi,

I need to add the duplication of a table within the same mdb to a vs project that I inherit.

I tried several approaches but none worked.

Can anyone help by providing the correct lines of code to make it happen?

Thank you.

Cheers

[dday9]

I'm fairly certain you could just use SELECT INTO and do this in a single command.

Here is untested code:

Private Sub DuplicateTable(connectionString As String, tableToCopy As String, newTableName As String)
    If (String.IsNullOrWhitespace(connectionString)) Then
        Throw New ArgumentNullException(NameOf(connectionString))
    End If
    If (String.IsNullOrWhitespace(tableToCopy)) Then
        Throw New ArgumentNullException(NameOf(tableToCopy))
    End If
    If (String.IsNullOrWhitespace(newTableName)) Then
        Throw New ArgumentNullException(NameOf(newTableName))
    End If
    If (tableToCopy.Equals(newTableName, StringComparison.OrdinalIgnoreCase)) Then
        Throw New ArgumentOutOfRangeException(NameOf(newTableName))
    End If

    Using accessConnection = New OleDbConnection(connectionString)
        accessConnection.Open()

        Dim commandText = $"SELECT * INTO {newTableName} FROM {tableToCopy};"
        Using cloneTableCommand = New OleDbCommand(commandText, accessConnection)
            cloneTableCommand.ExecuteNonQuery()
        End Using

        accessConnection.Close()
    End Using
End Sub

Keep in mind that even if this does work, it isn't the best option because you do leave yourself open to SQL injection since I'm just taking parameter values and putting them directly in the command text.

[jmcilhinney]

What you describe may be legitimate but it sounds unusual, so can you explain why you want to do that? It's quite possible that there's a better alternative.

[Paulo Lib]

Thank you dday9.

From what I read this is just to insert the records in the new table.

What I need is to make a replica of the table (structure and data - the new table doesn't exists it's the duplication process that creates it, like a DOS copy) and change it's name by adding the date.

Cheers

[jmcilhinney]

What I need is to make a replica of the table (structure and data - the new table doesn't exists it's the duplication process that creates it, like a DOS copy) and change it's name by adding the date.

That sounds like a terrible idea and that's exactly why I asked you to explain what you're actually trying to achieve. Some beginners think it's a good idea to have multiple tables for basically the same data but one table per year or per month or something like that. That defeats the purpose for which relational databases were made. DO NOT do that. In a case like that you should have one table containing all the data. You might have a column for the date and you then filter based on that column to get data for a date range. You might also have another table that, for instance, contains one record for each year and then every record in the original table has a foreign key to the appropriate year record in the second table, so you can identify blocks of records that relate to particular years.

Could you please answer my question and explain what you're actually trying to achieve so that we can correct you if you're doing the wrong thing before you waste a whole lot of time and make your life considerably harder?

[Paulo Lib]

Hi jmcilhinney,

I need to have an image of the table (which is not to big, around 300 records) when I make the duplication because the original table will have its data deleted after the duplication to receive new data.

[wes4dbt]

I think the point jmc is making is why are you deleting the data?? It might not be the best method. So, why are you deleting the data?

[techgnome]

While deleting data is fine, if you're having to make a back up of the data before it's deleted, because it is being deleted, then there's a problem with the design and the data should not be deleted.
And if you do need to off-load the data, for backup reasons, you should just have a table for that purpose and copy the data into it - and not creating new tables with dates on them.


-tg

[Paulo Lib]

The reason for keeping the data is to have an image of the data before a new data is loaded

[wes4dbt]

The reason for keeping the data is to have an image of the data before a new data is loaded

As tg suggested,

And if you do need to off-load the data, for backup reasons, you should just have a table for that purpose and copy the data into it

I'd suggest when you off-load the data to the backup table add a meaningful date to each record. Then if needed you can retrieve the records by date. You really don't want to just keep creating new tables.

[jmcilhinney]

It always amazes me how bad some people are at explaining things, which goes a long way to accounting for why they don't understand things. You're telling us what your solution to the problem is, not what problem you're trying to solve. Why are you deleting data at all? You probably don't even need two tables. Just use one table and don't delete the data. If you want to add new data, add new data. If you need to somehow distinguish between what is new data and what is old data then there are countless ways you can do that. One option would be to use a Boolean column where new data has one value and old data has the other. It's safe to say that what you are doing is bad but we can't really tell you what would be the best alternative because you refuse to actually explain what you're doing.

[techgnome]

Typically when I see people trying to do this, it's for one of two reasons:
1. It's an import process and they're "saving" the data in the staging table. In this case, it's the wrong thing to do. When you import data, it should go into a staging table. You mark the data as DIRTY... then scrub and process as needed. Then you mark it CLEAN. Then you move it to the production tables. Then you mark it DONE. No need to delete anything, no need to off-load the data to some randomly named table. All data is preserved.

2. Accounting. This perhaps the most common reason. Sigh... I cannot stress how bad that is. But I see it all the time and it drives the Accountant in me up a wall. There is zero reason to delete data (accounting data should NEVER be deleted anyways) and there is zero reason that it needs to be put into secondary tables. Trust me, you do that, reporting becomes much more difficult. Instead, design the tables properly - with the help of an accountant if you can --- I used to work on an enterprise system that had A/R built into it... built by devs who had no clue what they were doing. Trying to generate compliant reports was massively complicated and time consuming. It eventually got redesigned (I had to raise hell about it for 3-4 years) by people who knew what they were doing - reports became a lot more simpler and faster.

The exception to this - Data Warehouses... that's the only place data should be off-loaded to... but it's not an exact copy of the data... it's de-normalized data that is designed to make long-term, historical reporting faster. But even still, data moved to the warehouse still resides in the original tables... it's never deleted.


-tg

[Paulo Lib]

Thank you all.

I just follow the orders I've been given.
The image of the table is for external auditing purposes.

After I searched for the way of duplicate the table programatically and found nothing I came here for help.

[techgnome]

well, the SELECT ... INTO is the syntax you're looking for...

-tg

[ChrisE]

Thank you all.

I just follow the orders I've been given.
The image of the table is for external auditing purposes.

After I searched for the way of duplicate the table programatically and found nothing I came here for help.

you have a solution in Post#2
did you try it ?

another problem could be FOREIGN KEY(s) and INDEXES

[Shaggy Hiker]

I agree with everybody here that this has a pretty bad smell to it. Push back against the orders, if you can.

However, for what you are doing, you will need a CREATE TABLE statement. The syntax for those isn't terribly difficult, but you can certainly make it more difficult if you want to...and you may want to. The key question is whether or not you can count on the table structure remaining the same, or if you have to determine the table structure every time you run the CREATE TABLE query. The former is much easier than the latter, but both are possible.

There may be an easier way to do this, but if I were to approach it with the expectation that the table might change each time, I'd run a query against the existing table to get structure only. Something like this should work:

https://learn.microsoft.com/en-us/do...e?view=net-8.0

Building the CREATE TABLE query from that would then just be tedious. Adding foreign keys and indexes just makes it more so.

[Zvoni]

As others said:
CREATE TABLE NewTable AS SELECT blablabla FROM OldTable
Or
SELECT blablabla INTO NewTable FROM OldTable

No idea, which of the above is supported by JET

[Paulo Lib]

Hi all.

Thank you for all your input.

I've ill for the past days and unable do try your solutions.
Nevertheless I left your suggestions of creating only one table with a added column for timestamp to create the history of the changes to the original table.
And after much debate between my team and the auditing firms they accepted.

Now can I ask you an easy practical way to insert the records into the history table adding a datetime value in the new column?
They have the same structure besides the datetime column in the history table.

[ivansmo]

VB code sample

Dim cmd As New SqlCommand("Stored_procedure", con) With {
                  .CommandType = CommandType.StoredProcedure
             }
            cmd.Parameters.AddWithValue("@id", Trim(TXT_id.Text))
            cmd.Parameters.AddWithValue("@someData", Trim(TXT_someData.Text))
            cmd.Parameters.AddWithValue("@time", DateTime.Now)

SQL sample

INSERT INTO table_name
           VALUES (@id, @someData, @time);

[Paulo Lib]

VB code sample

Dim cmd As New SqlCommand("Stored_procedure", con) With {
                  .CommandType = CommandType.StoredProcedure
             }
            cmd.Parameters.AddWithValue("@id", Trim(TXT_id.Text))
            cmd.Parameters.AddWithValue("@someData", Trim(TXT_someData.Text))
            cmd.Parameters.AddWithValue("@time", DateTime.Now)

SQL sample

INSERT INTO table_name
           VALUES (@id, @someData, @time);

Thank you.

What I meant was to copy the records from the table into the history table adding the datetime in the column DATE_TIME (this one only exists in the history table).

[Zvoni]

Thank you.

What I meant was to copy the records from the table into the history table adding the datetime in the column DATE_TIME (this one only exists in the history table).

And what's the initial value for that DateTime? Today/Now?
Just declare "Now" as DEFAULT for that Column. Then you don't need to pass a Value when copying from old to new table.

Everytime someone inserts a new record there, it automagically gets the current timestamp.

If you need to update (which wouldn't make sense in the context of "auditing"), then you have to pass a new value

Aircode

CREATE TABLE tblhistory
(
   ID INTEGER PRIMARY KEY,
   ...... /*other Fields*/
   date_created DATETIME DEFAULT NOW() NOT NULL
);

[ivansmo]

Create a Stored_procedure to move the records and once it all goes well, delete data from the original table.

As Zvoni has said, you can create a default entry for the Datetime column and it will populate automatically. If you prefer to pass a value, use a SP parameter as my example shows.

[Paulo Lib]

Thank you all.

The situation is now solved.

[Zvoni]

Create a Stored_procedure to move the records and once it all goes well, delete data from the original table.

Or just use a DELETE with a JOIN.
That way you only delete records from the original table, that has been succesfully transferred