googlemad Posted October 26, 2017 Posted October 26, 2017 I need to add some data lost back into a SQL database which is easy enough as it isn't a complicated database at all, and if I get the formatting right in Excel I can just copy and paste the data straight into Management Studio. Anyway the one thing I do need to change is the ID numbers of the data so that the numbers are unique (since the data loss until somebody noticed the database has been online so the previous ID numbers have been 'overwritten' by new data entries! However due to how the system works some rows use the same ID so when I'm generating the new IDs I need to replicate the duplication somehow but with the new ID! So here is a small sample of the data 5492048 5492048 5492048 5492048 5492047 5492047 5492047 5492047 5492046 5492046 5492046 5492045 5492045 5492045 5492044 5492044 5492043 5492043 5492043 5492043 and obviously I would like it to become similar to the following : 5492056 5492056 5492056 5492056 5492055 5492055 5492055 5492054 5492053 5492053 5492053 5492052 5492052 5492052 5492051 5492051 5492050 5492050 5492050 5492050 Usually I would think 'screw it' and just do it myself manually but with 91,320 rows that isn't such a tempting option!
Steve21 Posted October 26, 2017 Posted October 26, 2017 (edited) If you know the offset, any reason you don't just do a formula based addition to each? Like a straight 7 offset etc in your example etc As an example, if column A is the IDs. Insert a new column next to it, and set the formula as =A1+"offset" etc, so say in your example 7. It'll populate the column with it, then the important part, select and copy the whole column, right click on itself and press v to repaste as values not formula, Then just delete column A and B will have the right IDs if that makes sense? Steve Edited October 26, 2017 by Steve21
dapaulio Posted October 26, 2017 Posted October 26, 2017 Easy to do yes but I’m concerned that there may be more to it. Let me get this straight. This list of numbers, are they foreign id meaning they refer to a unique id in another table in your sql database. If that is the case then you will need to also add the new ids in the table containing the unique Id removing all the duplicates first and cross referencing then in this table. If I am not mistaken it sound like your example is from table 2 so therefore their should be a relationship with a parent record in table 1. Changing the id in table 2 would require in table 1 being also changed Eg table 1 Unique ID, car manufacture etc ... 5492043, Ford 5492044, Vauxhall 5492045, Honda Table 2 Unique id, foreign id, car model 1, 5492043, fiesta 2, 5492043, focus 3, 5492044, Corsa 4, 5492043, ka 5, 5492044, Astra 6, 5492045, civic Apologies if I have misunderstood
googlemad Posted October 27, 2017 Author Posted October 27, 2017 Let me explain the system because I'm more confused myself now! Basically the software scans images and then does OCR to check for barcodes on the scans and import them in so that users can then search for documentation using barcode numbers 3 Tables tbl_ImageList - A list of images and where to find them on the server (These are all unique ID rows so just need to do a straight update on this list to match the latest available unique ID on SQL ID ImageID FileName MultiPage ScanDateTime Archived FileDate FileTime 1234567 1234567 2017\10\26\20171026075017923Y.tif Y 26/10/2017 07:50 N 26-Oct-17 07:10 tbl_BCList - A list of ImageID and the barcodes contained on it - this is the huge one as there could be several barcodes on each image (I see what you're saying, when I update the ImageID in tbl_ImageList I need to sync it with the ImageID in this table!). Ignore BCType and StnCode. ImageID BarCode BCType StnCode 1234567 287526 S OFFICE 1234567 B4RC123 tbl_NoBarcode - A list of images where the scan didn't pick a barcode up, again ImageID needs to sync with the tbl_ImageList value although no duplicates in this list, so just need to compare the 1200 or so ImageID entries in this with tbl_ImageList and update them the same ImageID StnCode ImageID StnCode 7654321 OFFICE So basically, the 2 sub tables both link to the ImageID on tbl_ImageList
Steve21 Posted October 27, 2017 Posted October 27, 2017 So assuming the ImageID is the same value across all 3 tables you'd just repeat the process I mentioned on all 3, and then you'd merge the "working" and "fixed" ones together. As unless I've misunderstood, you're basically going to have 6 values, 3 with ID 123etc that is the real 123, and 3 with 123 that's the fake123 that needs updating. So as long as you update the fake123 before re-adding it to the list, it'll be seperate values still right? But are the ImageIDs definitely the same values? Steve
googlemad Posted October 27, 2017 Author Posted October 27, 2017 I've decided to go about it a slightly different way in that I've restored the SQL backup as of 08:00 yesterday and going to swipe any changes made since then from the current live copy of the database and add them to the end of the 08:00 backup In Excel for tbl_ImageList I now have the following set up ID ImageID 5492101 5457851 5492102 5457852 5492103 5457853 ID is what I will eventually duplicate across to ImageID, but I've left it like that for now so I know what the old number was (ImageID) vs the number I plan to assign (ID) in tbl_BCList the 'duplicates' for the different barcodes are sadly not in a particular sequence, so I have ImageID BarCode 5457851 15904 5457851 15904 5457852 287529 5457852 287529 5457853 55287528 5457853 287528 5457853 287528 5457853 287528 The 'gap' between values varies throughout the data as you can see, but I'm thinking a fancy formula could now look at both ID and ImageID with what I have set up and replace these values with my proposed (ID) numbers? The ImageID is the same value, SQL itself will add the 'ID' value when I do the import (but it just matches the ImageID) Sorry my head is starting to hurt!
googlemad Posted October 27, 2017 Author Posted October 27, 2017 So in the restored database 5492101 is the next available unique ID / ImageID, but the earliest record after the screw up in the current live database is 5457851, it obviously has this much lower number due to the 3 week missing data
dapaulio Posted October 31, 2017 Posted October 31, 2017 So in excel I would use a vlookup formula On sheet 1 Old id, new id On sheet 2 copy the contents of your sql table and add a new column with the formula in each of the rows that have record. I’m assuming the imageId is in column A =vlookup(a1,sheet 1!a:b,2,false) Formula explained Vlookup(lookup value, range, return value, matchmode) Match mode True = closest match False = exact match Hope this is helpful
dapaulio Posted October 31, 2017 Posted October 31, 2017 Once you have all the new Id, copy the column with the formula in and paste special values only over the imageid column You can then copy paste it back in to your respective tables
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now