Insert Into

Question

Post reply

Insert Into

_taz_

Ten Centuries

Points: 1225
More actions
June 16, 2010 at 2:45 pm

#233911

This runs in line with my previous post.
I put my combines notes into a table AllNotes with 2 columns Userid and Notes
I want to insert the Notes into an existing table in another DB. The UserID is common to both, however there are more UserID's in the 2nd DB than in AllNotes. UserID is unique in each table so there is no duplicate within one table.
I did this script
Insert into DB1.dbo.Client (Notes)
Select DB2.dbo.AllNotes.notes
From DB2.dbo.AllNotes inner join
DB1.dbo.Client on cid=UserID
I get this error
"Msg 512, Level 16, State 1, Procedure trClient_IOIns, Line 20
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."
I know there are no duplicate ID's

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

It doesn't look like a message you can get from that statement.

Are you sure there's no trigger on the table?

-- Gianluca Sartori

_taz_ Ten Centuries Points: 1225 More actions · Answer 2

I didn't design DB1 so I can't say for sure. But I can't find anything.

I imported a bunch of stuff earlier and it went fine

I just tried with one client by adding

where clientid=23680

to the end and it worked fine

scratch that it said it completed, but when I look in the DB1 the Notes field is still NULL

ok found trigger, trying to read through it and get around it (disabled it, then I hit a constraint error)

at least I'm learning something today

_taz_ Ten Centuries Points: 1225 More actions · Answer 3

Ok, so I disabled the trigger. then I got a constraint error so I disbaled the constraint checking on insert. then I get a Primary Key error about inserting duplicate userid. I must have something wrong in my insert statement, I don't want to insert new userid's I just want to insert the notes from allnotes table into the clients table where the cid in allnotes =userid in clients

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

If you just want to update table Client, you must use an UPDATE statement:

UPDATE DB1.dbo.Client

SET Notes = DB2.dbo.AllNotes.notes

FROM DB1.dbo.Client

INNER JOIN DB2.dbo.AllNotes

ON cid=UserID

If you also want to insert missing rows, you could use the MERGE statement:

MERGE INTO DB1.dbo.Client AS Target

USING (

SELECT UserId, Notes

FROM DB2.dbo.AllNotes.notes

) AS Source (UserId, Notes)

ON Target.cid = Source.UserId

WHEN MATCHED

THEN UPDATE SET Target.Notes = Source.Notes

WHEN NOT MATCHED

THEN INSERT (cid, Notes) VALUES (Source.UserId, Source.Notes);

-- Gianluca Sartori

_taz_ Ten Centuries Points: 1225 More actions · Answer 5

Gianluca Sartori (6/17/2010)
If you just want to update table Client, you must use an UPDATE statement:
UPDATE DB1.dbo.Client
SET Notes = DB2.dbo.AllNotes.notes
FROM DB1.dbo.Client
INNER JOIN DB2.dbo.AllNotes
ON cid=UserID
If you also want to insert missing rows, you could use the MERGE statement:
MERGE INTO DB1.dbo.Client AS Target
USING (
SELECT UserId, Notes
FROM DB2.dbo.AllNotes.notes
) AS Source (UserId, Notes)
ON Target.cid = Source.UserId
WHEN MATCHED
THEN UPDATE SET Target.Notes = Source.Notes
WHEN NOT MATCHED
THEN INSERT (cid, Notes) VALUES (Source.UserId, Source.Notes);

DOH! now I feel stupid.. I knew that. guess I got caught up in fixing it rather than looking at what was wrong.

Thanks