Copy all field from table to table.

[calvinle]

Hi,

I am working on a database with backend data, and the form list all case in the back-end.
When user open a case, it will download the data from back-end and store in local table. Reason for so is to avoid disconnection issue because the LAN is quite slow and bug a lot.

Is there a way to transfer the data from table A to table B without enumerating all the field?

Thanks

 

[Minty]

You can use a SELECT INTO query. https://www.w3schools.com/sql/sql_select_into.asp be warned that the existing table will need deleting before hand. Also continually creating and deleting temp tables can cause database bloat.

You may be better doing this into a "Side" database, another local database purely used to store temp objects where the bloat doesn't matter.

 

[missinglinq]

Also probably better to get the LAN situation worked out! This kind of problem usually rears its head when using a WAN or other 'long distance' connections.

What's to say that a disconnect won't occur in the middle of transferring the entire session to the Back End?

Linq ;0)>

 

[Pat Hartman]

Yes, you can do it. Minty told you how. But doing it is extremely dangerous in a multi-user environment. What happens if userA copies the data to his local PC and takes a phone call. Meanwhile userB copies the same data and does his update promptly. When userA is ready to update, he is going to overlay the changes made by userB.

So, if you are fixated on doing this, you have a lot of other work that has to be done. For example, when userA copies the record, you must first "lock" the record by tagging it with userA's ID and a date/time. That way if someone else wants to update the record, you app can prevent it and tell userB that userA has the record locked and it cannot be currently modified. Then when userB commits his update, you remove the lock field values to unlock the record.

As we all know "stuff" happens and you will end up with orphaned locked records so you also have to establish procedures to identify locked records and clear the locks.