如何将动态列添加到现有表 [英] How to add dynamic column to an existing table

问题描述

我有 2 个表，第一个表包含以下列，

I have 2 tables 1st table contains following columns,

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

第二个表包含以下列，

id,Name,Chk1,chk2,Chk3

如果 table1 动态更新为值 '4,'c4','ch4'，我必须将列 'Chk4' 添加到 table2.如何编写程序来执行此操作?

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

推荐答案

将此查询用作过程.

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

这篇关于如何将动态列添加到现有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！