How to add dynamic column to an existing table
13,926
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
Author by
user2514925
Updated on June 07, 2022Comments
-
user2514925 almost 2 years
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
-
Ritesh Khatri about 10 yearslike my answer if you satisfied withmy answer
-
Ritesh Khatri about 10 yearsit's about create procedure than why use to trigger.
-
user2514925 about 10 yearsIts getting execute but the new column not getting added
-
Ritesh Khatri about 10 yearsyou use QUOTENAME() function that why it's create problm. remove QUOTENAME() than you get successfully result
-
user2514925 about 10 yearsi have declared another variable and added '_COMPLETEDDATE' string in it.its working fine now.Thank you..