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 |
|
scootermcfly
Yak Posting Veteran
66 Posts |
 Posted - 2003-02-27 : 16:31:09
|
| Currently, when I have to add a column to a table, I use something like:IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'test' AND column_name = 'Col6' )ALTER TABLE test ADD Col2 varchar(1) NULLGOIs there a better way to make sure that the column does not already exist before executing?And, is there any way that I can force the column that I am adding to be say the fourth column instead of always the last?So if I had a table test that was:Create table test(Col1 varchar(1) NULL,Col3 varchar(1) NULL)and I ran the script above, Col2 will be at the end of the table. Is there anyway that I can force it to be between Col1 and Col3?Thanks,Scooter McFly |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-27 : 16:37:51
|
| If you want to change the order of the columns, you will have to do a create and a drop. You will also have to move your data into a temporary location so that you don't lose it. If you need help on this, please let us know.The way that you are checking to see if a column exists already is the best way that there is (well at least that I can think of).Edited by - tduggan on 02/27/2003 16:38:17 |
 |
|
|
scootermcfly
Yak Posting Veteran
66 Posts |
Posted - 2003-02-27 : 16:52:54
|
| Yeah, I figured I would have to move the data out of the table, drop the current and then reload it. I just didn't know if there was a easy way to reorder the columns.Thanks,Scooter McFly |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-02-27 : 16:53:58
|
| How about (there are many things that can go wrong with automation like this. I prefer to be a little more careful with altering objects, but..)Declare @sql varchar(4000), @Counter int, @NewColorder intSelect @Counter = 1, @NewColOrder = 2IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'test' AND column_name = 'Col2' ) BEGINSELECT * FROM FROM INFORMATION_SCHEMA.COLUMNS WHERE Into #temp table_name = 'Test'If @@Error <> 0 -- perform error handlingSELECT * FROM Test Into Test_bkpIf @@Error <> 0 -- perform error handlingDECLARE CSR1 CURSOR For SELECT * (use column names) From #tempSelect @SQL = 'CREATE TABLE ' + Distinct(TABLE_NAME) FROM #TempetcLoop through Cursor, continue to build @SQL, when you find the location, add your new column...yada yada yadaAll very time consuming, it is just easier to manage the database with simple DDL.Instead I reccomend creating a script, this way you can test it in dev, and release the same script to production:Select * Into Test_bkp from TestgoDrop Table TestGoCreate table Test (Col1 int, col2 int, col3 int) on [PRIMARY]GoInsert Into Test (Col1, null, Col3)Select From Test_bkpGoDrop Table Test_bkpGoAny way, a lot more typing then I thought.Good LuckBrett 8-) |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-02-28 : 07:24:28
|
| Just keep in mind Codd's Information Rule: 'all information in the database to be represented in one and only one way, namely by values in column positions within rows of tables.'There is no (and should not be) consideration for a column's ordinal position in RDBMS.Jay White{0} |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-02-28 : 07:48:31
|
| Along the lines of what Jay said, you should be adding ROWS to your tables as data is collected, NOT columns ... Altering database objects in this manner is probably not the best design.Give us more info on why you are doing this, and maybe someone can come up with a better solution for you than manipulating columns in a table.Example:If you have a table of "Student Test Scores" and are storing test scores in a table like:StudentID, Test1Date, Test1Score, Test2Date, Test2Score, Test3Date, Test3Score, etc.....Then that might explain why you have the need to add columns to your tables regularly -- to accomidate more test scores for each student.Instead, however, consider storing the data like this:StudentID, TestDate, ScoreThen you can add as many test scores as needed, and also any other attributes for that test (class, date, subject, etc.) without ever adding columns to your data. Also, you can then query your data easily to get the # of tests per student, avg student score, etc, using regular old SQL aggregate functions over ROWS instead of adding up and counting columns in your data.Sorry if this doesn't apply to you and/or you know this already, but it's so important I just wanted to make sure it was mentioned.- JeffEdited by - jsmith8858 on 02/28/2003 07:49:54 |
|
|
|
scootermcfly
Yak Posting Veteran
66 Posts |
Posted - 2003-02-28 : 12:16:05
|
| Yeah, i knew all the stuff about positional ordering of data columns.Reason for the post was, I am creating some alter scripts, and if a developer somewhere along the way was using an insert into values( ) statement, I wanted to make sure that the table was in the order that they thought it was. They know better, and have been told many times, but every now and then i catch something that fails and they think it is because of the db. After review, seems their code is what caused the problem.Thanks,Scooter McFly |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-02-28 : 14:13:34
|
| I can't TELL developers often enough. The syntax is:INSERT INTO (COL1,COL2,ect) VALUES(1,2,ect) OrINSERT INTO (COL1,COL2,ect) SELECT COL1, COL2, ect FROM TABLEAlso another pet peeve is SELECT * FROM TABLESorry, just kinda bugs meBrett8-) Edited by - x002548 on 02/28/2003 14:57:14 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-28 : 14:18:33
|
| The developer should change their code to correct the problem. You should not change a table layout in order to get a developer's code to work. They should be using a column list so that it doesn't matter what order the columns are physically stored in the table. |
|
|
|
|
|
|
|
|
Altering table columns