alter-table

Why would 'alter table switch partition' fail silently?

I have a partitioned fact table on a SQL Server 2005 (Ent, Ed., 32 bit, SP2) that I am constructing a partition for (the fact table is a snapshot type). The process builds the data in a separate table, applies appropriate indexes and then switches the partition into the table. This has worked in the past The table structures are ident...

Best way to add a new column with an initial (but not default) value?

I need to add a new column to a MS SQL 2005 database with an initial value. However, I do NOT want to automatically create a default constraint on this column. At the point in time that I add the column the default/initial value is correct, but this can change over time. So, future access to the table MUST specify a value instead of a...

Adding an identity to an existing column [SQL Server]

I need to change the primary key of a table to an identity column, and there's already a number of rows in table. I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database. What's the SQL command to alter the column to have an identity property? ...

What's wrong with adding LOBs to an Oracle table?

I'm trying to ALTER a table by adding a new CLOB column (on Oracle 10), but it's failing. Giving me an ORA-01735 error. Problem is I can't find out what in particular is wrong with my query by googling around so I figured I'd ask here just in case. Anyways my query is: ALTER TABLE "MYSCHEMA"."MYTABLE" ADD "ACOLUMN" CLOB(2048); And g...

How do I add a column in a sqlite 2 database table

How do I add an additional column to an existing sqlite 2 database table that have data and indexes associated with it. It seems like the alter table SQL for that is not available in sqlite2? ...

SQL Alter Table then Modify Values

I have a SQL script that I am working on and I run into an issue when I'm creating (or editing) a column and then attempting to modify that new column. For example: BEGIN ALTER TABLE SampleTable ADD ColumnThree int END IF (EXISTS (SELECT * FROM sys.columns WHERE name = 'ColumnThree')) BEGIN UPDATE SampleTable SET ColumnThree ...

Is it possible to use Linq to ALTER a database table?

I am trying to programmatically drop a column in a table inside of an access database and found myself unable to do so! Is it at all possible? it makes me think that i don't have any clear idea of things linq to sql can't do. Any ideas? ...

Change huge table PK column data type

Now that we've ran out of int capacity on a PK column (which is an IDENTITY) I'd like to do this to bigint, but simple ALTER TABLE seems to be unable to handle that big of a table. So my question is: how do I change the type of a PK column with keeping actual values in place and do I need to alter referencing tables as well? ...

SQL Server script: ALTER PROCEDURE - Executing multiple ALTER PROCEDURE into one script without having to select each of the ALTER one after another

I know this is not a big issue, but it tickles me anyway. I have a SQL Server 2005 script to create new data tables, constraints, altering some tables to add columns, altering procedures to take the table changes into account, etc. Everything runs fine until the script encounters my ALTER PROCEDURE statements. The error message is as f...

How do I add more members to my ENUM-type column in MySQL?

The MySQL reference manual does not provide a clearcut example on how to do this. I have an ENUM-type column of country names that I need to add more countries to. What is the correct MySQL syntax to achieve this? Here's my attempt: ALTER TABLE carmake CHANGE country country ENUM('Sweden','Malaysia'); The error I get is: ERROR 1265 ...

RENAME faster than DROP+ADD in MySQL alter table

I'm performing some MySQL table maintenance that will mean removing some redundant columns and adding some new ones. Some of the columns to drop are of the same type as ones to add. Would the procedure be faster if I took advantage of this and reused some of the existing columns? My rationale is that changing column names should be a s...

How to change MySQL column definition?

create table test( locationExpect varchar(120) NOT NULL; ); I want to change it to : create table test( locationExpect varchar(120); ); ...

How to Add a Column using SMO with UserefinedDatatype when that talble already has some data????

I'm making a Utility that compares 2 Databases and makes one Equal to the other by changing and adding objects (Tables, Views, UserDefinedDatatypes, Defaults, Functions etc etc etc) I'm trying to add a Column, not null with a userdefineddatatype wich has a Default defined, the problem is this: I'm not able to use BindDefault before the...

Trying to add a column to a temp table fails

I have looked through this a number of times this morning and cannot find the issue. Its probably dead simple and I'll feel like an idiot when someone points it out. What would this SQL fail? -- Get CurrentRowCount DECLARE @MaxID INT SELECT @MaxID = ISNULL(MAX(WorkTypeID),0) FROM caWorkType ALTER TABLE #WorkType ADD _RowID INT NOT NUL...

PostgreSQL v7.4 ALTER TABLE to change column

I have a need to change the length of CHAR columns in tables in a PostgreSQL v7.4 database. This version did not support the ability to directly change the column type or size using the ALTER TABLE statement. So, directly altering a column from a CHAR(10) to CHAR(20) for instance isn't possible (yeah, I know, "use varchars", but that's n...

What's the deal with Rails' alter_table method?

I've been using sqlite3 for my database under development and my app has gotten complex enough that it's a bit slow to work with. I've just switched to MySQL and run rake db:create ; rake db:migrate and one of my migrations failed with the following error message: undefined method `alter_table` for #<ActiveRecord::ConnectionAdapters::M...

Mysql 5.1.42 alter table auto_increment = 0 doesn't work, truncate does

For my automated acceptance tests, I want inserts to start with id=1. I achieved this on one PC (XP 32bit, mysql 5.1.something) with (after deleting all rows from the table), "alter table tableName auto_increment = 0". I'm now setting up a new PC (Windows 7 64bit, mysql 5.1.42), and this command seems to have no effect. I can see in the...

How can i convert a price field which is currently varchar to a decimal so my prices will order correctly?

I am using MYSQL. I have a varchar field which i incorrectly used for a price. Now the ordering of this table will not work correctly putting anything over 1000 to the bottom of the list. I need to convert this price field in an existing POPULATED database from varchar to decimal i guess? Any help would be appreciated. ...

Modify a Column's Type in sqlite3

Hey guys, I'm pretty new to sqlite 3 and just now I had to add a column to an existing table I had. I went about doing that by doing: ALTER TABLE thetable ADD COLUMN category;. Of course, I forgot to specify that column's type. The first thing I was thinking about doing was dropping that column and then re-adding it. However, it seems t...

SQL Server - alter column - adding default constraint

I have a table and one of the columns is "Date" of type datetime. We decided to add a default constraint to that column Alter table TableName alter column dbo.TableName.Date default getutcdate() but this gives me Incorrect syntax near '.'. Does anyone see anything obviously wrong here, which I am missing (other than having a better...