Insert to a table

Can I do an insert from table A to table B only columns that are in A but not
in B and changing them before insert? I want the primary key of rows in A
that don't exist in B to be inserted in B, but I want to flag them in a
couple of other columns. I see that I can either use an Insert query with
select statement where all the columns are inserted as is or Insert...Values
where all are manual values. Is there a statement that can be a combination
of these two?
ThanksYou should be able to formulate an INSERT SELECT statement to do what you
want:
INSERT INTO TableB (col1, col2, col3, col4, ...)
SELECT colA, 'some value', 1234, colB, ...
FROM TableA
WHERE ...
--
David Portas
SQL Server MVP
--|||On Mon, 13 Dec 2004 15:01:01 -0800, Niles wrote:
>Can I do an insert from table A to table B only columns that are in A but not
>in B and changing them before insert? I want the primary key of rows in A
>that don't exist in B to be inserted in B, but I want to flag them in a
>couple of other columns. I see that I can either use an Insert query with
>select statement where all the columns are inserted as is or Insert...Values
>where all are manual values. Is there a statement that can be a combination
>of these two?
>Thanks
Hi Niles,
You mean something like this?
INSERT INTO TableB (KeyColumn, OtherColumn, ThirdColumn)
SELECT KeyColumn, OtherColumn, 'Literal value'
FROM TableA AS a
WHERE NOT EXISTS
(SELECT *
FROM TableB AS b
WHERE b.KeyColumn = a.KeyColumn)
or alternatively:
INSERT INTO TableB (KeyColumn, OtherColumn, ThirdColumn)
SELECT a.KeyColumn, a.OtherColumn, 'Literal value'
FROM TableA AS a
LEFT OUTER JOIN TableB AS b
ON b.KeyColumn = a.KeyColumn
WHERE b.KeyColumn IS NULL
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)