CODEWITHSUNDEEP
javasqlservletsdb2
Sharepoint Madness
Sharepoint Madness

Reputation: 57

Moving Data From one Table to Another Similar Table

I usually try to avoid using cursors and looping through a result set but how can I accomplish this in the following scenario without looping through SomeTable and inserting rows one by one to MyTable?

Simple schema is as follows:

MyTable
- Name  VARCHAR(20)
- Code1 CHAR(5)
- Code2 CHAR(5)

SomeTable
- Name      VARCHAR(20)
- SomeCode1 CHAR(10)
- SomeCode2 CHAR(10)

SQL Statement:

INSERT INTO MyTable (Name, Code1, Code2)
SELECT Name, First Five Chars of SomeCode1 Only if SomeCode2 is not null or empty or doesn't have a certain value, SomeCode2
FROM SomeTable

I'm doing this inside Java code but I'm not sure if it's possible to do this in one whole INSERT INTO SELECT statement. I'm almost close to writing a for loop with single INSERT statements in them. Should I create some sort of a function in the database or.. ? I'm using DB2.

Upvotes: 1

Views: 1708

Answers (3)

user645280
user645280

Reputation:

If you want null entries when not found:

insert into MyTable (Name, Code1, Code2)
select
  Name,
  if(not isnull(SomeCode2) and SomeCode2 != ''
     and SomeCode2 != 'foo', substring(SomeCode1,1,5), null),
  SomeCode2
  from SomeTable
;

-or-

If you don't want any row to exist when entries aren't found:

insert into MyTable (Name, Code1, Code2)
select
  Name,
  substring(SomeCode1, 1, 5),
  SomeCode2
  from SomeTable
  where not isnull(SomeCode2) and SomeCode2 != '' and SomeCode2 != 'foo'
;

Upvotes: 0

Sergey Godunov
Sergey Godunov

Reputation: 1

insert into MyTable (Name, Code1, Code2)
select Name, case when (SomeCode2 is not null and length(trim(SomeCode2)) <> 0) then substr(SomeCode1, 0, 5) end, SomeCode2 from SomeTable;

Upvotes: 0

Bill
Bill

Reputation: 4585

INSERT INTO MyTable (Name, Code1, Code2)
SELECT Name, 
  CASE When coalesce(SomeCode2, '')='' Then NULL
       When SomeCode2=@somevalue Then NULL
       Else substring(SomeCode1, 1, 5)
, SomeCode2
FROM SomeTable

Upvotes: 1

Related Questions