CODEWITHSUNDEEP
sqlsql-serverinsert
jenn
jenn

Reputation:

SQL: INSERT INTO...VALUES..SELECT

How do I write INSERT statement if I get the values of colA from TableX, colB from TableY and colC from TableZ?

For example:

INSERT INTO TableA (colA, colB, colC) VALUES (?,?,?)

Any ideas if it is possible?

Upvotes: 23

Views: 74088

Answers (4)

Andomar
Andomar

Reputation: 238246

In response to marc_s's answer, you can query from unrelated tables in a since select like:

INSERT INTO TableA
    (colA, colB, colC)
SELECT
    (SELECT valA FROM TableX WHERE ...),
    (SELECT valB FROM TableY WHERE ...),
    (SELECT valC FROM TableZ WHERE ...)

Upvotes: 24

lexx
lexx

Reputation: 637

You will need to join the tables that you want to make the selection from.

Here is a resource on SQL joins:

www.w3schools.com/sql/sql_join.asp

You also might want to check out this free PDF book from the guys at www.simple-talk.com that covers SQL basics:

SQL Server Crib Sheet Compendium

Upvotes: 0

marc_s
marc_s

Reputation: 755187

INSERT INTO TableA(colA, colB, colC)
  SELECT TableX.valA, TableY.valB, TableZ.valC
    FROM TableX
   INNER JOIN TableY ON :......
   INNER JOIN TableZ ON ........

Of course, TableX, TableY and TAbleZ might also be related in some other way (not INNER JOIN).

If you cannot find any relation between the tables AT ALL, you could also do three separate

SELECT @value1 = valA FROM TableX WHERE ......
SELECT @value2 = valB FROM TableY WHERE ......
SELECT @value3 = valC FROM TableZ WHERE ......

and then an insert like this:

INSERT INTO TableA(colA, colB, colC)
             VALUES(@value1, @value2, @value3)

That's the ultimate last resort, you can can't express everything in a single SELECT statement.

Marc

Upvotes: 40

Binary Worrier
Binary Worrier

Reputation: 51719

Insert into TableA (ColA, ColB, ColC) . . .

Must be the column names as the are in Table A. There's nothing wrong with 

Insert into TableA (ColA, ColB, ColC) . . .
Select TableX.Col1, TableY.Col1, TableZ.Col5 
From TableX, TableY, TableZ
Where . . .

Upvotes: 4

Related Questions