How to insert a new field and Toggle its value

Question

I have a table name MyDBTbl.

   Name   DisplayOrder
---------------------------
   Home        1
   Products    2
   Contact     true
   Career      false

I want to insert a new property Qualification with default value True. And when user run the script again it will check the existence of property , if Not present insert it and if Present toggle its value to false and vise versa.

SQL Server has columns, not fields. Your post, also implies your table only has 2 columns, which represents multiple different properties; this is almost also a bad idea. Finally, what is your question? You forgot to ask one; you just stated what you want to do. What is it you need help with? — Thom A, Jun 18 '18 at 10:49
Possible duplicate of [Add a column to a table, if it does not already exist](https://stackoverflow.com/questions/8870802/add-a-column-to-a-table-if-it-does-not-already-exist) — Daniel Bürckner, Jun 18 '18 at 10:50
Oh.. my Mistake , i am new to SQL and don't know , so i want to provide customer a query, after running the Query, new property will insert in existing table and if he executes it again, it will change its value to false. Hope its clear now. — Abhinav Parashar, Jun 18 '18 at 11:03

Sreenu131 · Answer 1 · 2018-06-18T11:15:38.503

Create Table Initially in your database

CREATE TABLE Tempdata(Name VARCHAR(20), DisplayOrder VARCHAR(20))
INSERT INTO Tempdata
SELECT'Home'     ,'1'       UNION ALL
SELECT'Products' ,'2'       UNION ALL
SELECT'Contact'  ,'true'    UNION ALL
SELECT'Career'   ,'false'

Run this below sql script for your the requirement and written code just based on requirement, i think it will be helpful to you

 IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_NAME='Tempdata')
BEGIN
CREATE TABLE Tempdata(Name VARCHAR(20), DisplayOrder VARCHAR(20))
INSERT INTO Tempdata
SELECT'Home'     ,'1'       UNION ALL
SELECT'Products' ,'2'       UNION ALL
SELECT'Contact'  ,'true'    UNION ALL
SELECT'Career'   ,'false'
END
--DROP TABLE Tempdata
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_NAME='Tempdata')
BEGIN

        IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
                        WHERE TABLE_NAME='Tempdata' AND COLUMN_NAME='Qualification')
        BEGIN
        ALTER TABLE Tempdata ADD Qualification VARCHAR(10)  NULL
        ALTER TABLE Tempdata ADD CONSTRAINT Df_Qualification DEFAULT('True') FOR Qualification
        UPDATE Tempdata SET Qualification='True'

        END

ELSE 
    IF  EXISTS(SELECT 1 FROM Tempdata
                      WHERE   Qualification='True'
                        )
    BEGIN
    UPDATE Tempdata SET Qualification='False'

    END
    ELSE UPDATE Tempdata SET Qualification='True'

END

SELECT * FROM Tempdata

How to insert a new field and Toggle its value

1 Answers1