如何检查列(我确定不存在)是否存在?并添加该列 [英] How to check if column(that I'm sure that doesn't exist) exist? and add that column

问题描述

例如，我将检查我是否有
表格:周
列:星期一，星期二，星期三，星期四

但我想检查星期五是否存在

for example i will check if i have a
Table: Week
Columns: Monday, Tuesday, Wednesday ,Thursday

but i want to check if friday exist

IF NOT EXISTS(SELECT Friday FROM INFORMATION_SCHEMA.COLUMNS)
 ALTER TABLE TimeTB ADD Friday nvarchar(50) DEFAULT 'Free' NOT NULL

错误:列名星期五"无效.

Error: Invalid column name ''Friday''.

推荐答案

ADD列，然后是列名:)

ADD Column then column name :)

ALTER TABLE Table_Name ADD COLUMN Col_Name nvarchar(50)

好的，奇怪的要求.但是，您可以在SQL中使用 TRY-CATCH .

试试类似的东西:

Ok, strange requirement. Yet, you can use TRY-CATCH in SQL for it.

Try something like:

BEGIN TRY
  SELECT
     MyNewColumnNameToAddIfNotExists
  FROM
     MyExistingTable
END TRY
BEGIN CATCH
  ALTER TABLE MyExistingTable ADD MyNewColumnNameToAddIfNotExists nvarchar(50) DEFAULT 'Free' NOT NULL      
END CATCH

附注:您可能需要确保该错误仅是由于缺少列造成的！

此外，如果需要，您可以尝试使用Try-Catch进行事务处理.在此处查找详细信息 [

P.S.: You might need to make sure that the error is because of missing column only!

Further, if needed you can try Transactions along with Try-Catch. Look for details here[^].

您的SELECT语句不正确.试试这个:

Your SELECT statement is incorrect. Try this:

IF NOT EXISTS
    (
        SELECT 0 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'TimeTB' AND COLUMN_NAME = 'Friday'
    )
BEGIN
    ALTER TABLE TimeTB ADD COLUMN [Friday] nvarchar(50) DEFAULT 'Free' NOT NULL
END

另外，我在星期五"两边加了方括号，以防万一这是保留字.似乎有人已经添加了类似的答案，但已将其删除.不知道为什么会这样.

Also, I put square brackets around "Friday" just in case it''s a reserved word. Looks like somebody already added a similar answer, but it was deleted. Not sure why that is.

这篇关于如何检查列(我确定不存在)是否存在?并添加该列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！