locked
how to set a flag for existing records RRS feed

  • Question

  • ID,   Name,  Sal

    -------------------------

    1    aaa      1000

    2    bbb     2000

    3   ccc       3000

    4   ddd     4000

    -------------------------

    In this table i need to add one more column(FLAG)

    so, If i insert a new record then flag should be 1 and for existing 4 records i need to set flag =0.

    Thanks in advance.


    bala krishna
    Wednesday, August 17, 2011 6:44 AM

Answers

  • No need for extra transactions, multiple statements nor triggers!

     

    MERGE dbo.TargetTable AS tgt
    USING (VALUES (@ID, @Name, @Sal, 1)) AS src(ID, Name, Sal, Flag) ON src.ID = tgt.ID WHEN NOT MATCHED BY SOURCE THEN UPDATE SET tgt.Flag = 0 WHEN NOT MATCHED BY TARGET THEN INSERT (ID, Name, Sal, Flag) VALUES (src.ID, src.Name, src.Sal, src.Flag) WHEN MATCHED THEN UPDATE SET tgt.Name = src.Name, tgt.Sal = src.Sal, tgt.Flag = src.Flag;

    N 56°04'39.26"
    E 12°55'05.63"

    • Proposed as answer by Uwe RickenMVP Wednesday, August 17, 2011 8:03 AM
    • Edited by swePeso Wednesday, August 17, 2011 8:08 AM Used wrong alias for target table
    • Marked as answer by Kalman Toth Tuesday, August 23, 2011 9:30 AM
    Wednesday, August 17, 2011 7:47 AM
  • Hi Bala Krishna

    After adding the new Flag column, you update the existing records with Flag = 0

    then you can alter your table to add default constraint on your FLAG column please check below code

    ALTER TABLE YourTable
    
    ADD CONSTRAINT Flat_default
    
    DEFAULT 1 FOR FLAG ;
    
    GO
    
    
    
    

     


    Thanks & Regards Prasad DVR


    Hallo Prasad,

    that's not the correct way! If you read the post you'll see that he wants to have an update on existing columns if a new one will be entered.
    Furthermore the FLAG-Attribute need to be created - just a CONSTRAINT will fail because the attribute does not exist.

     

    USE tempdb
    GO
    
    DROP TABLE dbo.tbl_foo
    GO
    
    -- Basic Table
    CREATE TABLE dbo.tbl_foo
    (
    	Id		int				PRIMARY KEY,
    	Name	nvarchar(10),
    	Sal		int
    )
    GO
    
    -- Now we enter some test data
    INSERT INTO dbo.tbl_foo
    (Id, Name, Sal)
    VALUES
    (1, 'aaa', 1000),
    (2, 'bbb', 1000),
    (3, 'ccc', 2000),
    (4, 'ddd', 2999)
    GO
    
    -- What's in the table
    SELECT * FROM dbo.tbl_foo
    GO
    
    -- Now we add the new column (type is BIT)
    ALTER TABLE dbo.tbl_Foo
    ADD Flag bit DEFAULT (1)
    GO
    
    -- Now we update existing values with 0 and the last record with 1
    UPDATE	dbo.tbl_foo
    SET	Flag = 0
    
    UPDATE	dbo.tbl_foo
    SET	Flag = 1 WHERE Id = (SELECT MAX(Id) FROM dbo.tbl_foo)
    
    -- What does the table looks like
    SELECT * FROM dbo.tbl_foo
    GO
    
    -- Now we create the trigger
    CREATE TRIGGER dbo.trg_tbl_foo_Insert
    ON dbo.tbl_foo
    FOR INSERT
    AS
    	SET NOCOUNT ON
    	
    	UPDATE	f
    	SET		f.Flag = 0
    	FROM	dbo.tbl_foo f
    	WHERE	f.Id NOT IN (SELECT Id FROM inserted)
    	
    	SET NOCOUNT OFF
    GO
    
    -- Now we test the trigger
    INSERT INTO dbo.tbl_foo
    (Id, Name, Sal)
    VALUES
    (5, 'eee', 4000)
    
    SELECT * FROM dbo.tbl_Foo
    

     


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    • Proposed as answer by Sharath048 Wednesday, August 17, 2011 1:03 PM
    • Marked as answer by Kalman Toth Tuesday, August 23, 2011 9:30 AM
    Wednesday, August 17, 2011 7:27 AM

