是否可以避免循环？ [英] Is it possible to avoid a loop?

问题描述

这是我的情况。我们正在将客户的论坛从其专有论坛迁移到InstantForum。我需要提出一个脚本来简化过渡。我们最关心的是消息，他们不关心用户帐户。


我有一张消息表（msgtable），以及一个将帖子插入新论坛的程序。我的想法是创建一个交叉引用表（tbl_IF_XREF）来存储旧论坛中帖子的ID，除了它是新论坛的ID。这样做的原因是我需要保持帖子的连续性，即表中的每个条目都可能有一个元素，它是同一个表中另一行的ID。


这是我到目前为止所拥有的：


1.创建交叉引用表

2.在InstantForum中创建一个新论坛; Archived Posts"

3.然后，使用INSERT / SELECT我填充我的交叉引用表，其中所有行都没有引用任何其他行。


那是关于它的......有什么想法吗？我会感激任何帮助。我习惯用VB编程，我不认为上帝使我的大脑能够用SQL编程：（

解决方案

这是我的情况。我们正在将客户的论坛从他们的专有论坛迁移到InstantForum。我需要提供一个脚本来简化过渡。我们最关心的是消息，他们不关心用户帐户。


我有一张信息表（msgtable），以及一个将帖子插入新论坛的程序。这是我的想法创建一个交叉引用表（tbl_IF_XREF）来存储旧论坛中帖子的ID，除了它是新论坛的ID。原因是我需要保持帖子的连续性，即每个表中的条目可能有一个元素，它是同一个表中另一行的ID。


这是我到目前为止所拥有的：


1.创建交叉引用表

2. Creat用于存档帖子的InstantForum中的新论坛

3.然后，使用INSERT / SELECT我使用所有不引用任何其他行的交叉引用表填充。


那是关于它的......有什么想法吗？我会感激任何帮助。我习惯用VB编程，我不认为上帝使我的大脑能够用SQL编程:(

编辑：我想我需要循环，因为在每次调用我的Insert过程后，我需要将它的返回值（新表中新行的ID）插入到我的交叉引用表中。

做什么你想到在旧的MSGTABLE表上创建一个插入触发器，它将每个记录插入一个新表，同时从交叉引用表中替换一个ID，其余的列将全部来自触发器上下文中的INSERTED表吗？

您如何看待在旧的MSGTABLE表上创建插入触发器，该表将每个记录插入到新表中，同时从十字架中替换ID引用表和其余列将全部来自INSERTED表中的触发器上下文？

感谢您的回复！

我有没有做太多的SQL，所以我不得不查找触发器来找出你在说什么。我不确定这会有所帮助。

MSGTABLE已经包含了数据，并且不再被插入了。


这里''我想做的伪代码：对于MSGTABLE

中的每个ROW的

if（（ROW.ID不在XREF中）和（ROW.PARENT = 0或（XREF中的ROW.PARENT）））然后

INSERT到NEWTABLE

INSERT到XREF

end if

next

我只需要能够使用SQL来做到这一点（或以某种方式获得相同的结果）。

Here''s my situation. We''re migrating a client''s forum from their proprietary one to InstantForum. I need to come up with a script to ease the transition. Our biggest concern is the messages, they''re not concerned with user accounts.

I have a table of messges (msgtable), and a procedure to insert posts to the new forum. It''s my idea to create a cross-reference table (tbl_IF_XREF) to store the ID of a post in the old forum aside it''s ID for the new forum. The reason for this is that I need to maintain the continuity of the posts, ie each entry in the table may have an element in it that is the ID of another row in the same table.

Here''s what I have so far:

1. Create the cross-reference table
2. Create a new Forum in InstantForum for "Archived Posts"
3. Then, using a INSERT/SELECT I populate my cross-reference table with all the rows that do not reference any others.

And that''s about it... Any ideas? I''d appreciate any help. I''m used to programming in VB, I don''t think god made my brain able to program in SQL :(

解决方案

Here''s my situation. We''re migrating a client''s forum from their proprietary one to InstantForum. I need to come up with a script to ease the transition. Our biggest concern is the messages, they''re not concerned with user accounts.

I have a table of messges (msgtable), and a procedure to insert posts to the new forum. It''s my idea to create a cross-reference table (tbl_IF_XREF) to store the ID of a post in the old forum aside it''s ID for the new forum. The reason for this is that I need to maintain the continuity of the posts, ie each entry in the table may have an element in it that is the ID of another row in the same table.

Here''s what I have so far:

1. Create the cross-reference table
2. Create a new Forum in InstantForum for "Archived Posts"
3. Then, using a INSERT/SELECT I populate my cross-reference table with all the rows that do not reference any others.

And that''s about it... Any ideas? I''d appreciate any help. I''m used to programming in VB, I don''t think god made my brain able to program in SQL :(

Edit: I think I need the loop because after each call to my Insert procedure I need to insert the return value from it (the ID of the new row in the new table) into my cross-reference table.

What do you think of creating an insert trigger on the old MSGTABLE table which will insert every record into a new table while replacing an ID from a cross reference table and the rest of the columns will be all from INSERTED table in a trigger context?

What do you think of creating an insert trigger on the old MSGTABLE table which will insert every record into a new table while replacing an ID from a cross reference table and the rest of the columns will be all from INSERTED table in a trigger context?

Thanks for the reply!
I haven''t done much SQL, so I had to look up triggers to find out what you were talking about. I''m not sure that will help.
MSGTABLE already contains data, and won''t be getting inserted into anymore.

Here''s pseudo code for what I want to do:

for each ROW in MSGTABLE

if ((ROW.ID not in XREF) and (ROW.PARENT = 0 or (ROW.PARENT in XREF))) then

INSERT into NEWTABLE
INSERT into XREF

end if

next

I just need to be able to do that (or get the same results somehow) using SQL.

这篇关于是否可以避免循环？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！