Is it possible to avoid a loop?

[DavidOxclove]

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 :(

[DavidOxclove]

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.

[iburyak]

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?

[DavidOxclove]

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.

[iburyak]

Is this one time data insert?

[iburyak]

I wouldn't do a loop just because it is not efficient


1. Get all records to insert into temp table.

[PHP]SELECT * into #TempTable
FROM MSGTABLE
WHERE ID not in (SELECT ID FROM XREF) -- FIND ID that is not in XREF TABLE
and PARENT in (SELECT ID FROM XREF -- As I understand parent could be 0 or must exist in XREF table
UNION
SELECT 0)[/PHP]

2. Now you've got all records that about to be inserted you can spot-check them before loading by using

[PHP]Select * from #TempTable[/PHP]

3. Insert data into new tables

[PHP]INSERT into NEWTABLE
SELECT * FROM #TempTable

INSERT into XREF
SELECT ID FROM #TempTable[/PHP]

[DavidOxclove]

I wouldn't do a loop just because it is not efficient


1. Get all records to insert into temp table.

[PHP]SELECT * into #TempTable
FROM MSGTABLE
WHERE ID not in (SELECT ID FROM XREF) -- FIND ID that is not in XREF TABLE
and PARENT in (SELECT ID FROM XREF -- As I understand parent could be 0 or must exist in XREF table
UNION
SELECT 0)[/PHP]

2. Now you've got all records that about to be inserted you can spot-check them before loading by using

[PHP]Select * from #TempTable[/PHP]

3. Insert data into new tables

[PHP]INSERT into NEWTABLE
SELECT * FROM #TempTable

INSERT into XREF
SELECT ID FROM #TempTable[/PHP]

Yes, this is a one-time migration.

This is a lot closer to what I'm shooting for, but the problem is that my cross-reference table, XREF doesn't have an entry until the insert into the new table.

Basically tbl XREF just stores the row's ID from MSGTABLE along with it's ID from the new table (which is the return value from the given procedure)

I need to be able to check each row in MSGTABLE, if it has a 0 for the parent I can just call the insert proc, and then insert the information (ID from MSGTABLE and the result from the proc) into my XREF table,

but if the parent isn't 0, I have to look up the parent in the XREF table, and then I can call the stored proc (with the results of the lookup) and then insert the result in XREF.

[DavidOxclove]

Here's some simplified sample tables that might help me explain my situation

[HTML]MSGTABLE
ID Message parentID
1 Hello 0
2 Re:Hello 1
3 Re:Re:Hello 2
4 Re:Hello 1
5 Hi There 0
6 Re:Re:Re:Hello 3
7 Re:Hi There 5
[/HTML]
[HTML]tbl_XREF
ID new_ID[/HTML]
[HTML]NEWTABLE
ID Message parentID[/HTML]

I need to move/copy all the elements from MSGTABLE to NEWTABLE, keeping their relationships intact. My insert permissions for NEWTABLE are limited to a stored procedure, if_sp_InsertPost(Message, parentID, @intIdentity OUTPUT).

Also, in NEWTABLE, I have no idea what the ID will be (could be starting at 4000, could be alphabetic)

[iburyak]

Yes, this is a one-time migration.

This is a lot closer to what I'm shooting for, but the problem is that my cross-reference table, XREF doesn't have an entry until the insert into the new table.

Basically tbl XREF just stores the row's ID from MSGTABLE along with it's ID from the new table (which is the return value from the given procedure)

I need to be able to check each row in MSGTABLE, if it has a 0 for the parent I can just call the insert proc, and then insert the information (ID from MSGTABLE and the result from the proc) into my XREF table,

but if the parent isn't 0, I have to look up the parent in the XREF table, and then I can call the stored proc (with the results of the lookup) and then insert the result in XREF.

What should be done if parent isn't 0 and you wasn't able to find matching record in XREF table?

[DavidOxclove]

What should be done if parent isn't 0 and you wasn't able to find matching record in XREF table?

Provided there are no errors with the data, that shouldn't occur, but in the case that it did, I suppose I would like to just skip that row and continue.

BTW, I really appreciate all your help with this :)

[iburyak]

I'll do it tomorrow. I have to go now.
I think I have a picture on what to do.

[iburyak]

1. Create Basic database objects and insert test data

[PHP]create table MSGTABLE ( ID int,
Message varchar(20),
parentID int )
go
insert into msgtable values (1, 'Hello', 0)
insert into msgtable values (2, 'Re:Hello', 1)
insert into msgtable values (3, 'Re:Re:Hello', 2)
insert into msgtable values (4, 'Re:Hello', 1)
insert into msgtable values (5, 'Hi There', 0)
insert into msgtable values (6, 'Re:Re:Re:Hello', 3)
insert into msgtable values (7 , 'Re:Hi There', 5)
go
Create table tbl_XREF
(ID int,
new_ID int)
go

Create table NEWTABLE(
ID int identity(100,1),
Message varchar(20),
parentID int)

go
Create proc if_sp_InsertPost
@Message varchar(20),
@parentID int,
@intIdentity int OUTPUT
as
insert into NEWTABLE (Message, parentID) values ( @Message, @parentID)

Set @intIdentity = @@identity
go[/PHP]

2. Run insert

[PHP]DECLARE @ID int,
@Message varchar(20),
@parentID int,
@new_ID int

DECLARE InsertMessages CURSOR FOR
SELECT ID, Message, parentID
FROM MSGTABLE
ORDER BY parentID

OPEN InsertMessages

FETCH NEXT FROM InsertMessages INTO @ID, @Message, @parentID
WHILE @@FETCH_STATUS = 0
BEGIN
IF @parentID = 0
BEGIN
EXEC if_sp_InsertPost @Message, @parentID, @new_ID output

IF NOT EXISTS(Select * from tbl_XREF where ID = @ID and new_ID = @new_ID)
INSERT INTO tbl_XREF values (@ID, @new_ID)
END
ELSE
BEGIN
SELECT @parentID = new_ID FROM tbl_XREF WHERE ID = @parentID

EXEC if_sp_InsertPost @Message, @parentID, @new_ID output

IF NOT EXISTS(Select * from tbl_XREF where ID = @ID and new_ID = @new_ID)
INSERT INTO tbl_XREF values (@ID, @new_ID)
END
FETCH NEXT FROM InsertMessages INTO @ID, @Message, @parentID
END

CLOSE InsertMessages
DEALLOCATE InsertMessages[/PHP]
3. Test Data

[PHP]select * from MSGTABLE
select * from tbl_XREF
select * from NEWTABLE[/PHP]


Irina.

[DavidOxclove]

Irina,
Did I mention you're awesome?
Thank you!
Now I can keep my job and my children won't go hungry!

[iburyak]

:)

Send me a private e-mail. I will give you my direct e-mail address...