Insert with max values from multiple 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

General SQL Server Forums

New to SQL Server Programming

Insert with max values from multiple tables

Author

Topic

BSinatra
Starting Member

13 Posts

Posted - 2007-12-20 : 13:08:16

I am trying to add records to 2 separate tables and each table has a unique ID from the other table. I need to find the highest number ID from both tables (and add 1) and add a record with both new values to both tables.

I have tried the following, but it is not working. Any help would be appreciated, Thanks (my tables are MEMOS and PDSMSGC)

INSERT INTO MEMOS (MemoID, ParentID, FieldName, MemoText)
SELECT MAX(MemoID) + 1 FROM "MEMOS", MAX(MessageID) +1 FROM "PDSMSGC", 'pmc:MemoID', 'Hi my name is bob'
INSERT INTO PDSMSGC (MessageID, MemoID) SELECT MAX(MessageID) + 1 FROM "PDSMSGC", MAX(MemoID) FROM "MEMOS"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-20 : 13:21:04

Try this:

INSERT INTO MEMOS (MemoID, ParentID, FieldName, MemoText)
OUTPUT INSERTED.ParentID,INSERTED.MemoID INTO PDSMSGC
SELECT (SELECT MAX(MemoID) + 1 FROM MEMOS),
(SELECT MAX(MessageID) +1 FROM PDSMSGC),
'pmc:MemoID', 'Hi my name is bob'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 13:45:10

You mean MemoID column in Memos table is not IDENTITY?
You mean MessageID column in PdsMsgc table is not IDENTITY?

E 12°55'05.25"
N 56°04'39.16"

BSinatra
Starting Member

13 Posts

Posted - 2007-12-20 : 15:17:22

visakh16, thanks:
That gave me a syntax error at the INSERTED command.
Does it matter that I am using Pervasive SQL?

Peso, thanks, yes:
Those fields are not IDENTITY fields.

here is an example of what I am trying to do:
Before the command, the MAX numbers are: MEMOS.MemoID = 439 & PDSMSGC.MessageID = 18

If I knew what the number was each time, I would do this:
insert into MEMOS (MemoID, ParentID) SELECT '440', '19'
insert into PDSMSGC (MessageID, MemoID) SELECT '19', '440'

so instead I need to find out what the MAX number is for both columns and + 1, then take the new numbers and insert them into both tables.

thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 15:25:02

[code]DECLARE @MemoID INT,
@MessageID INT

BEGIN TRAN

SELECT @MemoID = MAX(MemoID)
FROM Memos

SELECT @MessageID = MAX(MessageID)
FROM PdsMsgc

SELECT @MemoID = COALESCE(@MemoID, 0) + 1,
@MessageID = COALESCE(@MessageID, 0) + 1

INSERT Memos
(
MemoID,
ParentID,
FieldName,
MemoText
)
VALUES (
@MemoID,
@MessageID,
'pmc:MemoID',
'Hi my name is bob'
)

INSERT PdsMsgc
(
MessageID,
MemoID
)
VALUES (
@MessageID,
@MemoID
)

COMMIT TRAN[/code]

E 12°55'05.25"
N 56°04'39.16"

BSinatra
Starting Member

13 Posts

Posted - 2007-12-20 : 16:23:34

Okay thanks, I've never worked with stored procs but it is a good time to learn.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources