Menu
I am trying to alter a table in SQL server with a script. In the past I have always done this kind of thing through a GUI, but now I need to generate a script to do it for customers.
I have an SQL Server database table that is like this:
MyTable ------- ColA int NOT NULL ColB int NOT NULL ColC int NOT NULL ColD VARCHAR(100)
The primary key is defined across ColA, ColB, and ColC.
I want the SQL script to change the table like so:
MyTable ------- ColA int NOT NULL ColB int NOT NULL ColX int NOT NULL (new column, default 0 for existing data) ColC int NOT NULL ColD VARCHAR(100)
The primary key would now be defined by ColA, ColB, ColX, and ColC.
This is easy to do through SQL Server GUI. But when I have it generate a script from that, it seems unnecessarily complex. Basically, the script creates a temporary table with the new schema, copies all the data, indexes, and constraints from the old table into the temp table, deletes the old table, then renames the new one to the name of the old one. In addition, it has lines like this:
ALTER TABLE dbo.Tmp_MyTable ADD CONSTRAINT
MyTable21792984_ColC_DF DEFAULT ((0)) FOR ColC
I'm concerned that these random-looking numbers there (i.e. 21792984) will not be the same on all customer database instances. They look like something that the SQL server generates when creating the database that would be unique to each instance.
Is there a more straight-forward way of changing the table through SQL commands? I've looked online but what I've found is mostly basic and/or generic.
Update: From the answers I have received, it looks like the difficulty lies in putting the new column "in between" two columns. I've realized it doesn't really matter what order the columns are in (if I am wrong feel free to leave an answer correcting me). In my case, the change is much simpler if I just add the column to the end of the table, and nothing in the code is relying on the specific column order.
844
In Microsoft SQL Server, we can change the order of the columns and can add a new column by using ALTER command. ALTER TABLE is used to add, delete/drop or modify columns in the existing table. It is also used to add and drop various constraints on the existing table.
UPDATE table SET columnB = columnA; This will update every row. This will also work if you want to transfer old value to other column and update the first one: UPDATE table SET columnA = 'new value', columnB = columnA . Like other answer says - don't forget the WHERE clause to update only what's needed.
To insert a single column: Right-click the whole column to the right of where you want to add the new column, and then select Insert Columns. To insert multiple columns: Select the same number of columns to the right of where you want to add new ones. Right-click the selection, and then select Insert Columns.
There isn't another way to insert a column in a SQL Server table "in between" existing columns - you need to build a temp table and rebuild the old table. That said, column order shouldn't matter - are you sure that the column needs to be inserted in order?
Likely your best bet is to just use the GUI, script it out, and then change the constraint name to something reasonable within the script. You're right that the numerical constraint name isn't ideal, and it's not a best practice to allow SQL Server to determine your object names.
199
It's not a good practice to depend on any sort of "natural" or "inherent" ordering of columns in a database table. All columns should be referenced by name in any officially generated queries to return columns by name in the order specified by the query. If that rule isn't followed, any future schema changes are an absolute nightmare as the system code may need to be changed every time the database schema is updated.
The only annoying thing here would be when running one-off queries using SELECT * FROM ... on production/test/development databases hand-coded by users and having your new column show up on the end of the column list. However, I believe many query tools will let you reorder the columns from a sort of GUI.
41
If you are inserting a field into the middle of the table, you essentially have to drop the current table and recreate it which is what sql server is doing.
The random numbers are making sure the constraint has a unique name. If you keep the script and run it on multiple databases, then they will all be the same. If you are going to modify each one through the gui, then yes they will most likely be different.
To modify the primary key, all you need to do is find out the primary key constraint name and drop it. Just add a new constraint defining the primary key. This is assuming you don't have the primary key listed as a foreign key somewhere else.
5
I just want to point out why you never ever want to use the GUI to insert a column inthe middle of an existing table. When you do that it, creates a speatare new table, moves the data from the old table, renames the old table, renames the new table to the old tablename and drops the old table. This is bad enough ifyou havea small databaset. In a production world where tables can be quite large, you could be locking the users out of access to the table for several hours. Any database design where the order of columns in the database needs to be rearranged when a new column is added is a database headed for disaster. Becasue there are people who insist on doing this though, it is another reason why Select * is also a problem waiting to happen. You really don't want the state to show up in your zip column in a report because someone rearranged the columns in the table and you relied on select * from the column order.
4
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
