CODEWITHSUNDEEP
sqlsql-serverdynamic
Ajay Rawat
Ajay Rawat

Reputation: 53

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

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.

Upvotes: 1

Views: 2810

Answers (4)

Joel Priddy
Joel Priddy

Reputation: 441

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

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81960

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

Upvotes: 2

Z. Tian
Z. Tian

Reputation: 36

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 1

Related Questions