将数据从一个表移动到另一个类似表

发布于 2024-11-05 01:25:23 字数 654 浏览 0 评论 0原文

我通常会尝试避免使用游标和循环遍历结果集，但是在以下场景中如何实现这一点而不循环遍历 SomeTable 并将行逐一插入到 MyTable 中？

简单的架构如下：

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

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

SQL 语句：

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

我在 Java 代码中执行此操作，但我不确定是否可以在整个 INSERT INTO SELECT 语句中执行此操作。我几乎即将编写一个包含单个 INSERT 语句的 for 循环。我应该在数据库中创建某种函数还是......？我正在使用 DB2。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

裸钻 2024-11-12 01:25:23

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

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

回复收藏 0 原文

鼻尖触碰 2024-11-12 01:25:23

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;

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;

回复收藏 0 原文

贵在坚持 2024-11-12 01:25:23

如果您希望在未找到条目时出现空条目：

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
;

- 或 -

如果您不希望在未找到条目时存在任何行：

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

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'
;

回复收藏 0 原文

~没有更多了~