August 21, 2016 at 10:00 am
Hoping someone can point me in the right direction, we use a database to log alarms and trend data. We are working with a client that want's all of there old data imported. Normally I have an excel spread sheet that generates the code similar to below. However the client gave me a huge database that I got the tables moved over into ours but I can't figure out how to move it from there into our multiple tables. Normally I run 3 sets of code, one for each table.
So basically I need to pull data from dbo.old_data and check if it exist by index of case_01, if not copy the data from various columns to three different tables. If it does exist update it, they are still collecting data so I will have to do this again but with less data.
Thank you in advance for your help, I'm a plc programmer so sorry for not knowing the proper terms.
if not exists(select * from EPA001 where rtrim(CASE_01) = '000001')
insert into EPA001([CASE_01], [CLASS], [SUBCLASS]) select '000001', 'FLR_01', 'CRUDE'
else
update EPA001 set CLASS='FLR_01', SUBCLASS='CRUDE'
August 21, 2016 at 11:47 am
Thank you I will take a look into this.
August 21, 2016 at 12:14 pm
Don't be afraid to read through the input data three times. Reading through a table is very fast compared to the cost of doing individual inserts.
Also, you might want to read up on the MERGE operation, which handles both inserts and updates at the same time. If you go with the MERGE, you'll want to make sure your input data is sorted into the same sequence as the target data.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 21, 2016 at 1:01 pm
Thank you for the help most of the time we are dealing with under 20k records, this client wants there data from the last 12 years which is over 500k of records.
August 21, 2016 at 1:45 pm
How about doing the UPDATE on matched data, then doing an INSERT. Just do the whole table at once. (So it would have to be imported into a table in TempDB.)
August 21, 2016 at 1:57 pm
pietlinden (8/21/2016)
How about doing the UPDATE on matched data, then doing an INSERT. Just do the whole table at once. (So it would have to be imported into a table in TempDB.)
Do you have an example, it's one database with around 30 columns not all are needed. I need to basically put this data in 3 other tables all off the same key. Hope this makes since. I will have to do this again once the system goes online but they what to see the current data now, if it duplicates I'm in big trouble.
August 21, 2016 at 4:03 pm
T-SQL Programming Part 9 - Using the MERGE Statement to Perform an UPSERT
Wait, the more I read your post, the less sense it makes. What are you REALLY trying to do? At first, I thought you were trying to update matching records and insert the ones that didn't exist, so I suggested the UPSERT article... but then you said:
"However the client gave me a huge database that I got the tables moved over into ours but I can't figure out how to move it from there into our multiple tables".
So could you explain again what you're trying to do? Do you have table structures? (Please post the CREATE TABLE statements) for all the tables you're using in the question. I'm not quite sure what the question is anymore, so could you please clarify?
August 21, 2016 at 5:03 pm
I'm reading that one large input table needs to be split and merged into three tables which are subsets of the imported table. That isn't complicated. And 500k of input rows isn't a scary amount. I agree with pietlinden, show us the table structures and give us an example of what you want to see happen. Don't describe it, draw us a picture.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 21, 2016 at 5:31 pm
pietlinden (8/21/2016)
How about doing the UPDATE on matched data, then doing an INSERT. Just do the whole table at once. (So it would have to be imported into a table in TempDB.)
I was a little confused by this, so I was asking for a sample. But I get it now that I have re-read the article you sent me. I also copied the database to test it out, I believe this will work. I will also post the create tables info shortly, incase you have a better solution for the future. Thank you for your help.
August 21, 2016 at 10:24 pm
Thank you that functioned well, it was completed in seconds. I guess my version of a lot of data isn't that much. 🙂
Thank you!!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply