Post reply

Add a Column to Table

  • April 1, 2010 at 1:08 am

    #225724

    I want to add a column to a table in My SQL CE database by using the following command:

    ALTER TABLE tblProducts ADD isDeleted bit

    However I want to prevent the alter table command to be executed in case the column already exists.

    I could not use IF Exist in SQL CE.

    Anyone has an idea or solution for this?

    Regards,

    Vimal Panchal

  • April 14, 2010 at 2:42 pm

    #1151740

    Try this, Do a select count(*) from the system tables looking for the column name in the table you want, if the count > 0 then the column exist

    DECLARE @Counter as INT

    SELECT @Counter = COUNT(*)

    FROM sysColumns

    WHERE Blahhhhhh

    IF @Counter > 0 THEN

    BEGIN

    Alter Table

    END

  • April 22, 2010 at 7:20 am

    #1156086

    vimal_panchal (4/1/2010)

    I want to add a column to a table in My SQL CE database by using the following command:

    ALTER TABLE tblProducts ADD isDeleted bit

    However I want to prevent the alter table command to be executed in case the column already exists.

    I could not use IF Exist in SQL CE.

    Anyone has an idea or solution for this?

    Regards,

    Vimal Panchal

    IF NOT EXISTS (SELECT *

    FROM information_schema.columns

    WHERE table_name = 'tblProducts'

    AND column_name = 'isDeleted')

    BEGIN

    ALTER TABLE tblproducts ADD isDelete bit

    END

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • April 22, 2010 at 7:32 am

    #1156096

    Thanks to both of you for your reply but i want to do this in SQL Compact Edition database..which is not supporting DECLARE keyword as well as i can not start my query with IF......

    Thanks

    Vimal

  • April 23, 2010 at 7:59 am

    #1156858

    Can you use TRY CATCH?

    BEGIN TRY

    ALTER TABLE tblproducts ADD isDelete bit

    END TRY

    BEGIN CATCH

    END CATCH

  • June 14, 2010 at 4:34 am

    #1179743

    Check this...

    http://msdn.microsoft.com/en-us/library/ms174123(SQL.90).aspx

    And you can use if condition in your application code. Generate data set from the query to check the column using Information_Schema and take decission at application level code.

    Stored Procedures are NOT supported in SQL Server CE.

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG     [font="Arial Black"]here[/font][/url][/right]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply