Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
liddlem
Starting Member
1 Post |
 Posted - 2009-10-25 : 22:05:53
|
| Hi FolksWhile I have a little experience in VBS and Access databases, I am an absolute newbie to SQL.I have a SQL database that displays the data to the end user via a 3rd party app. In order to ensure that records are unique, they assign a running records number (RRN) - like an Autonumber in an access db. (Does SQL do this too?) Their app then stores the latest number in a text file. (Outside the database) I get the idea that when adding a new record, somehow the MAX(RRn) is returned to the 3-rd party front end and compared to the value in their text file. If the Txtfile.value < MAX(RRn) a script error occurs.Due to the nature of our business, I often need to manipulate some of the data in the tables. In the process, Its easier to reset the RRN values (and update the txt file) than checking what the existing max value is and continuing the series.Within the SQL database, I have 2 sets of the same data. (LVDATA and TSTDATA)So each tablename in the LIVE site is preceded with "LVDATA_" & TableName. Ditto for the TEST Data.While you're thinking about this, is this what a SCHEMA is?To ensure that all txt files are updated correctly, I need a script that reads the MAX(RRn) from each table (in either LIVE or TST) and then updates the relevant txt file accordingly.I presume that (conceptually) the code would look something like this.I dont know how to put it into SQL syntax.for each table in somethinghere - What goes in here? select max(RRN) from Table[n] - how do I say "TSTDATA_" & TableName ? Write Max(RRn) to-the-txtfile - Overwrite existing value. - Do I need to assign MAX(RRn) to a variable first? How?next tableAfter doing a little research, it seems that it might be good to save this as a stored procedure? I will investigate that too.Thanks for any assistance. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-10-25 : 23:26:08
|
| this sounds like a problem waiting to happen.have a look at IDENTITY column attributes and @@IDENTITY and Scope_Identity() functions in BOL.Identity columns are like autonumber in Access. Checking the Max(id) and writing it to a file is going to cause a race condition unless it's in a transaction -- which isn't necessary and can be a performance issue.Better to have the INSERT stored proc return the Scope_Identity() |
 |
|
|
|
|
|
Select MAX from all tables and update txt file