locked
I'm having trouble adding a column to my table. RRS feed

  • Question

  • I created a table with three columns in it: 

    UserId, password and username. However, I want to add in a fourth column for the GUID. 

    I'm incredibly new to SQL so I apologize if the problem turns out to be plain and clear. 

    IF NOT EXISTS (SELECT * 
                     FROM INFORMATION_SCHEMA.COLUMNS 
                     WHERE TABLE_SCHEMA = 'dbo' 
                     AND  TABLE_NAME = 'Users'
    				 AND  COLUMN_NAME = 'userGuid')
    	
    Begin
    
    CREATE TABLE [Users](
        [UserId] [int] IDENTITY(1,1) NOT NULL,
        [username] [varchar](50) NOT NULL UNIQUE,
        [password] [varchar](50) NOT NULL
    	/*userGuid uniqueidentifier NULL*/
    )
    
    
    ALTER TABLE Users  Add userGuid uniqueidentifier NULL
    
    END

    Someone suggested I try adding in a 'Commit Statement'. 

    I placed the commit statement in between the 'ALTER TABLE Users' statement and the 'END' Statement. 

    I execute both across my table, flip over to the DESIGN view, still nothing. I have my three columns but not the fourth. 

    This relates to an error I'm getting in VS: 'Invalid Column name 'userGuid'', but I won't get into that because I'm almost certain that the issue is that I don't actually have a userGuid column. 

    Saturday, March 14, 2015 3:29 PM

Answers

  • The code you posted will fail if the table exists but the column doesn't.  It will try to create the table again and fail.  If you want to create the table and/or column conditionally, try:

    IF NOT EXISTS (SELECT * 
                     FROM INFORMATION_SCHEMA.TABLES
                     WHERE TABLE_SCHEMA = 'dbo' 
                     AND  TABLE_NAME = 'Users')
    BEGIN
    
    	CREATE TABLE [Users](
    		[UserId] [int] IDENTITY(1,1) NOT NULL,
    		[username] [varchar](50) NOT NULL UNIQUE,
    		[password] [varchar](50) NOT NULL,
    		userGuid uniqueidentifier NULL
    	);
    
    END
    ELSE
    BEGIN
    
    	--table exists, add column if needed
    	IF NOT EXISTS (SELECT * 
    					 FROM INFORMATION_SCHEMA.COLUMNS 
    					 WHERE TABLE_SCHEMA = 'dbo' 
    					 AND  TABLE_NAME = 'Users'
    					 AND  COLUMN_NAME = 'userGuid')
    	BEGIN
    		ALTER TABLE dbo.Users ADD userGuid uniqueidentifier NULL;
    	END;
    
    END;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, March 14, 2015 3:47 PM
    Answerer

All replies

  • The code you posted will fail if the table exists but the column doesn't.  It will try to create the table again and fail.  If you want to create the table and/or column conditionally, try:

    IF NOT EXISTS (SELECT * 
                     FROM INFORMATION_SCHEMA.TABLES
                     WHERE TABLE_SCHEMA = 'dbo' 
                     AND  TABLE_NAME = 'Users')
    BEGIN
    
    	CREATE TABLE [Users](
    		[UserId] [int] IDENTITY(1,1) NOT NULL,
    		[username] [varchar](50) NOT NULL UNIQUE,
    		[password] [varchar](50) NOT NULL,
    		userGuid uniqueidentifier NULL
    	);
    
    END
    ELSE
    BEGIN
    
    	--table exists, add column if needed
    	IF NOT EXISTS (SELECT * 
    					 FROM INFORMATION_SCHEMA.COLUMNS 
    					 WHERE TABLE_SCHEMA = 'dbo' 
    					 AND  TABLE_NAME = 'Users'
    					 AND  COLUMN_NAME = 'userGuid')
    	BEGIN
    		ALTER TABLE dbo.Users ADD userGuid uniqueidentifier NULL;
    	END;
    
    END;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, March 14, 2015 3:47 PM
    Answerer
  • That worked, thank you!
    Saturday, March 14, 2015 4:10 PM