Here is the situation:
We have an old propriatary database that is used for daily tasks. Not much we can do with it, but we have to use it.
I can create basically a read only connection to it through ODBC.
I would like to, on a timed interval copy certain data for reporting from this slow thing to my SQL server so that I can learn to program, and create some cool reports etc without having to wait on this server all day.
So here is what I don't quite understand.
I had originally planned on just deleting the contents of the tbl on my SQL server just before I populated it each time, but found out that my AutoNumber field will continue to increase, and I'm assuming that eventually I'm going to run into a problem as a result.
Should I be doing some kind of update instead? if so do I need to first CHECK if the record exisit, if not then do an insert, if so do an update type thing?
Or is there a way to basically do it in one command?
I hope this makes sense. I would show you some code but there really isn't much to show you other than my insert statement :->
Thanks for any advice!
Josh
Well, the quickest and easiest answer will solve the first problem thatyou mentioned. If you use TRUNCATE TABLE yourTableName instead ofDELETE FROM yourTableName, you'll reseed the identity value and it'llstart all over from the beginning. As an added bonus, thisperforms better, with less overhead than using DELETE.
|||
This "AutoNumber field" is better known as an identity field. It is used in conjuction with a data-type such as int, or bigint. It automates the unique identification of records in a table. It is usually associated with the Primary Key field of a table. If you want to truely replicate the data across different instances, then you should preserve this number. This is especially important if you have other tables that reference these records, which you are also trying to replicate now or later. You can do this by updating the fields of the records based on this key field.
By the way... with an integer (SQL 2000) data-type you have about 2 billion records until you'll need to worry about running out of numbers. If you're getting that much data in, then you should look into data-warehousing concepts.
Example of update statement: update tablename set field2 = infield2 where keyfield1 = inkeyfield1, ...
Adam
Xanderno wrote:
Well, the quickest and easiest answer will solve the first problem thatyou mentioned. If you use TRUNCATE TABLE yourTableName instead ofDELETE FROM yourTableName, you'll reseed the identity value and it'llstart all over from the beginning. As an added bonus, thisperforms better, with less overhead than using DELETE.
Just be careful with TRUNCATE TABLE. This is a non-loggedoperation. If you are doing something like transaction logshipping this will throw a major wrench into it. This is likelynot an issue for you, but I have run into the problem so I thought I'dmention it.
The alternative is a DELETE with a subsequent DBCC CHECKIDENT to resetthe identity seed value. As Xander says, this will incur moreoverhead than a simple TRUNCATE TABLE.
|||Additionally, if you have records in your table which are related toother tables in the database by that identity value, the best course ofaction is an in-place UPDATE, with an INSERT for new records and aDELETE of records that have disappeared from the data source. FWIW Iactually converted a daily import procedure from the "delete allrecords and insert fresh" approach to the UPDATE/INSERT/DELETE approachand was surprised how quick it still ran (for about 6000records). (This was the same stored procedure in which I triedthe TRUNCATE TABLE, incurring the wrath of the DBA.)
|||Really, I have to wonder if you need the identity column at all. If it's only there for looks, (and I'd imagine that it is, since you'reasking about how large it'll get rather than how to maintain it for agiven row across imports) why not do away with it entirely? Ifit's just a static lookup table, and the only reason for importing itinto SQL Server is to reduce overhead, I'd suggest that the SQL Servertable not contain *anything* other than what you have in the originaltable.
As to how you get it into SQL Server, I'll leave it to you to decidewhether truncating the table, or updating/inserting new and changedrows will be most efficient, but do look at both options and keep thecaveats that Terri mentioned in mind.
