October 9, 2009 at 1:04 pm
Hi,
I have to inserts all the rows from source table to target table. But the query returns the follwing error:
Msg 515, Level 16, State 2, Line 48
Cannot insert the value NULL into column 'topic_id', table 'Mig_PHPBB_DEV.dbo.phpbb_posts'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Line 38
Cannot insert the value NULL into column 'topic_id', table 'Mig_PHPBB_DEV.dbo.phpbb_posts'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Query :
==========
declare @id as int
declare @tid as int
declare @poster_id as int
declare @postername as varchar(50)
declare @cat as varchar(80)
declare @subcat as varchar(80)
declare @topic as varchar(500)
declare @sub as varchar(500)
declare @subID as int
declare @body as varchar(800)
DECLARE RepliesCUR CURSOR FOR
select category, subcategory, topic, subject, body
from replies
OPEN RepliesCUR
FETCH NEXT FROM RepliesCUR into @cat,@subcat,@topic,@sub,@body
Set @id = (select forum_id from phpbb_forums where Forum_name=@Cat
and forum_type=0)
Set @subid = (Select forum_id from phpbb_forums where
parent_id=@id and forum_name=@subcat and forum_type=1 )
Set @tid = (select topic_id
from phpbb_topics
where Forum_id =@subid
and topic_title =@topic)
--Set @poster_id = (select user_id from phpbb_users where username = @postername)
WHILE @@FETCH_STATUS = 0
BEGIN
insert into phpbb_posts(forum_id,topic_id,post_subject,post_text)
values (@subid,@tid,@sub,@body)
FETCH NEXT FROM RepliesCUR into @cat,@subcat,@topic,@sub,@body
Set @id = (select forum_id from phpbb_forums where Forum_name=@Cat)
Set @subid = (Select forum_id from phpbb_forums where
parent_id=@id and forum_name=@subcat and forum_type=1 )
Set @tid = (select topic_id from phpbb_topics where Forum_id =@subid
and topic_title =@topic)
insert into phpbb_posts(forum_id,topic_id,post_subject,
post_text)
values (@subid,@tid,@sub,@body)
--Set @poster_id = (select user_id from phpbb_users where username = @postername)
End
close RepliesCUR
deallocate RepliesCUR
please help
"More Green More Oxygen !! Plant a tree today"
October 9, 2009 at 1:08 pm
Minaz,
FETCH NEXT FROM RepliesCUR into @cat,@subcat,@topic,@sub,@body
Set @tid = (select topic_id
from phpbb_topics
where Forum_id =@subid
and topic_title =@topic)
insert into phpbb_posts(forum_id,topic_id,post_subject,post_text)
values (@subid,@tid,@sub,@body)
It looks like the select is not returning any values in your table for this topic_title, and the table does not allow NULLS. Should the select always return a topic_ID based on the topic_title?
October 9, 2009 at 1:54 pm
I have to ask: Why is this being done with a cursor? It is terribly inefficient, as is populating each variable with a separate query to the same table.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 12, 2009 at 3:53 am
Bob is suggesting something like:
INSERT INTO phpbb_posts(forum_id, topic_id, post_subject, post_text)
SELECT F2.forum_id, T.topic_id, R.[subject], R.body
FROM replies R
JOIN phpbb_forums F1
ON R.category = F1.Forum_name
AND forum_type = 0
JOIN phpbb_forums F2
ON F1.forum_id = F2.parent_id
AND R.subcategory = F2.forum_name
AND forum_type = 1
JOIN phpbb_topics T
ON F2.forum_id = T.forum_id
AND F2.topic_title = R.topic
Please check this code produces the desired results before applying it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply