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 |
|
cuetan
Starting Member
8 Posts |
 Posted - 2002-05-08 : 18:35:34
|
| I am trying to kill two birds with one stone here.1) I am trying to use SQL Statements to achive the following...USE Texas_Golf_ResultsGOINSERT INTO Society_Groups(Society_Leader_Name, Society_Group_Desc)VALUES ('Jack Mason', 'Bedford Junior Blues')my first question is that how would it be possible for me to put multiple Society_Leader_Name, Society_Group_Desc. Like if I had 100 of each, instead of running this statement 100 times.2)Now I have an Identity set on the Society_Group_Id, which obviously gives auto id's. Now for example if I wanted to delete the 10th record from this table, how would I am make sure that the next id would continue from the 10th and not jump to 11. This way I would not have a 9th and then 11th record. I tried using this command:DELETE FROM Society_GroupsDBCC CHECKIDENT (Society_Groups,RESEED,10)But then it deletes all the records from the table. Any advice...please helpThanks in advance.Chetan |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-08 : 20:22:18
|
| Some SQL products (like MySQL) allow you to perform multiple-row INSERTs with one INSERT statement, but unfortunately SQL Server doesn't. The only way to INSERT multiple rows with one statement is to use the INSERT...SELECT syntax. If you already have this data in another table, then you can INSERT it into Society_Groups using this syntax.As far as gaps in identity values, when you think about it, it's logical for these gaps to exist. If you delete a row identified by the number 10, it makes no sense for a completely new and different row to be numbered 10. Identity values are NOT the same as row numbers, and the concept of a row number doesn't exist in relational databases (and is meaningless anyway) This is one of the reasons why identity columns shouldn't be used as a primary key (I know, I know, I'm not supposed to talk about this!)If you are going to use identity as your primary key, you should either accept the gaps that come from DELETE operations, or risk having data that could be inconsistent. And you should absolutely stop thinking of rows having a "number" or position. Rows are identified by the values they hold, NOT their position in a table. SQL Server (and other RDBMS software) will continue to frustrate you if you think of data in this way. |
 |
|
|
|
|
|
|
|
RESEEDING