By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Help with insert into query plz

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Jul 23 '05 #2

P: n/a
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)
Jul 23 '05 #3

P: n/a
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

Jul 23 '05 #4

P: n/a
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)
Jul 23 '05 #5

P: n/a
YES!!!!!!!!!!!!!!!!!!!!!!!!!
it worked, Thank you all for your help. I greatly appreciate it

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.