CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2sql-server-2012
user2514925
user2514925

Reputation: 949

How to add dynamic column to an existing table

I have 2 tables 1st table contains following columns,

 id code    Name
 1  c1  chk1
 2  c2  chk2
 3  c3  chk3

2nd table contains following columns,

id,Name,Chk1,chk2,Chk3

i have to add the column 'Chk4' into table2 if table1 is updated with value '4,'c4','ch4' dynamically.How to write procedure to perform this?

i've tried the following procedure but its not working fine.

         create proc Add_Check
          as 
          begin
          declare @Column varchar(50)
          declare @query varchar(255)
          declare @query1 varchar(255)
          set @Column= (select top 1 QUOTENAME(Name)
            from table1 where id=(Select MAX id) from table1))
          if exists(select 1 from table1
         where Name=@Column) 
         begin
         set @query = 'alter table table2 add ' + @Column + ' Varchar (50)'
         set @query1 = 'alter table table2 add ' + @Column + '_CompletedDate Varchar (50)'
         exec(@query)
         end
         end

Upvotes: 3

Views: 20449

Answers (3)

knkarthick24
knkarthick24

Reputation: 3216

Use Triggers as follows:

CREATE TRIGGER AddDynamicColumn_to_table2  
ON Table1  
AFTER INSERT  
AS  
Exec ('ALTER TABLE Table2 ADD ' + (select name from inserted) + ' Varchar(10)')  
GO

Upvotes: -1

Raj
Raj

Reputation: 10853

Try this: 

CREATE PROCEDURE <procedurename>
AS 
BEGIN    
    DECLARE @COLUMN varchar(10), @SQL Varchar(100);
    SELECT @COLUMN = Name FROM Table1 
    WHERE id = (SELECT MAX(id) FROM Table1)
                IF NOT EXISTS(
                              SELECT COLUMN_NAME 
                              FROM INFORMATION_SCHEMA.COLUMNS
                              WHERE TABLE_NAME = 'Table2' 
                              AND COLUMN_NAME = @COLUMN
                             )
                 BEGIN
                   SELECT @SQL = 
                          'ALTER TABLE Table2 ADD '
                           +@COLUMN+' varchar(10),'
                           +@COLUMN+'_CompletedDate varchar(50)'
                   EXECUTE (@SQL)
                 END
END

Upvotes: 0

Ritesh Khatri
Ritesh Khatri

Reputation: 484

use this query as procedure.

CREATE PROC ADD_CHECK
AS 
BEGIN
    DECLARE @COLUMN VARCHAR(50)
    DECLARE @QUERY VARCHAR(255)
    DECLARE @QUERY1 VARCHAR(255)

    SET @COLUMN= (SELECT TOP 1 NAME FROM TABLE1 WHERE ID=(SELECT MAX (ID)     FROM TABLE1))

    IF EXISTS(SELECT 1 FROM TABLE1 WHERE NAME=@COLUMN) 
    BEGIN
        SET @QUERY = 'ALTER TABLE TABLE2 ADD ' + @COLUMN + ' VARCHAR (50)'
        SET @QUERY1 = 'ALTER TABLE TABLE2 ADD ' + @COLUMN + '_COMPLETEDDATE VARCHAR     (50)'
        EXEC(@QUERY)
    END
END

Upvotes: 2

Related Questions