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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy