How to make a query replace column if exist using sql-server query

Question

I have a query as the below one:

alter table [toolDB].[dbo].[esn_sho] add esn_umts_sho_relation_key as Convert(nvarchar(50),[utrancell])+'_'+Convert(nvarchar(50),[utranrelation])

So All I need I want to replace esn_umts_sho_relation_key column if exist...

As I got this error:

[Microsoft][SQL Server Native Client 11.0][SQL Server]Column names in each table must be unique. Column name 'esn_umts_sho_relation_key' in table 'toolDB.dbo.esn_sho' is specified more than once

I tired to use the below code but it's doen't work:

IF NOT EXISTS (alter table [toolDB].[dbo].[esn_sho] add esn_umts_sho_relation_key as Convert(nvarchar(50),[utrancell])+'_'+Convert(nvarchar(50),[utranrelation]))

It gives me this error:

Incorrect syntax near the keyword 'alter'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'

So I want to add If Exist in this query any one knows how to solve this prolem...

The column I already exist in the table ,but I want to replace it if exist to escape from this error...

Hi, do you have a alternative if it doesn't work (error is thrown)? You could try a try catch block in SQL : learn.microsoft.com/en-us/sql/t-sql/language-elements/… — Omkar
– Omkar, Commented Feb 24, 2020 at 8:38
Also, may I ask why you need to insert a column into a table if there is a chance that it exists? Are you migrating from one database to other? — Omkar
– Omkar, Commented Feb 24, 2020 at 8:40
@Omkar Look I'll dicuss you my whole case... I am just creating a database based on excel files... after that I make visualize on the database after created and create scripts using python, pandas(function), and some queries, So every time I push those excels files to the sql I replace all the tables, and If I add add another columns So I'll replace the old columns with the new column with the new values, as this query is one of my queries — Mahmoud Al-Haroon
– Mahmoud Al-Haroon, Commented Feb 24, 2020 at 8:45
@Omkar as the excels file have a lot of data as there's a limitation in the excel file, If I visualize the excel file directly I'lll get errors, So I have to push all data I have to the database and start visualize from the database instead of the excel file.. So every time I import all data to excel file I want to replace it with the old data.. that's all... this is why I need to replace the table with the new table use to the new values in the tab;e but It's should have the same columns every time using this process — Mahmoud Al-Haroon
– Mahmoud Al-Haroon, Commented Feb 24, 2020 at 8:51
If you are simply overwriting all data, just drop the tables. You will require more time to check whether the data exists, if not then insert, vs just recreating the table again. — Omkar
– Omkar, Commented Feb 24, 2020 at 8:53

Akhileshkumar singh · Accepted Answer · 2020-02-24 08:41:59Z

2

IF COL_LENGTH('TableName', 'ColumnName') IS NOT NULL
BEGIN
    -- Column Exists
    -- here you can put your cond...
END


IF COL_LENGTH('[toolDB].[dbo].[esn_sho]', 'esn_umts_sho_relation_key') IS NOT NULL
BEGIN
    -- Column Exists
    alter table [toolDB].[dbo].[esn_sho] add esn_umts_sho_relation_key as Convert(nvarchar(50),[utrancell])+'_'+Convert(nvarchar(50),[utranrelation])---Put your condition in proper way...
END

answered Feb 24, 2020 at 8:41

Akhileshkumar singh

1,5322 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Praveen · Accepted Answer · 2020-02-24 08:43:23Z

1

IF EXISTS(SELECT * FROM DatabaseName  where ColumnName = @YourParameter)
BEGIN
You Can Write Alter Query Here
END
ELSE
BEGIN
You Can Set Here AN Else Condition/Optional
END

answered Feb 24, 2020 at 8:43

Praveen

261 bronze badge

Comments

Omkar · Accepted Answer · 2020-02-24 09:00:33Z

1

To solve this particular issue, you can use the system tables. Try this

SELECT * 
    FROM sys.all_objects obj
    JOIN sys.all_columns col ON obj.object_id = col.object_id
WHERE 
    obj.Name = @YourTableName
    AND col.Name = @YourColumnName

This will give you the columns in your table if it exists. You can use this to make your decision about what you would do if it exists/doesn't exist.

answered Feb 24, 2020 at 9:00

Omkar

3923 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to make a query replace column if exist using sql-server query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related