Add columns from one to another table dynamically in SQL Server

Question

I have 2 tables, A and B. Table A has columns Q1, Q2, Q3, Q4 and Table B has columns Q1 and Q2 along with others. In Table A, columns will get changed in future with Q5, Q6, Q7 etc. I want to write dynamic SQL to alter the table B (adding new columns) whenever new columns are added to Table A. Columns which will get added always be like Q1, Q2,Q3...Q40.

example of my tables:

create table tableA 
(
    [sid] [varchar](50) not null, 
    [rid] [varchar](50) not null,
    [Q_URL] [varchar](500) null,
    [Q1] int null,
    [Q2] int null,
    [Q3] int null,
    [Q4] int null
);

create table tableB 
(
    [s_id] [varchar](50) not null,
    [rid] [varchar](50) not null,
    [Q_URL] [varchar](500) null,
    [Q1] int null,
    [Q2] int null
);

I think this is not an accurate approach for SQL based DB. In fact, if you need to add columns to a table, seems that you're missing a bit on the DB table design. Unless you're planning to consider XML fields or JSON fields. You can also consider a more dynamic DB approach, NON SQL DB's, like MongoDb. That's json based. — David Espino
– David Espino, Commented May 10, 2018 at 22:28
You can use the ordinal position and column name columns from information_schema.columns for this to build out your dynamic SQL but it will be terribly brittle and you will probably have to trigger it with DDL triggers which will get messy. It sounds like you want a database versioning solution rather than just some dynamic SQL. — S.C.
– S.C., Commented May 10, 2018 at 22:29
How about designing your table so that you have sid varchar(50), rid varchar(50), Q_URL varchar(500), Q_NUMBER int, value int. This way you don't need to keep adding columns, instead you have one row per sid, rid, Q_NUMBER combination. — anu start
– anu start, Commented May 10, 2018 at 23:03
Stop right now. This is a bad design. If you have the opportunity you need to change this. If you are making dynamic changes to fields then you need to stop doing this, and instead move this data into rows. — Nick.Mc
– Nick.Mc, Commented May 11, 2018 at 1:02

Jay Dhanki · Accepted Answer · 2018-07-13 14:44:28Z

1

    if object_id('tempdb..#source_col') is not null
            drop table #source_col
        Select distinct column_name as source_r_column from hr.INFORMATION_SCHEMA.COLUMNS
        where table_name = 'tableA'
        and column_name like 'Q%'
            and column_name <> 'Q_URL';
    if object_id('tempdb..#destination_col') is not null
                drop table #source_col
            Select distinct column_name as source_r_column from hr.INFORMATION_SCHEMA.COLUMNS
            where table_name = 'tableB'
            and column_name like 'Q%'
                and column_name <> 'Q_URL';

if object_id('tempdb..#newcols') is not null
    drop table #newcols
Select row_number() over(order by t1.source_q_columns) as colnum,t1.source_q_columns, 'int' as datatype
into #newcols
from #tableA t1
left join #tableB t2 on t1.source_q_columns= t2.destination_q_columns
where t2.destination_q_columns is null;

Declare @sql nvarchar(max)
Declare @column varchar(10)
Declare @datatype varchar(10)
Declare @max_colnum int
set @max_colnum = (select max(colnum)  from #newcols)
While(@max_colnum != 0)
begin
set @column = (select source_q_columns from #newcols where colnum = @max_colnum) 
print @column
select @sql = 'Alter Table destination Add '+ @column + ' int null' 
print @sql
EXEC (@sql)
set @max_colnum = @max_colnum -1
end

This is what i came up. I believe this will work for any case. :)

edited Jul 13, 2018 at 14:44

answered May 11, 2018 at 14:23

Jay Dhanki

234 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nick.Mc Over a year ago

Compare this to simply inserting a row. This is a bad design and you need to change it.

Jay Dhanki Over a year ago

why it's a bad design ? What's the issue in the code ? Thanks.. !

Nick.Mc Over a year ago

The bad design is adding columns to your table when you should be adding rows. Adding a row is a one-liner and downstream apps don't need to know about new columns. Dynamically adding columns is fundamentally a bad idea.

M.Ali · Accepted Answer · 2018-05-10 23:42:11Z

0

You can create something called a DDL Trigger for Alter Table event. The Trigger is created on the database level so it will fire for any Alter table commands executed on the database.

A sample of DDL trigger for Alter table event on the database level for TableA would look something like this.

CREATE TRIGGER tr_Replicate_DDL_For_TableA
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
    SET NOCOUNT ON;

Declare @data       xml = EVENTDATA()
       ,@TableName  SYSNAME
       ,@Sql        NVARCHAR(MAX);

SET @TableName  = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME');
SET @Sql        = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)');

  IF (@TableName = 'TableA')  --<-- only replicate changes if TableA's schema changed.
  BEGIN
      SET @sql = REPLACE(@sql , 'TableA' , 'TableB');

      Exec sp_executesql @sql;
  END

END
GO

answered May 10, 2018 at 23:42

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

1 Comment

Jay Dhanki Over a year ago

Nice idea if Table A always modify not drop. But Table A is source table and Table B is destination table in D/W. Table A can be dropped and re-created. In this case, your code won't work since it's tracking changes only. This is the only reason i'm looking for dynamic SQL. Thanks for your time & efforts. Highly appreciate your idea..

Collectives™ on Stack Overflow

Add columns from one to another table dynamically in SQL Server

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related