Updating Existing Records Based on Dates

[Stuugie]

Hi all,

I have a table called tblCPI that holds approximately 160,000 records that have to do with Canadian CPI. I have created a process that did the initial data load into it and now I need help figuring out how to update it according to date values. For instance, say we have a date range in the table from Jan 2005 to Aug 2012. Now there is an update from our source and I'll need to update the records from Jan 2005 to the Sep 2012.

I know that updating pre-existing records like this is not generally accepted but Stats Canada revises a lot of historical records on their end so I have to do the same on my end. I was thinking that using dates as comparators would be the way to go but my skills (so far) are lacking with regards to writing SQL statements and I don't know how to write:

If srcDate = dbo.tblCPI.Variable_Date then update dbo.tblCPI.Variable_Value Else do nothing.

Any guidance would be very much appreciated.

[BitBlt]

A little more detail is required. Where does the value in variable srcDate come from? How are the update values being supplied? What are you updating the column dbo.tblCPI.VariableValue to?

Depending on the answers to those questions, a solution could vary wildly.

One little pointer, though: you seem to be thinking row-at-a-time rather than set-at-a-time...get a row, decide what to do, do something (or not), get the next row, decide what to do...etc. This is also knows as the RBAR technique (as in "Row By Agonizing Row") and generally will not perform as well as relational updating.

[Stuugie]

Hi BitBit,

SrcDate comes from a flat csv file that is auto-downloaded from the interwebs, which I hope answers question 2. The dbo.tblCPI.Variable_Value is just a Double type value that gets updated on a monthly basis/changes on a monthly basis.

LoL, I definitely would like this update to happen as efficiently as possible but I'm still learning and thought that I would have to use RBAR technique. I would love to be able to just update by sets but I haven't a clue as to how to go about doing that.

So much to learn and from a user standpoint, I can't do it quick enough.

[BitBlt]

Of course...users do tend to be a little impatient at times :-)

As far as being able to update by sets, is it possible for you to load the CSV into a table on your database, then use that to join back to your set you want updated? For instance, using SSIS to read the file? (There is a wizard you can use to import the data, then you can tweak it to perform whatever updates you want.) How many rows are usually in the CSV file? Does it have a key value?

Is that Variable_Value just one value that gets applied across multiple rows, or is there a separate value for each interesting row? More detail will help us guide you.

[Stuugie]

Thanks so much for your time on this. I am using SSIS but I haven't delved into the wizard yet. This particular csv file has about 160,000 rows and yes there are key values. As for the variable value, there are separate values for each (lol) interesting row, as per dates used. Example: Jan 2005's variable value may be 201.03, Feb 2005's value may be 202.05, and so on until the most recent date in the set.

I will look closer at my SSIS options but as it is I thought I was going to have to create a temp table, load it from the csv, and then load that into the database. If that is the case, my SQL programming is still lacking on how to do such a thing. Thank goodness for the interwebs and people like you.

[BitBlt]

Woo hoo! Long live the InterWeb!

Yes, SSIS has some very powerful features, but it does require some study and practice to use it effectively. The nice thing about the Import Wizard is that you can put together a "tinkertoy" version of your import, save it for later, then add features and functionality layer by layer. I don't know if you have spent any time with SQL2000, but the precursor to SSIS was a little feature called DTS. It was much simpler and easier to use, but much less powerful.

I suggest that you import your file with the Wizard the first time, and use the table definition created by SSIS as a "permanent temporary table" in a separate staging database. Once you have that first go-round completed, you can open up the package with BIDS (That is, "SQL Server Business Intelligence Development Studio" to open up the package and tweak it to do more of what you want.

In the short term, once you get your data loaded into ANY database, you then have the full power of the relational engine to join tables and do your updates with a single SQL statement.

Bottom line is it's going to take a little initiative on your part to investigate these tools. I don't know if you're in a position to do that, or if there is anyone in your organization/environment who can help out. All I can do is point out that a little work now will pay major dividends later, because you just KNOW that if you succeed with this, you will be "rewarded" with more impossible tasks. Welcome to Information Technology! Whee!

Anyway, best of luck. Finding DaniWeb was your best move so far. :-)

[Stuugie]

I have been working with SSIS over the summer and have already created a package that did the initial download of the csv file, transformed it into a format that suited the db, and then loaded the db.

SSIS is a corporate tool that any one of the 1000s of employees can use here but as far as I know, there are only a handful that do. Most departments use either Excel, Access, or Oracle depending on their requirements.

Personally, I like SSIS but there just doesn't seem to be much support for it here so I am somewhat on my own. Because of that, I am coupling SSIS 2008 with Visual Studio 2010 as well as using the built in BIDS offered by SQL Server.

I am going to try the wizard out today to see how I fair with it and if all goes well I will do as you recommend and build onto it as needed.

Also, DaniWeb has been such a valued website for me and I am very grateful of its existence.

Thanks you very much again for your advice and I'll let you know how things go for me.