How to insert one row of a table into two rows of another table

Question

I have a record in a table Table1 and i want to split that one record into two rows and insert into another table Table2.

Table1

ID  Date      User       value
1   29/05/18   XXX   X_ID||X_value||22||xx
2   29/05/18   YYY   Y_ID||Y_value||33|yy

and I want table1 values to be inserted into table2 as

Table2

P_ID ID Date       User   Field    Value
 1   1  29/05/18    XXX    X_ID     22
 2   1  29/05/18    XXX    X_Value  XX
 3   2  29/05/18    YYY    Y_ID     33
 4   3  29/05/18    YYY    Y_Value  YY

Value from table1 is field in table2 and nothing could be hardcoded as I would be having many records in table table1. EDIT: and what if value in table1 has more values like X_ID||X_VALUE||Y_ID||Y_Value||22||xx||33||yy. How do I make this query dynamic so that irrespective of output it separates record in different rows of table2

Is it possible to have more or less than 2 (field,value) in a record of Table1 or is the format of the value column fixed? — Aleksej, May 29 '18 at 10:47
create a temp table with 2 "dummy" rows, join with temp table selecting the correct columns for each of those dummy rows. Or just select twice using a union — jean, May 29 '18 at 10:48
It's totally up to you, but why do you split it into two **rows**, and not two **colums**, like (ID, Date, User, X, Y)? — wolφi, May 29 '18 at 11:47
I'm asking, because your table 2 looks a bit like [EAV](https://stackoverflow.com/questions/27893539/sql-alternatives-to-a-generic-datamodel) — wolφi, May 29 '18 at 12:03

score 1 · Accepted Answer · answered May 29 '18 at 10:49

1

You can use regexp_substr():

select id, date, user, regexp_substr(val, '[^|]+', 1, 1) as field, regexp_substr(val, '[^|]+', 1, 3) as value
from table1
union all
select id, date, user, regexp_substr(val, '[^|]+', 1, 2) as field, regexp_substr(val, '[^|]+', 1, 4) as value
from table1;

You can just use insert to put this into another table. This assumes that p_id is assigned automatically. If you want to assign it yourself, you can use row_number():

select row_number() over (partition by id order by which) as p_id,
       id, date, user, field, value
from ((select id, date, user, regexp_substr(val, '[^|]+', 1, 1) as field, regexp_substr(val, '[^|]+', 1, 3) as value, 1 as which
       from table1
      ) union all
      (select id, date, user, regexp_substr(val, '[^|]+', 1, 2) as field, regexp_substr(val, '[^|]+', 1, 4) as value, 2 as which
       from table1
      )
     ) t

answered May 29 '18 at 10:49

Gordon Linoff

1,242,037
58
646
786

for p_id I am trying to create sequence but I am getting error as sequence is not allowed. @linoff gordon. And row_number() can slow down the process as I am going to process lots of rows. Do we have any other alternative to auto generate p_id. – SYMA May 30 '18 at 07:47
@SYMA . . . I'm not sure why a sequence would not be allowed. Perhaps you should ask another question. – Gordon Linoff May 30 '18 at 12:33
can you also help with, what if value in table1 has more values like X_ID||X_VALUE||Y_ID||Y_Value||22||xx||33||yy. How do I make this query dynamic so that irrespective of output it separates record in different rows of table2 – SYMA May 30 '18 at 12:39

score 0 · Answer 2 · answered May 30 '18 at 05:06

I have tried to solve the problem by using cursors.

DECLARE @ID INT

DECLARE @NAME VARCHAR(50)

DECLARE @DATE DATE

DECLARE @VALUE VARCHAR(500)

DECLARE @List TABLE (item NVARCHAR(MAX))

DECLARE @SELECTCURSOR CURSOR

SET @SELECTCURSOR = CURSOR FOR SELECT * FROM tempTable1

OPEN @SELECTCURSOR

FETCH NEXT FROM @SELECTCURSOR INTO @ID,@DATE,@NAME, @VALUE

WHILE @@FETCH_STATUS = 0

BEGIN

SET @VALUE = Replace(@VALUE, '||', '.');

INSERT INTO tempTable2(ID,[User],[Date],Field,Value) VALUES(@ID,@NAME,@DATE,ParseName(@VALUE,4),PARSENAME(@VALUE,2))

INSERT INTO tempTable2(ID,[User],[Date],Field,Value) VALUES(@ID,@NAME,@DATE,ParseName(@VALUE,3),PARSENAME(@VALUE,1))

FETCH NEXT FROM @SELECTCURSOR INTO @ID,@DATE,@NAME, @VALUE

END

CLOSE @SELECTCURSOR

DEALLOCATE @SELECTCURSOR

How to insert one row of a table into two rows of another table

2 Answers2