MonetDB - Insert data to one column

Question

I have a table with 5 columns:

CREATE TABLE "voc"."MyTable" (
        "c_1"  TINYINT,
        "c_2"  TINYINT,
        "c_3"  TINYINT,
        "c_4"  TINYINT,
        "c_5"  TINYINT
);

I want to calculate the values for each column in separate, and insert the values for this column only. e.g.

INSERT INTO "voc"."MyTable"(c_1)
SELECT ....

This Insert command inserts 5000 rows into MyTable, with the values for c_1 column and null for all the other columns. If I run now

INSERT INTO "voc"."MyTable"(c_2)
SELECT ....

Then I have another new 5000 rows. And in total - 10000 rows. I want the new values of c_2 to be set in the same rows as c_1. i.e. to have

c_1 | c_2 | c_3 | c_4 | c_5
1     1     null null  null
2     2     null null  null
1     2     null null  null

I thought it would be possible in monetdb because its a columnar db, but so far I didn't find how to do it.

score -1 · Answer 1 · answered Jan 28 '15 at 12:50

This should be done after inserting first column only. Here change the table (other_table) name when shifting to other column

declare @sql nvarchar(50)

declare @value1 nvarchar(50)
declare @value2 nvarchar(50)

declare   @tab1 table(a int identity(1,1), t_name nvarchar(max)) 
insert into @tab1 select col_1 from "voc"."MyTable"

declare   @tab2 table(a int identity(1,1), col_2 nvarchar(max)) 
insert into @tab2 select col_2 from other_table

declare @count int=1
declare @c int
select @c=count(table_name) from table




  while(@count<=@c)
 begin

select @value1 =t_name from @tab1 where a=@count
select @value2 =col_2 from @tab2 where a=@count

set @sql=(   'Update voc.MyTable
        set column='+@value2
            +' where col_1='+@value1    )
exec @sql
set @count=@count+1
end

The performance of your code will be very bad for us - and its critical for us. — yishaiz, Jan 28 '15 at 13:05

MonetDB - Insert data to one column

1 Answers1