Insert with select

Question

Insert with select

Rog Saber

SSCrazy

Points: 2548
More actions
September 7, 2006 at 11:33 am

#114321

This is pretty straightforward but I want to know an easy way to do it and I'm not sure of the exact syntax
I have a trigger. On the execution of that trigger, I want to perform an insert into another table. The table has a bunch of coulmns. I want to use all the values (except 2) from that last record of the table. The last record being the record with the most recent entry date.
Assume my table looks like this:
table A
col_1.....col_2.....col_3.....col_4.....col_5
Joe.......Simms....ABC.......123.......Jan 1, 2006
Joe.......Simms....BBB.......123.......Jan 2, 2006
Let's assume that I now want to insert a record using all the values of the most recent record except, instead of, I want to insert Bob - so now my table will look like this:
col_1.....col_2.....col_3.....col_4.....col_5
Joe.......Simms....ABC.......123.......Jan 1, 2006
Joe.......Simms....BBB.......123.......Jan 2, 2006
Bob.......Simms....BBB.......123.......Sep 7, 2006 (current_date)

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

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

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

Are you trying to use the record dated Jan 2 2006 (Most current b4 insert) and insert it again (with the Name Changed to BOB abd Date Changed to Current Date). Please clarify.

Thanks

Sreejith

Rog Saber SSCrazy Points: 2548 More actions · Answer 2

"Are you trying to use the record dated Jan 2 2006 (Most current b4 insert) and insert it again (with the Name Changed to BOB abd Date Changed to Current Date). Please clarify."

Yes

SQLBill SSC Guru Points: 51440 More actions · Answer 3

How are you getting "Bob"? If you can put that into a variable, your insert could be:

INSERT INTO tblname

SELECT @newname,

col_2,

col_3,

etc

FROM tableA

WHERE col-5 = (SELECT MAX(col_5 FROM table A)

-SQLBill

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 4

Within a trigger (except for INSTEAD OF triggers), the inserted table contains the rows that were inserted, consider the inserted table as an alias for the table associated with the trigger.

INSERT INTO MyOtherTable (col_1, col_2, col_3, col_4, col_5)

SELECT col_1, col_2, col_3, col_4, col_5

FROM inserted

Andy