Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
terper
Starting Member
3 Posts |
 Posted - 2003-01-31 : 21:05:04
|
| I have two tables with the exact same structure and I want to use a button in a form to move not copy records from the one to the other!I'm using access and so far the solution I'm using is through visual basic and the docmd.runsql statement to do the following:First step: I copy the data I want to the second table (which in the beggining of every transaction is normally empty) Second step:I copy the whole second table back to the firstThird step: I delete all the duplicate records in the first table.It does my job fine but it seems so stupid tht I guess there must be an easier way to do this!PS1:More info: to understand better the reason I do this...both tables contain telephones and among other fields contain a "used" (true or false) field. It is used in a multiuser enviroment. Once a user uses a phone number its marked used and noone else uses it. Again this is done through visual basic. When I put too many phones in table one it runs slowly searching for free phones. So I save all the phones in a second table and select each day a specific number of phones to enter into the active table.At the end of the day I move the used phones in a table calle "Telephones Used" and the rest to the "Telephones All". The following day once again I take phones from "Telephones All" to "telephones Active"...And there the problem comes. As you understand the data have to be moved and not copied for the whole procedure to be meaningful!PS2: Excuse my bad English I'm ... Greek! Best regards from Athens!!!!<edit> Moved to MS Access Forum </edit>Edited by - robvolk on 01/31/2003 21:10:20 |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-02-01 : 22:29:05
|
| You can insert into the first table from the second table or source where the value doesn't already exist in the first table.INSERT INTO Table1 ( ItemID, [Value])SELECT Table2.ItemID, Table2.ValueFROM Table2 LEFT JOIN Table1 ON Table2.ItemID = Table1.ItemIDWHERE (((Table1.ItemID) Is Null)); |
 |
|
|
terper
Starting Member
3 Posts |
Posted - 2003-02-03 : 02:55:31
|
| There are three different tables..., Telephones all, and Telephones Used, And Telephones Active! Source:Telephones all (no used - or all null - records)Destination: Telephones Active ( here i insert only not used phones, and during the day's work some get used)At the end of the day I move the used phones in the table called "Telephones Used" and the rest to the "Telephones All". And even if that wasn't the case still i'd have after the transfer you suggest two instances of the same record , one in each table!I want to be able to have single instances!Thanks though very much for the try... |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-02-03 : 14:22:46
|
| This is more a modeling concern than a physical one. Can't you just add a "Status" Column to 1 table and keep all of the phones in that table. You are then just left having to perform an update. This mass moving around is ineffecient, And, Access's buffers will continue to grow and grow at an alarming rate. If you continue with your method, make sure you repari and compact on a regular basis.Good LuckBrett 8-) |
|
|
|
terper
Starting Member
3 Posts |
Posted - 2003-02-06 : 16:46:07
|
| I do compact and repair every week!I use two tables so that the search for used or not phones is not performed throughout a table with 200000 records but one with only 10000 ones and furthermore one with few used records its time the research begins!It's much - I mean much - faster in giving a valid record (not used) with 10000 than with 200000 records to look over!That's why the need for data transfer rather than data marking!If you do have any suggestions please do reply! |
|
|
|
|
|
|
Move records from one table to another