All replies

  • ID,   Name,  Sal

    -------------------------

    1    aaa      1000

    2    bbb     2000

    3   ccc       3000

    4   ddd     4000

    -------------------------

    In this table i need to add one more column(FLAG)

    so, If i insert a new record then flag should be 1 and for existing 4 records i need to set flag =0.

    Thanks in advance.


    bala krishna
    Wednesday, August 17, 2011 6:48 AM
  • Hi Bala Krishna

    After adding the new Flag column, you update the existing records with Flag = 0

    then you can alter your table to add default constraint on your FLAG column please check below code

    ALTER TABLE YourTable
    
    ADD CONSTRAINT Flat_default
    
    DEFAULT 1 FOR FLAG ;
    
    GO
    
    
    
    

     


    Thanks & Regards Prasad DVR
    Wednesday, August 17, 2011 6:51 AM
  • Hallo Bala,

    you have to use a trigger which can cover this...

    USE tempdb
    GO
    
    
    -- Basic Table
    CREATE TABLE dbo.tbl_foo
    (
    	Id		int				PRIMARY KEY,
    	Name	nvarchar(10),
    	Sal		int
    )
    GO
    
    -- Now we enter some test data
    INSERT INTO dbo.tbl_foo
    (Id, Name, Sal)
    VALUES
    (1, 'aaa', 1000),
    (2, 'bbb', 1000),
    (3, 'ccc', 2000),
    (4, 'ddd', 2999)
    GO
    
    -- What's in the table
    SELECT * FROM dbo.tbl_foo
    GO
    
    -- Now we add the new column (type is BIT)
    ALTER TABLE dbo.tbl_Foo
    ADD Flag bit DEFAULT (1)
    GO
    
    -- Now we update existing values with 0 and the last record with 1
    UPDATE	dbo.tbl_foo
    SET	Flag = 0
    
    UPDATE	dbo.tbl_foo
    SET	Flag = 1 WHERE Id = (SELECT MAX(Id) FROM dbo.tbl_foo)
    
    -- What does the table looks like
    SELECT * FROM dbo.tbl_foo
    GO
    
    -- Now we create the trigger
    CREATE TRIGGER dbo.trg_tbl_foo_Insert
    ON dbo.tbl_foo
    FOR INSERT
    AS
    	SET NOCOUNT ON
    	
    	UPDATE	f
    	SET		f.Flag = 0
    	FROM	dbo.tbl_foo f
    	WHERE	f.Id NOT IN (SELECT Id FROM inserted)
    	
    	SET NOCOUNT OFF
    GO
    
    -- Now we test the trigger
    INSERT INTO dbo.tbl_foo
    (Id, Name, Sal)
    VALUES
    (5, 'eee', 4000)
    
    SELECT * FROM dbo.tbl_Foo
    


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Wednesday, August 17, 2011 7:22 AM
  • Hi Bala Krishna

    After adding the new Flag column, you update the existing records with Flag = 0

    then you can alter your table to add default constraint on your FLAG column please check below code

    ALTER TABLE YourTable
    
    ADD CONSTRAINT Flat_default
    
    DEFAULT 1 FOR FLAG ;
    
    GO
    
    
    
    

     


    Thanks & Regards Prasad DVR


    Hallo Prasad,

    that's not the correct way! If you read the post you'll see that he wants to have an update on existing columns if a new one will be entered.
    Furthermore the FLAG-Attribute need to be created - just a CONSTRAINT will fail because the attribute does not exist.

     

    USE tempdb
    GO
    
    DROP TABLE dbo.tbl_foo
    GO
    
    -- Basic Table
    CREATE TABLE dbo.tbl_foo
    (
    	Id		int				PRIMARY KEY,
    	Name	nvarchar(10),
    	Sal		int
    )
    GO
    
    -- Now we enter some test data
    INSERT INTO dbo.tbl_foo
    (Id, Name, Sal)
    VALUES
    (1, 'aaa', 1000),
    (2, 'bbb', 1000),
    (3, 'ccc', 2000),
    (4, 'ddd', 2999)
    GO
    
    -- What's in the table
    SELECT * FROM dbo.tbl_foo
    GO
    
    -- Now we add the new column (type is BIT)
    ALTER TABLE dbo.tbl_Foo
    ADD Flag bit DEFAULT (1)
    GO
    
    -- Now we update existing values with 0 and the last record with 1
    UPDATE	dbo.tbl_foo
    SET	Flag = 0
    
    UPDATE	dbo.tbl_foo
    SET	Flag = 1 WHERE Id = (SELECT MAX(Id) FROM dbo.tbl_foo)
    
    -- What does the table looks like
    SELECT * FROM dbo.tbl_foo
    GO
    
    -- Now we create the trigger
    CREATE TRIGGER dbo.trg_tbl_foo_Insert
    ON dbo.tbl_foo
    FOR INSERT
    AS
    	SET NOCOUNT ON
    	
    	UPDATE	f
    	SET		f.Flag = 0
    	FROM	dbo.tbl_foo f
    	WHERE	f.Id NOT IN (SELECT Id FROM inserted)
    	
    	SET NOCOUNT OFF
    GO
    
    -- Now we test the trigger
    INSERT INTO dbo.tbl_foo
    (Id, Name, Sal)
    VALUES
    (5, 'eee', 4000)
    
    SELECT * FROM dbo.tbl_Foo
    

     


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    • Proposed as answer by Sharath048 Wednesday, August 17, 2011 1:03 PM
    • Marked as answer by Kalman Toth Tuesday, August 23, 2011 9:30 AM
    Wednesday, August 17, 2011 7:27 AM
  • Use of Triggers are not advisable for data integrity until and unless you are bound to use them.

     

    Instead you can have your INSERT and triggering statements (INSERT/UPDATE/DELETE) bounded in TRANSACTIONS in Stored Procedures. Use of SPs will provide you more organized, performant and scalable code for your apps.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Wednesday, August 17, 2011 7:35 AM
  • Please do not post duplicate questions, check my comments on you similar post.
    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Wednesday, August 17, 2011 7:37 AM
  • Use of Triggers are not advisable for data integrity until and unless you are bound to use them.


    That's nonsens. A trigger is always part of the whole transaction.

    If the transaction rolls back the trigger changed will do so, too!

    Can you get me a link which is underlying your advice?
    See my changes to the abvove code example and you'll see what I mean!

    -- Now we test the trigger
    BEGIN TRANSACTION
    	INSERT INTO dbo.tbl_foo
    	(Id, Name, Sal)
    	VALUES
    	(5, 'eee', 4000)
    
    	SELECT * FROM dbo.tbl_Foo
    ROLLBACK TRANSACTION
    
    SELECT * FROM dbo.tbl_Foo


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Wednesday, August 17, 2011 7:42 AM
  • @Uwe, You are absolutely right and I don't deter with your first 2 lines.

     

    I was talking about general/best practice to use triggers or not. But yes, there are lot of conditions where trigger plays a crucial role.

     

    I would always go for creating a SP and put my whole business logic there, instead of creating an Insert statement calling from the GUI and ending up with n-number of triggers to handle my business logic.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Wednesday, August 17, 2011 7:58 AM
  • No need for extra transactions, multiple statements nor triggers!

     

    MERGE dbo.TargetTable AS src
    USING (VALUES (@ID, @Name, @Sal, 1) AS src(ID, Name, Sal, Flag) ON src.ID = tgt.ID
    WHEN NOT MATCHED BY SOURCE THEN UPDATE SET tgt.Flag = 0
    WHEN NOT MATCHED BY TARGET THEN INSERT (ID, Name, Sal, Flag) VALUES (src.ID, src.Name, src.Sal, src.Flag)
    WHEN MATCHED THEN UPDATE SET tgt.Name = src.Name, tgt.Sal = src.Sal, tgt.Flag = src.Flag;
    

    N 56°04'39.26"
    E 12°55'05.63"


    Hallo SwePoso,

    that's one of the reasons why I love this forums. That's a pretty fine solution.
    Only one - from my point - important hint:

    This construct is always available since SQL Server 2008


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Wednesday, August 17, 2011 8:01 AM
  • You can use the triggers as mentioned by Uwe Ricken.. 

    Or try the below solution.

    Add your new column to the table, by using the below alter statement

    alter table tbl
    add FLAG bit
    


    Before inserting an new rows.... Run the below update statement.

    UPDATE tbl
    Set FALG = 0
    
    


    Once this statement is succesful, then run the below statement.

    ALTER TABLE tbl
    ADD CONSTRAINT def_flag
    DEFAULT 0 FOR flag ;
    



    Nothing is Permanent... even Knowledge....
    My Blog
    Wednesday, August 17, 2011 8:43 AM