INSERT to a table with a sub query

Question

Can I do this in SQL 2005?

SELECT 'C'+inserted.exhid AS ExhId,inserted.exhname AS ExhName,inserted.exhid AS RefID INTO mytable FROM inserted 
    WHERE inserted.altname IS NOT NULL

It won't work if the table exists, but will create the table if it is non-existent. How do I get it to insert into an existing table?

Answer 1

To insert into an existing table, use INSERT INTO instead of `SELECT INTO

Answer 2

like this

INSERT INTO mytable
SELECT 'C'+inserted.exhid AS ExhId,inserted.exhname AS ExhName,
 inserted.exhid AS RefID  FROM inserted 
    WHERE inserted.altname IS NOT NULL

you also don't need the aliases in this case

Answer 3

SQLMenace's answer is correct. But to add to it, I would suggest that it is a good practice to explicitly list your columns that you are inserting into in the event the table structure/column order ever changes, that way your proc stands a better change of working consistently.

INSERT INTO mytable ( 
ExhId, 
ExhName, 
RefID) 
SELECT 'C'+inserted.exhid,
inserted.exhname, 
inserted.exhid 
FROM inserted 
WHERE inserted.altname IS NOT NULL