Insert a Row with values from multiple tables (SQL Server)

I'm defining a delete trigger, and I need to backup the row deleted but only a few arguments from the original and including one column from other table:

TableC:

* Column 1: value from a column in TableA

* Column 2 to 6: values from colums 1,2,3,5,6 from TableB

All I want is something like this:

INSERT into TableC values (

(SELECT Col开发者_开发百科umn1A from TableA where TableA.Column = 'SomeValue'),

(SELECT column1, column2, column3, column5, column6 from TableB));

The result on TableC must be:

Column1A , column1, column2, column3, column5, column6

But that is not working. In my special case, TableB is the deleted table accessible only in triggers.

I'm using SQL-Server 2008 but all I need is the logic of the query, and then I try to translate it.

Thank you.

In another case, which I honestly find very odd, that might be closer to what you're describing, this might work:

INSERT INTO MyTable
SELECT 
    (SELECT ColumnA FROM Table1),
    Table2.ColumnA,
    Table2.ColumnB,
    Table2.ColumnC,
    Table2.ColumnD
FROM
    Table2

That way, you're only selecting that one column from Table1, yet selecting many columns from Table2, regardless of any relationship between them.

You need to do joins on the select.
Here's an example:

INSERT INTO MyTable
SELECT 
    Table1.ColumnA,
    Table1.ColumnB,
    Table2.ColumnA,
    Table2.ColumnB
FROM
    Table1
    INNER JOIN Table2 ON Table1.ID = Table2.ID  

This is just an example of "inserting from two tables".
You need to modify it to match what you're looking for, which I did not comprehend from your arbitrary example.

You can use a cross join to join unrelated tables:

INSERT  MyTable
        (col5, col7)
SELECT  t1.col5
,       t2.col7
FROM    Table1 t1
CROSS JOIN 
        Table2 t2
WHERE   t1.ID = 'SomeValue'
        and t2.ID = 'OtherValue'

If one of the tables just contains one row, you can omit the where part for it.