dynamically add column names and add their values as a row sql server

Question

I have a table table1 with column name a, b, c, d, e, f. Now the task is to get the value of each column which will definitely be a single row value and insert that into other table2 - columns(x, y, z) . So my query would be like :

 insert into table2 (x, y, z)
select a, '', '' from table1
union all
select b, '', '' from table1
union all
select c, '', '' from table1
union all
select d, '', '' from table1
union all
select e, '', '' from table1
.
.
.
union all
select f, '', '' from table1

Now if a new column add in table1 then again I have to add a select statement in this. Just want to avoid this how can I write a dynamic query which automatically consider all the columns and make it shorter.

John Cappelletti · Accepted Answer · 2017-03-28 15:50:33Z

2

Seems like your looking for a Dynamic EAV Structure (Entity Attribute Value). Now the cool part is the @YourTable could be any query

Declare @YourTable table (ID int,Col1 varchar(25),Col2 varchar(25),Col3 varchar(25))
Insert Into @YourTable values
 (1,'a','z','k')
,(2,'g','b','p')
,(3,'k','d','a')


Select A.ID
      ,C.*
 From  @YourTable A
 Cross Apply (Select XMLData=cast((Select A.* for XML Raw) as xml)) B
 Cross Apply (
                Select Attribute = attr.value('local-name(.)','varchar(100)')
                      ,Value     = attr.value('.','varchar(max)')              -- change datatype if necessary
                 From  B.XMLData.nodes('/row') as A(r)
                 Cross Apply A.r.nodes('./@*') AS B(attr)
                 Where attr.value('local-name(.)','varchar(100)') not in ('ID','OtherFieldsToExclude')  -- Field Names case sensitive
             ) C

Returns

ID      Attribute   Value
1       Col1        a
1       Col2        z
1       Col3        k
2       Col1        g
2       Col2        b
2       Col3        p
3       Col1        k
3       Col2        d
3       Col3        a

edited Mar 28, 2017 at 15:50

answered Mar 28, 2017 at 15:44

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

2 Comments

Ajay Rawat Over a year ago

thanks this resolves my issue both column name and value are coming. But i am still scratching my head in understanding this method.

John Cappelletti Over a year ago

@AjayRawat Cross Apply is a very powerful option. The first will convert the row into XML. The second Cross Apply will take that XML and unpivot the results,

Gordon Linoff · Accepted Answer · 2017-03-28 15:44:18Z

1

A simpler way to do this uses cross apply:

insert into table2 (x, y, z)
    select v.x, '', ''
    from table1 t1 cross apply
         (values (t1.a), (t1.b), (t1.c), (t1.d), (t1.e), (t1.f)
         ) v(x);

If you want to insert new values when new columns are added to the table, then you would want a DDL and probably a DML trigger. DML triggers are the "standard" triggers.

You can read about DDL triggers in the documentation.

That said, I am highly suspicious of database systems that encourage new columns and new tables to be added. There is probably a better way to design the application, for instance, using an EAV data model that provides greater flexibility with attributes.

answered Mar 28, 2017 at 15:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Z. Tian · Accepted Answer · 2017-03-28 15:46:14Z

0

try this

insert into table2 
select Tmp.id, tb1.* from table1 tb1,
((SELECT B.id FROM (SELECT [value] = CONVERT(XML ,'<v>' + REPLACE('a,b,c,d,e,f' , ',' , '</v><v>')+ '</v>')) A     
OUTER APPLY 
(SELECT  id = N.v.value('.' , 'varchar(100)') FROM A.[value].nodes('/v') N ( v )) B)) Tmp

answered Mar 28, 2017 at 15:46

Z. Tian

364 bronze badges

Comments

Joel Priddy · Accepted Answer · 2017-03-28 15:54:28Z

0

This, if I am reading it correctly, looks like a perfect time to use PIVOT.

answered Mar 28, 2017 at 15:54

Joel Priddy

4413 silver badges15 bronze badges

Collectives™ on Stack Overflow

dynamically add column names and add their values as a row sql server

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related