Help with insert into query plz

[ndn_24_7]

Hello all,

I'm trying combine 2 tables data into a single table. My tables look
like this

Table1
ID int 4
filename nvarchar 25
incident nvarchar 50
dreport nvarchar 10
treport nvarchar 7
doccur nvarchar 10
DateOccured datetime 8
toccur nvarchar 7
loc nvarchar 55
ucode nvarchar 20
rwby nvarchar 30
disp nvarchar 55
Narrative ntext 16

Table2
ID int 4
nincident nvarchar 50
ncompl nvarchar 50
nfilename nvarchar 25
narr ntext 16

I used these 2 statements to narrow each table down to matching
filenames:

Select *
INTO TABLE1b
from table1
where exists(select * from table2 where
table1.filename = table2.nfilename)

SELECT *
INTO TABLE2b
FROM table2
WHERE exists(select * from table1 where
table1.filename = table2.nfilename)

My issue now is how to copy the narr field in table2 and insert it in
the Narrative field in table1 where table1.filename = table2.nfilename.
For every matching filename and nfilename(both are the keys) I need to
pull the narr field from table2 and insert it into Narrative in
table1. Does this make sense? Any assistance is greatly appreciated

[louis]

begin tran

update table1
set narrative=ltrim(rtrim(narrative)) + ltrim(rtrim(narr))
from table1 A
join table2 B
on A.filename=B.nfilename

select top 500 * from table1

-- if it looks correct then run the following line
--- COMMIT
-- if looks wrong then run this line
--- ROLLBACK

[Hugo Kornelis]

On 18 Jan 2005 13:26:05 -0800, ndn_24_7 wrote:

(snip)
ssue now is how to copy the narr field in table2 and insert it in

the Narrative field in table1 where table1.filename = table2.nfilename.
For every matching filename and nfilename(both are the keys) I need to
pull the narr field from table2 and insert it into Narrative in
table1. Does this make sense? Any assistance is greatly appreciated

Hi ndn_24_7,

Something like this, maybe?

UPDATE Table1
SET Narrative = (SELECT Table2.Narr
FROM Table2
WHERE Table2.nfilename = Table1.filename)
WHERE EXISTS (SELECT *
FROM Table2
WHERE Table2.nfilename = Table1.filename)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[ndn_24_7]

I got this error when running Hugo query
[The text, ntext, and image data types are invalid in this subquery or
aggregate expression.]

and this error when running louis query
[Argument data type ntext is invalid for argument 1 of rtrim function.]
any sugestions

[Hugo Kornelis]

On 18 Jan 2005 14:30:56 -0800, ndn_24_7 wrote:

I got this error when running Hugo query
[The text, ntext, and image data types are invalid in this subquery or
aggregate expression.]

and this error when running louis query
[Argument data type ntext is invalid for argument 1 of rtrim function.]
any sugestions

Hi ndn_24_7,

Try this variation on Louis' query:

update table1
set narrative=narr
from table1 A
join table2 B
on A.filename=B.nfilename
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[ndn_24_7]

YES!!!!!!!!!!!!!!!!!!!!!!!!!
it worked, Thank you all for your help. I greatly appreciate it