Insert statement using 3 tables

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 2005 Forums

Transact-SQL (2005)

Insert statement using 3 tables

Author

Topic

keentolearn
Starting Member

21 Posts

Posted - 2009-07-10 : 15:26:25

Hi All,

I need to insert data from table1 (ExpID,Description) into Table2 (LookUpID, Value) based on a certain create in Table3. I can join Table1 with Table3 using the MainID column as table1.ExpID = Table3.MainID I want to only insert data from table1 into table3 where the creator in table3 = 'OO'

I have started with this but it is returning an error msg as it doesnt recorgnise the MainID:

DECLARE @ID int, @wording nvarchar(4000)
DECLARE Importcursor CURSOR FOR
SELECT 15, EnglishWording FROM DRALL AS A
inner Table3 as b on b.main = a.ExpID
where b.createdby='OO'
OPEN Importcursor

FETCH NEXT FROM Importcursor INTO @ID, @Wording
WHILE @@FETCH_STATUS = 0

BEGIN
INSERT INTO Table2 (LookUpID, LookUpValue)
(SELECT @ID, EnglishWording FROM Table1)

FETCH NEXT FROM Importcursor INTO @ID, @Wording
END

CLOSE Importcursor
DEALLOCATE Importcursor

I would very much appreciate you help as soon as possible.

Many thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-10 : 15:59:37

No cursor required.

INSERT INTO Table2 (LookUpID, LookUpValue)
SELECT 15, a.EnglishWording
FROM DRALL a
JOIN Table3 b
On a.ExpID = b.main
WHERE b.Creator = 'OO'

keentolearn
Starting Member

21 Posts

Posted - 2009-07-13 : 07:32:03

[quote]Originally posted by russell

No cursor required.

INSERT INTO Table2 (LookUpID, LookUpValue)
SELECT 15, a.EnglishWording
FROM DRALL a
JOIN Table3 b
On a.ExpID = b.main
WHERE b.Creator = 'OO'

Russell, many thanks for your response. The problem I have with the INSERT sttatement is if I do the way you suggest, I receive the following error msg:
Msg 8152, Level 16, State 10, Line 1
String or binary data would be truncated.
The statement has been terminated.

Any idea why?

Thanks again.

keentolearn
Starting Member

21 Posts

Posted - 2009-07-13 : 08:50:34

Please ignore the question regarding the error msg. I have fixed the issue where some fields had too many characters..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources