Can't insert ntext value via insert into select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Can't insert ntext value via insert into select

Author

Topic

DeepBlueSea
Starting Member

5 Posts

Posted - 2005-08-30 : 16:08:58

Hi all,

I am encountering a problem when I am doing a subquery to insert ntext value. If I change the column name to a column which has a varchar, it works fine. If the column data type is ntext, query analyzer reports an error.

Server: Msg 279, Level 16, State 3, Line 1
The text, ntext, and image data types are invalid in this subquery or aggregate expression.

Please note that the datatype for childpage.summary and templates.body is ntext. Also, the tables are just dummy tables meant for testing.

Here are the ddl/dml statements.

drop table basepage

create table basepage (basepagekey bigint CONSTRAINT basepage_pk PRIMARY KEY, name varchar(20))

insert into basepage(basepagekey, name) values (1, 'one')
insert into basepage(basepagekey, name) values (2, 'two')
insert into basepage(basepagekey, name) values (3, 'three')
insert into basepage(basepagekey, name) values (4, 'four')
insert into basepage(basepagekey, name) values (5, 'five')

select * from basepage

drop table childpage

create table childpage (summary ntext, fbasepagekey bigint CONSTRAINT childpage_fk01 REFERENCES basepage (basepagekey))

insert into childpage(fbasepagekey, summary) values (1, 'cool')
insert into childpage(fbasepagekey, summary) values (2, 'hot')
insert into childpage(fbasepagekey, summary) values (3, 'sweet')

select * from childpage

insert into childpage(fbasepagekey, summary)
select basepagekey, (select body from dbo.templates where template_cd = 'XX') from basepage
where basepagekey not in (select fbasepagekey from childpage)

select * from childpage

Appreciate any help.

Thanks.

Kristen
Test

22859 Posts

Posted - 2005-08-31 : 00:07:25

This is what I would try:


insert into childpage(fbasepagekey, summary) 
select basepagekey, T.body
from basepage 
     JOIN dbo.templates AS T
         ON T.template_cd = 'XX'
where basepagekey not in (select fbasepagekey from childpage)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources