Insert and Update an array of dynamic length into a table

Question

My table is

(id int,property varchar)

Suppose I want to insert {3, 'a, b, c'}. How can I do it in a stored procedure?
The table entry would be:

id property  
3   a  
3   b  
3   c

Also when i want to update my id=3 row by {3, 'ab, b, bg, ht'} . What would be the stored procedure for this insert and update operation? My table entries should become

id property  
3   ab  
3   b  
3   bg  
3   ht

the edition is not right. It changed the initial idea. Should be {3, 'a, b, c'} — Thiago C. S Ventura, Jan 07 '14 at 12:11
It means my id is 3 and property is {a,b,c}. three table entries for each property item. — zooney, Jan 07 '14 at 12:14
I tried solving the above problem by first deleting the matched rows and then inserting the new rows. I was looking for an efficient approach to solve it. And answer by t-clausen.dk by use of 'merging' is quite appropriate for my problem.Kindly re-open my problem as it enlightens the approach of merging in sql as well. — zooney, Jan 08 '14 at 08:58

t-clausen.dk · Accepted Answer · 2014-01-07T14:29:30.983

sample table:

create table yourtable (id int,property varchar(5))

Procedure for that table:

create procedure p_test
(
@id int, 
@prolist varchar(2000)
) as
begin
;with x as
(
  SELECT * FROM yourtable WHERE id = @ID
)
MERGE INTO 
x t1 
using 
(SELECT @id id, ltrim(t.c.value('.', 'VARCHAR(2000)')) property
FROM (
    SELECT x = CAST('<t>' + 
        REPLACE(@prolist, ',', '</t><t>') + '</t>' AS XML)
) a
CROSS APPLY x.nodes('/t') t(c))  t2 on t1.id = t2.id and t1.property = t2.property
when not matched then INSERT (id,property)   
VALUES(t2.id, t2.property) 
when matched 
THEN UPDATE SET t1.id = t2.id
WHEN NOT MATCHED BY SOURCE THEN DELETE
; 
end

Testing:

exec p_test 3, 'b,c'
select * from yourtable
exec p_test 3, 'a,b,c'
select * from yourtable
exec p_test 3, 'a,c'
select * from yourtable
exec p_test 4, 'g,h'
select * from yourtable

Result:

id  property
3   b
3   c

id  property
3   b
3   c
3   a

id  property
3   c
3   a

id  property
4   g
3   c
3   a
4   h

EDIT:

in order to update a new column use this table:

create table yourtable (id int,property varchar(5), is_active bit default 1)

Use this procedure:

alter procedure p_test
(
@id int, 
@prolist varchar(2000)
) as
begin
;with x as
(
  SELECT * FROM yourtable WHERE id = @ID
)
MERGE INTO 
x t1 
using 
(SELECT @id id, ltrim(t.c.value('.', 'VARCHAR(2000)')) property
FROM (
    SELECT x = CAST('<t>' + 
        REPLACE(@prolist, ',', '</t><t>') + '</t>' AS XML)
) a
CROSS APPLY x.nodes('/t') t(c))  t2 on t1.id = t2.id and t1.property = t2.property
when not matched then INSERT (id,property, is_active)   
VALUES(t2.id, t2.property, 1) 
when matched 
THEN UPDATE SET t1.id = t2.id, is_active = 1
WHEN NOT MATCHED BY SOURCE THEN 
UPDATE SET t1.is_active = 0
; 
end

thanks a lot. One small query , can u please give the idea behind the merging. Also if 'my_table' had a column is_active and instead of deleting the row when not matched by source ,I want to update is_active to 0. How to do it? — zooney, Jan 07 '14 at 13:38
@zooney that is a good question. I don't know right now. It may be necessary to make another expression inthe stored procedure — t-clausen.dk, Jan 07 '14 at 14:01

score 1 · Answer 2 · answered Jan 07 '14 at 12:03

1

First one:

insert into <tableName>(id, property) values(3, 'a');
insert into <tableName>(id, property) values(3, 'b');
insert into <tableName>(id, property) values(3, 'c');

Second issue:

update <tableName> set property='ab' where property = 'a';
update <tableName> set property='bg' where property = 'c';
insert into <tableName>(id, property) values(3, 'ht');

And now, a question: are you sure this is what your problem needs? Usually, when we call a column id we want it to be an identifier, that is, unique for each row. This may be a little bit off topic, but just in case...

answered Jan 07 '14 at 12:03

nestedloop

2,596
23
34

:well above scenario is just an example. the data would be dynamic. – zooney Jan 07 '14 at 12:07
@zooney I understand. Then try those queries, they should work. Substitute your table name for and give it a go. – nestedloop Jan 07 '14 at 12:09

score 0 · Answer 3 · answered Jan 07 '14 at 12:05

0

There should be an id/primary key which will be unique. Profide any unique field and update records on the base of that unique field. Or you can make property unique or pair of id and property unique.

answered Jan 07 '14 at 12:05

Mohsin Shoaib

158
10

This is the case normally. However it is perfectly possible to do what the OP wants, though admittedly quite awkward. – nestedloop Jan 07 '14 at 12:07

score 0 · Answer 4 · edited May 23 '17 at 12:28

0

If I understood well, you could do something similar to this:

  Insert INTO @tempTable
  SELECT 3, * FROM dbo.splitstring('a,b,c,d')

It's just a pseudocode and you should do it inside your stored procedure.

If this is the right approach you have to take a look at this: T-SQL split string

edited May 23 '17 at 12:28

Community

1
1

answered Jan 07 '14 at 12:25

Thiago C. S Ventura

2,448
1
29
43

Insert and Update an array of dynamic length into a table

4 Answers4