#alter-table

Fixed Adding an identity to an existing column #dev #it #asnwer

Adding an identity to an existing column

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?

Answer: Adding an identity to an existing column

You…

Fixed Rename column SQL Server 2008 #dev #it #asnwer

Rename column SQL Server 2008

I am using SQL Server 2008 and navicat. I need to rename a column in a table using SQL.

ALTER TABLE table_name RENAME COLUMN old_name to new_name;

This statement doesn’t work.

Answer: Rename column SQL Server 2008

It would be a good suggestion to use an already built-in function but another way around is to:

Create a new column with same data type and NEW NAME.

Ru…

Fixed check if temp table exist and delete if it exists before creating a temp table #dev #it #asnwer

check if temp table exist and delete if it exists before creating a temp table

I am using the following code to check if the temp table exists and drop the table if it exists before creating again. It works fine as long as I don’t change the columns. If I add a column later, it will give an error saying “invalid column”. Please let me know what I am doing wrong.

IF OBJECT_ID('tempdb..#Results')…

How to: SQL Server alter rename table

SQL Server alter rename table

I’m trying to rename a table in SQL Server. The SQL query that I have used is :-

ALTER TABLE oldtable RENAME TO newtable;

but it gives me an error.

Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword ‘TO’.

Thanks.

Answer: SQL Server alter rename table

To rename a table in SQL Server, use the sp_rename command:

sp_rename old_table_name,…

How to: How do you change the datatype of a column in MS SQL?

How do you change the datatype of a column in MS SQL?

I am trying to change a column from a varchar(50) to a nvarchar(200). What is the SQL command to alter this table?

Answer: How do you change the datatype of a column in MS SQL?

alter table TableName alter column ColumnName nvarchar(200) [null | not null]

EDIT As noted NULL/NOT NULL should have been specified, see Rob’s answer as well.

Answer:…

How to: Sql Server add auto increment primary key to existing table

Sql Server add auto increment primary key to existing table

As the title, I have an existing table which is already populated with 150000 records. I have added an Id column (which is currently null).

I’m assuming I can run a query to fill this column with incremental numbers, and then set as primary key and turn on auto increment. Is this the correct way to procede? And if so, how do I fill the…

How to: How do I rename a column in a SQLite database table?

How do I rename a column in a SQLite database table?

I would need to rename a few columns in some tables in a SQLite database. I know that a similar question has been asked on stackoverflow previously, but it was for SQL in general, and the case of SQLite was not mentioned.

From the SQLite documentation for ALTER TABLE, I gather that it’s not possible to do such a thing “easily” (i.e. a single…

Fixed Altering a column: null to not null #dev #it #asnwer

Altering a column: null to not null

I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to “NOT NULL.” Aside from changing nulls to 0, data must be preserved.

I am looking for the specific sql syntaxto alter a column (call it ColumnA) to “not null.” Assume the data has been updated…