MS SQL 2005 How to Insert / Create missing Records in Existing dbo (table)

How do I simply insert records into a MS SQL 2005 database object (table) that were not created becaus ethe MS SQL server was disconnected at the time it was suppose dto receive and archive data?
Who is Participating?

Improve company productivity with a Business Account.Sign Up

elliotsegalConnect With a Mentor Author Commented:
All concerned,

Due to workload and priorities, I'm going to have to back-burner this issue and resume at a later date.  Let's close it out and pick it up later.  Thanks for the help thus far.
Do you have the data is it in a csv format?
you can use a bulk insert you could use bcp it depends on how the data that you want to add is arranged/formated and what options are available to you.
Ya, we're going to need a lot more information than that. Generally speaking, if an insert is attempted when the database is unavailable, its not logged and there's no way to recover it.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

elliotsegalAuthor Commented:
Derek & Arnold,

This doesn't happen often but when it does it is very time consuming to manually enter, etc.

I'll get try and get the normal procedure(s) I use to update posted here.  Major problem as you stated is that there's no way to recover when the RDBMS is unavaillable.

This database object procedure is used with a SCADA system for it's historical database, many entried evry hour, minute, etc.  

What I've been doing is manually using Excel ith MS Query to edit the table(s) where the record/entry for a particular hour is missing because either the RDBMS or the SCADA system application(s) were disconnected/unavailable.

I could prep the data in a .csv format , but the real issue (I think) is that I'll have a record for 15:00 but at 16:00 the disconnect occured and no records ar in the .dbo (table) for that hour at all.  What's the best way to at least make the records for 16:00 and then I can copy the actual data/values to the .dbo/table using some VBA script/macros in Excel?


Using excel/macros is a possibility.

Are your databases unavailable often? Are these unexpected outages? I'd look more into the cause for the unavailability than working around it if possible.
You could see whether a detection is an option within the scada app that will in a case where the DB is unavailable to add the entries into a file which can then be imported when the database becomes available by a cron/scheduled task job checking for the server.
elliotsegalAuthor Commented:
Derek & Arnold,

Good points on both. Here's more background:

The database isn't unavailable often, but when it is unavailable it's invasive and time consuming to create records and then insert.  I'm sure most of that is due to my lack-of-skil/knowledge and not thinking out of the box.

The cause is generally a catastrophic event (i.e., online/production SQL Server going offline).  The MS SQL server is on the same server(s) as my SCADA/EMS apps.

 [I have four servers with MS SQL all with the same DBs on it.

I use snap-shots to keep the other three insync and up-to-date with the online/production server.  One of the serves is running as back-up and will failover as required, the other two servers are Web servers distrubuting Java Web Apps, etc, for end user access via VPN, firewall to firewall, through initial access at a Proxy server.  

I do like the idea of a scheduled task to check for and when an insert wasn't done (currently scheduled on the hour with a 5-minute offset to allow the SCAD/EMS to do its front-end processing and have enough time to output to MS SQL into a historical database's .dbo's (i.e., tables).  Each .dbo is basically the current month's values.  I generate new .dbo's for the upcomming month o/a the 20th of each month even thoughthere's no live data getting to it yet.  

Isuue I may have is "buffering" the values from the SCAD/EMS front-end until the MS SQL Databse came back online, in order to have data to import after records in the tables were created.

Right now, if the online/production server crashed for some reason at the top of the hour througgh 5-minutes past the hour, then I don't have any place to insert the data because that hour that just past records were'nt created in the .dbo (table).  

It would appear as 01:00, 02:00, 03:00, 05:00.... if the crash/problem occured at the "hour ending" at 05:00.  Meaning that the data for the period 04:00:00 - 04:59:59 doesn't get written/archived and the next set of records wouldn't occur until it was time to do this for 05:00:00 - 05:59:59.

Adding to this, there are several .dbo's for each month (i.e., there's an hourly archive, a 5-minute archive, 1-minute archive, etc.)  these tables represent the different data acquired from the SCADA /EMS at different intervals.  (How much energy was used for an entire hour, what the status of NOx was from a generation plant minute-by-minute, etc.).

Any ideas?

Do you have an option to have the insert run more frequently such that it does not try to jam a large amount of data at the same time and perhaps that is the time when your snapshot runs making hings worse?
elliotsegalAuthor Commented:
Yes, the insert check could be scheduled prior to the snapshots (once before each snapshot interval).  I belive that could work where:

1. Insert check for previous hour's records were created in the .dbo (table)
2. If records aren't present, create them and then data values could either be copied from the last good hour's records or manually inserted (e.g., macro run in Excel uisng MS Query, etc.).

 Basically I'm thinking that if the "insert records" check shows that the subject hour's records weren't created, it would create them, but with no data values in them (they'd be nulls / "0").

If for some reason the front-end process didn't have value sot populate the newly created records, then the insert script would copy the previous hour's values (better than nulls / "0") as a last resort, which would at least maintain contents and reasonable historical values for trending and forecasting purposes.

Any specific values could always be manually inserted after the records are created, for example, the status indication (i.e., on/off, good/bad, fail/normal) for specific conitions (i.e., a generator on/offline, status of a switch or circuit breaker, etc.).

Now the hard part, scripting this!

What do you think?
What are the available options that you currently use?
Does it have an option to check whether the connection to the database is available?
Do you have an option to do two things, i.e. attempt to insert data into the database while at the same time, outputing the data into a flat file (CSV, tsv, etc.) this way you can have a separate script run using the data from the file to see wther the data exists in the table and if not insert the record.  Presumably you have duplicate cancelation such that an entry using the same timestamp will be rejected as a duplicate.

Which programming languages are you comfortable with VB, C#, perl, etc.
elliotsegalAuthor Commented:

I'm not a coding Ace, but most of my work is done in C# and VBA.

I do currently have an option to check if the DB is available and an error will generate when attempting to connect; I've got to look closer at that to see exactly what's going on.  I belive that is being"checked" from the SCADA application and not at SQL Svr.

I do have duplicate cancellation set-up, and allow nulls (expect for primariy keys)

As far as the insert I'm going to have to get more detail/access the SCADA application code/script to see how that is happening;
elliotsegalAuthor Commented:
Stopped work on project due to othe rhigher priorities
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.