Well, how do you do it? I've not seen any report on this... so could it be next to impossible?
Say you have 10 records, 1-10 and you want to insert a new record between record 4 and record 5 in a SQL database, just how do you code it? And oh, each record uses the auto ID increment feature.
Here's an idea:
CREATE TABLE MyTable (id int identity(1,1), col1 varchar(16))
GO
INSERT INTO MyTable SELECT 'value 01'
INSERT INTO MyTable SELECT 'value 02'
INSERT INTO MyTable SELECT 'value 03'
INSERT INTO MyTable SELECT 'value 04'
INSERT INTO MyTable SELECT 'value 05'
INSERT INTO MyTable SELECT 'value 06'
INSERT INTO MyTable SELECT 'value 07'
INSERT INTO MyTable SELECT 'value 08'
INSERT INTO MyTable SELECT 'value 09'
INSERT INTO MyTable SELECT 'value 10'
GO
CREATE TABLE tmpMyTable (id int identity(1,1), col1 varchar(16))
GO
SET IDENTITY_INSERT tmpMyTable ON
GO
INSERT INTO tmpMyTable (id,col1)
SELECT id, col1
FROM MyTable
WHERE id < 5
INSERT INTO tmpMyTable (id,col1) SELECT 5,'value between'
GO
INSERT INTO tmpMyTable (id,col1)
SELECT id+1, col1
FROM MyTable
WHERE id > 4
SET IDENTITY_INSERT tmpMyTable OFF
GO
DROP TABLE MyTable
GO
EXEC sp_rename 'tmpMyTable', 'MyTable'
GO
SELECT * FROM MyTable
|||
Thanks... I'm not very good at SQL but I figure, you create a new table, add those values to it and them insert them back into the old table?
If I can renumber the ID, that would be fine. I deleted record #2 and when I leave record one (view), it still stops where record #2 was deleted. I have to click again to go to record 3. I don't know why it just doesn't jump to record #3, seeing that #2 is deleted.
|||
Take a look at your other question...
Renumbering ID fields is BAD... I really mean it. It can only be done via another table to temporary store the data. There should be no reason to renumber IDs anyway, and if there IS a reson, then the field should not ba an ID ;)
|||
...ooops! That would be "delete" the old table and make the new table the current table.
Okay, ok, this can solve my problem, how can I advance to the next record using code only? I programed my app to go by ID, so if ID #2 is deleted, nothing happens because #2 is deleted. I have to click until the program counter is up to a ID value that isn't deleted before any records show up.
If I say (not really my code, just for example purposes)
CurrRec=CurrRec+1
Select Data from Table1 WHERE ID = CurrRec
Read
While CurrRec is NullDB then
CurrRec=CurrRec+1
Read? 'I could use a read to get pass the deleted record ID
'however, there's a problem if CurrRec > total records
'because it's at the end of the DB
end While
I could put in a time out loop that exits if no record is found in x tries... but surely there must be a way to jump to the NEXT RECORD and a END OF RECORD and so on.
Any takers?
|||
well to move through your records you should do something like this...
Dim oreader As SqlClient.SqlDataReader
Dim oCmd As SqlClient.SqlCommand
oCmd.CommandText = "Select * from mytable"
oreader = oCmd.ExecuteReader
While oreader.Read
'dostuff
End While
|||
I do also need to... move.first, move.last, move.next, ect. With the currRec to move next, I add 1 to currRec, to move back, I subtract 1, to move first, currRec=1.
I'm now thinking I can using CTE functions, if only I can find the syntax for them. That way, I don't use the ID, just the record number.
Thanks.
|||
zdrae,
To select each record, one record at a time, you can use the Top keyword. Each time you get back a record, just save the ID in a variable so that you may use it to select the next record.
EXAMPLE:
Select Top 1 Data from Table1 Where ID > prevID Order by ID
The idea is to select the next record who has an ID greater than the current ID.
Also, just FYI, I think some people in these forums are confused by your use of the term "ID". It seems to me that you really only mean an identity column, but the other posters in this forum are right that you should not attempt to renumber an identity field.
You may be used to programming in a "line-by-line" oriented way, but SQL is really very different and powerful as a data manipulation language. After using it for a while, you'll love how simple and powerful SQL is compared to "line-by-line" data processing.
Hope this helps...
|||
Actually, I didn't like SQL one bit... until I figured out what should actually be in the connection string (I finally got it open); but still I wasn't able to get any data out until I found that I was missing a read statement.
It's on my notebook and the speed is terrible... but everything is slow on it anyway... even games. Thanks.
|||This works great going forward. Any ideas on going backwards? I could store previous IDs but that won't do any good once it goes off the reservation.|||
Instead of a Datareader you could use the Datareader to fill a Dataset or a Datatable.
Those 2 objects will store the data permanently untill you discard them. By doing so they will allow you to scroll foreward and backwards (Or accessing any record you retrieved)
But be aware that you allways should only fetch those records you need...
|||
Yeah, I'm going to put more investment into tableadapters/binding... I just have to figure out how to program it to get the results I need. Wish there was a book... so now I have to make do.
Thanks.
|||
SQL 2005 has a new function called ROW_NUMER. This generates a sequential number, based on another column in the table. So it may eliminate the need for inserting in the middle of the table, and renumbering the IDs. Sample usage:
Select ROW_NUMBER() over (order by MyColumn) as 'SequenceNumber', MyColumn from MyTable
hth
Fluffy
|||
Hi,
As a lot of other replies have said updating an identity column's value is bad. You certainly should not be doing it as part of normal operation of whatever application you are writing to access the database.
Perhaps if you gave a bit of information about what you are actually trying to do with your application in this situation then someone could suggest a better way of doing it. From what you've said so far it simply sounds like you haven't grasped how SQL works and are trying to doing things in an odd way.
Bye,
Dev.
|||In the first place, there's no concept of up or down in space, and there's no concept of 'middle' in a relational database. Indeed, the very essence of a relational database is that the physical location of the rows is irrelevant. So you can't talk about 'inserting a record into the middle of a database' and be talking, sensibly, about a relational database at all. Records go where they go, and that's all there is to it: you leave it up to the RDBMS to work out how to retrieve them, and display them in a user-defined order, when you issue SQL queries against the database.
Second, autoincrementing columns are used to provide meaningless surrogate keys. You suddenly want to start insisting on inserting a new number in between a series of pre-existing auto-incremented ones and what you are actually telling me is that you are reading meaning into what is meant to be, and what was designed to be, meaningless. If you begin changing the auto-assigned numbers to make room for a new entry, and you're actually updating a primary key... and that's just a relational no-no, usually. If the numbers assigned have to have a specific value, they mean something to you, they cannot therefore be meaningless and you shouldn't therefore have used an autoincrementing column to capture them. If, on the other hand, you are happy with autoincrementing numbers, then be happy with them whatever they may be for a particular record, and the question you ask then becomes redundant.
And those who have answered with code samples proposing turning autoincrementing off for a bit, updating the formerly autoincrementing column to make space for the new insert, and the rest... well, frankly, they've missed the point. The request makes no sense as written, demonstrates a fundamental misunderstanding of relational databases and their use, and cannot sensibly be answered.
If there's a specific business problem to be dealt with, that's a different matter ...and one that almost inevitably will come down to fixing a poor application design or being clearer on poorly-understood business requirements.
SimonSa wrote: