CODEWITHSUNDEEP
sqlsql-serverdatabase
Paul DUJARDIN
Paul DUJARDIN

Reputation: 3

SQL SERVER: I would like to transfer my data to another column of the same table before droping the first one

I am having problems with some of my SQL scripts on SQL SERVER, indeed I am trying to transfer data from a column A to a column B of the same table and then drop the column B,

However my problem is that I have to check for the existence of A beforehand because the code is meant to be executed on a server where I don't have access (I work as a third party developper on a professionnal app)

Here is my code:

-- Export the data from the column name
-- Drop the column name

USE p_mynacellelocation_db_ChecklistWPF
GO


IF COL_LENGTH('model_trolley_part','name') IS NOT NULL
BEGIN
UPDATE model_trolley_part
SET name_en=[name];

ALTER TABLE model_trolley_part
DROP COLUMN [name];
END

In the case of the column name being non existent I would like not to do anything

However on execution of the code in a database where the column name is non existent SQL Server returns me:

Msg 207, Level 16, State 1, Line 12 Invalid column name 'name'.

Upvotes: 0

Views: 64

Answers (3)

Sean Lange
Sean Lange

Reputation: 33571

Instead of jumping through all these hoops simply rename your original column.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sql-server-2017 

exec sp_rename 'model_trolley_part.name', 'name_en', 'COLUMN'

Upvotes: 3

sheeni
sheeni

Reputation: 407

Try this:

IF EXISTS(SELECT *
          FROM   INFORMATION_SCHEMA.COLUMNS
          WHERE  TABLE_NAME = 'myTableName'
                 AND COLUMN_NAME = 'ColumnA') 
BEGIN

// your update and drop code goes here

END

you might want to check your account privileges if you are modifying table structure etc..

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31775

You need to use dynamic SQL for this. The reason is that the parser will try to validate your code, even the code that won't be executed because its IF condition wouldn't be met.

The parser is smart enough to see there is no table named name, but it's not smart enough to realize that the code shouldn't get executed because of the IF, so it raises a parsing error.

Using dynamic SQL in the BEGIN..END block after the IF hides this from the parser so it will execute.

Upvotes: 0

Related Questions