Auto id''s? is it possible to recalculate?
-
I've a flat database with 6 fields, the last one being an "id" field i.e set to INT identity YES(not for replication). This works great for me, but now I need to insert some records in the middle of the list, can this be achieved? maintaining the sort list?
-
Try this:
SET IDENTITTY_INSERT <schema name>.<table name> ON
--your insert statements goes here
SET IDENTITY_INSERT <schema name>.<table name> OFF
Paul
-
Thanks for the quick response Paul, but I don't understand what this actually does.
What I'd like to do if at all possible is insert all the new records I need to, delete the ID field, then something like select * from
order by field1 field2,field3,field4
then add the id field back again so everything is in order again, is that possible?
-
Mick,
Yeah, it can be done but I gotta ask... doesn't the ID field mean anything?
If you do this, any joins that reference the table by ID will be totally out of whack... the ID order of the rows in an RDBMS just shouldn't matter... --Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff, the thing is although the database already has just over a thousand records it's still only in it's early stages and as yet isn't being used, I'd only just started it when I realised I'd omitted a set of records. Any help on how to achieve what I've asked for would be appreciated
-
Roger that... still shouldn't matter but "can do"...
This obviously won't work if foreign keys exist...
First things first... make a "backup" of the table just in case...
SELECT * INTO DeleteMe FROM YourTable
The rest is easy because TRUNCATE resets the IDENTITY property to the initial seed...
--===== Copy the data from the source to a temp table SELECT Field1,Field2,Field3,Field4,Field5 --Do NOT include the ID column INTO #MyHead FROM YourTable
--===== Trucate the source table (Resets the ID seed) TRUNCATE TABLE YourTable
--===== Copy the data back to the source table in the correct order INSERT INTO YourTable (Field1,Field2,Field3,Field4,Field5) SELECT Field1,Field2,Field3,Field4,Field5 FROM #MyHead ORDER BY Field1,Field2,Field3,Field4
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Many thanks Jeff, that's very handy. I will take your comments on board.
Mick
-
you may need a few extra steps if it's important to insert the set you missed into the MIDDLE of the existing identity sequence. Since Jeff has so nicely started some code - I'll take the libery to "add" to his code.
Edits inline....
First things first... make a "backup" of the table just in case...
SELECT * INTO DeleteMe FROM YourTable
The rest is easy because TRUNCATE resets the IDENTITY property to the initial seed...
--===== Copy the data from the source to a temp table SELECT Field1,Field2,Field3,Field4,Field5, id --Do include the ID column for the where clause later INTO #MyHead FROM YourTable
--===== Trucate the source table (Resets the ID seed) TRUNCATE TABLE YourTable
--===== Copy the data back to the source table in the correct order INSERT INTO YourTable (Field1,Field2,Field3,Field4,Field5) SELECT Field1,Field2,Field3,Field4,Field5 FROM #MyHead
where id< 1234 --- where 1234 is the last id beofre the spot where you wish to do the insert ORDER BY Field1,Field2,Field3,Field4
--===== insert the data from the NEW table to the source table in the correct order INSERT INTO YourTable (Field1,Field2,Field3,Field4,Field5) SELECT Field1,Field2,Field3,Field4,Field5 FROM MyForgotSequenceTable
ORDER BY Field1,Field2,Field3,Field4
--=====Finish the Copy the data back to the source table in the correct order INSERT INTO YourTable (Field1,Field2,Field3,Field4,Field5) SELECT Field1,Field2,Field3,Field4,Field5 FROM #MyHead
where id>= 1234 --- where 1234 is the last id beofre the spot where you wish to do the insert ORDER BY Field1,Field2,Field3,Field4
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
thanks Matt, that's great
-
Ah... Matt's right... I left out a step... I should have said to insert all your missing rows into YourTable first, then run my code... don't need to add extra code if you do it that way...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff, you've both helped
-
You bet, Mick. Thanks for the feedback...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply