SQL Insert - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Insert

Author

Topic

cjonline
Yak Posting Veteran

55 Posts

Posted - 2005-04-14 : 06:17:52

Hi,
I have a table with alot of data in it.. the first column is called QuestionNo. There are about 120 questions listed in this table from 1 - 120.

I would like to be able to amend this table at say question 80 and add a few more questions. How do I renumber the remainder of the questions to keep consitency?
Thanks

Craig.

mfemenel
Professor Frink

1421 Posts

Posted - 2005-04-14 : 06:28:41

You can tackle this with a simple loop. Here's the steps.
1. Create a table which mirrors the table you're trying to restructure. I'm going to call that table "questions" and your copy "questions_copy". Do not make your questionno in questions_copy an identity column. Just make it an int for now. We're doing this so if you screw something up you screw it up in the copy. Always a good idea when rearranging data.
2. Insert questions 1-80 into questions_copy.
3. Alter questions_copy and make question_num an identity column identity(81,1)
4. Insert your new records into questions_copy. Since you completed step 3 it will start numbering at 81.
5. Insert the rest of your records (81-120) from questions into question_num. When you write your select statement from the questions table make sure you order by question_num asc so that they get inserted in the right order and get renumbered correctly. You could also write a while loop and go through each one starting your counter at 81 and going until the counter=120. Either way will work but the largere insert statement will be faster.

Mike
"oh, that monkey is going to pay"

cjonline
Yak Posting Veteran

55 Posts

Posted - 2005-04-14 : 06:42:52

wow.. dont think I'm good enough to work that one out..

any examples of t-sql code to do this.

lets say the table is 3 columns, questionno, question,options
1 - 130 are already placed in table. I need to insert 3 new rows at question 80.

REgards,
Craig.

andy8979
Starting Member

36 Posts

Posted - 2005-04-14 : 09:37:31

Could you please give some more detials of the columns in the table, like is it an identity column, or the foreign key restrictions so that it would be helpful.

Anuj

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-14 : 10:10:22

Something like this maybe


CREATE TABLE NewQuestionTable (QuestionNo int identity(1,1), Question varchar(100), Options varchar(100)
GO

--INSERT Questions 1 - 80 from old table
INSERT NewQuestionTable (Question, Options)
SELECT Question, Options
FROM OldQuestionTable
WHERE QuestionNo <= 80
ORDER BY QuestionNo
GO

--INSERT 3 New Questions
INSERT NewQuestionTable (Question, Options)
SELECT 'New Question 1', 'A' UNION ALL
SELECT 'New Question 2', 'B' UNION ALL
SELECT 'New Question 3', 'C'
GO

--INSERT remaining questions from old table
INSERT NewQuestionTable (Question, Options)
SELECT Question, Options
FROM OldQuestionTable
WHERE QuestionNo >= 81
ORDER BY QuestionNo
GO

--DROP OLD Table
DROP TABLE OldQuestionTable
GO

--Rename NEW table to the old table name
EXEC sp_rename 'NewQuestionTable', 'OldQuestionTable'
GO

You need to take into account any primary keys, foreign keys constraints etc on the old table

Andy

Beauty is in the eyes of the beerholder

mfemenel
Professor Frink

1421 Posts

Posted - 2005-04-14 : 11:22:53

cj-
You have to give stuff like this a try and not be afraid to make some mistakes. No one starts off knowing everything. I know some people on this site(rvolk) are good at this because they've screwed up and learned from it.

p.s. You're welcome Rob!

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources