SQL insert data dynamic column name from another table

Question

i'm trying to insert data from one table to another with dynamic column name from @array to @array2

error

The multi-part identifier "s.id" could not be bound.

SQL CODE:

DECLARE @Array TABLE
(
    id int not null,
    dt varchar(12) not null,
    ld varchar(16) not null,
    val varchar(12) not null,
    ty varchar(4) not null,
    PRIMARY KEY CLUSTERED (id,dt)
)
DECLARE @Array2 TABLE
(
    id int not null,
    dt varchar(12) not null,
    ld varchar(16) not null,
    min varchar(12) null,
    mout varchar(4) null,
    PRIMARY KEY CLUSTERED (id,dt)
)

INSERT INTO @Array VALUES
 ('1','2015-11-11','2015-11-11','20:08','min')
 ,('2','2015-11-11','2015-11-11','20:08','mout')
 ,('3','2015-11-11','2015-11-11','20:08','min')
 ,('4','2015-11-11','2015-11-11','20:08','min')

 Select * from @Array s
 WHERE NOT EXISTS (select s.id,s.dt,s.ld,s.ty from @Array2
    WHERE id != s.id AND dt != s.dt)
    INSERT INTO @Array2 (id,dt,ld,s.ty) VALUES(s.id,s.dt,s.ld,s.val)
                                   ^
                dynamic column name from @Array TABLE

here is SQL Fiddle link, thanks.

LordBaconPants · Accepted Answer · 2015-11-25T19:58:33.807

0

I would re-write your insert along the lines of:

INSERT INTO @Array2 (id,dt,ld,s.ty)
Select s.id,s.dt,s.ld,s.ty from @Array s
left join @Array2 a2 on a2.id = s.id
where a2.id is null

Your error is coming from the fact that Array2 doesn't have a ty column defined. The fix there is to either put it in there or re-evaluate what you are putting into it. Also, thumbs up for the fiddle link :)

EDIT:

On second reading of your question, do you want to dynamically add that column to array2? If so, then that would require quite a bit of mucking around, and I would try to find another solution. Changing your schema like that on the fly is ill-advised.

EDIT2:

INSERT INTO @Array2 (id,dt,ld,min,mout)
Select 
   s.id,
   s.dt,
   s.ld,
   case s.ty when 'min' then s.val else '' end,
   case s.ty when 'mout' then s.val else '' end
from @Array s
left join @Array2 a2 on a2.id = s.id
where a2.id is null

EDIT3

UPDATE a2
SET 
   a2.dt = s.dt,
   a2.ld = s.ld,
   a2.min = case s.ty when 'min' then s.val else '' end,
   a2.mout = case s.ty when 'mout' then s.val else '' END
FROM @Array2 a2
   LEFT JOIN @Array s ON a2.id = s.id
WHERE s.id IS NOT null

edited Nov 25 '15 at 19:58

answered Nov 23 '15 at 00:20

LordBaconPants

1,404
1
19
22

i have 2 column that i would update or insert if not exist, for example each loop will select random column **min** or **mout**, so i'm trying to use **s.ty** to specify which column to check and update if null. i tryed to look into merge but have issue selecting dynamic column example can be found here http://stackoverflow.com/questions/14806768/sql-merge-statement-to-update-data – Jah Nov 23 '15 at 11:01
@Jah I've added in an edit to reflect what I think you are trying to do there; the updated statement would follow a similar pattern. I find merge much slower and more prone to data issues, but that is my personal bias. You can use a similar theory there – LordBaconPants Nov 23 '15 at 20:31
you are a genius, any change i can add update option if exists? – Jah Nov 25 '15 at 01:03
@Jah Edit3 will do the job. If you do go with this method I'd suggest running the update first, then the insert. That way you aren't updating rows you've just inserted. – LordBaconPants Nov 25 '15 at 20:00

SQL insert data dynamic column name from another table

1 Answers1