How to insert data from one table to other table with some static data in single statement?

Question

I've one table Car as below

Car_ID | Car_Name

Sample data

Car_ID    Car_Name
1         Honda City
2         Ferrari
3         Mercedes
4         Rolls Royce

I've data in Car table. Now i've other table Car_Detail as below

Car_Detail_ID | Car_ID | Car_Color

Now i've to insert data into Car_Detail from Car where Car_Color will be default value. How to achieve this in single statement ?

Expected result

Car_Detail_ID   Car_ID    Color
1               1         Red
2               2         Red
3               3         Red
4               4         Red

EDIT

Sample Data

Car Table

Car_ID    Car_Name
1         Honda City
2         Ferrari
3         Mercedes
4         Rolls Royce

Color Table

Color_ID    Color_Name
1           Red
2           Yellow
3           Blue
4           Green

Expected Result

Car_Detail_ID   Car_ID    Color_ID
1               1         1
2               2         1
3               3         1
4               4         1
5               1         2
6               2         2
7               3         2
8               4         2
9               1         3
10              2         3
11              3         3
12              4         3
13              1         4
14              2         4
15              3         4
16              4         4

John Woo · Accepted Answer · 2012-11-22T14:01:46.433

3

use INSERT INTO...SELECT statement, the query below assumes that Car_Detail_ID is an auto-incremented column and Color has a default value of Red

INSERT INTO Car_Detail (Car_ID)
SELECT Car_ID
FROM Car

but if not,

INSERT INTO Car_Detail (Car_ID, Color)
SELECT Car_ID, 'Red' Color
FROM Car

or something like this,

INSERT INTO Car_Detail (Car_Detail_ID, Car_ID, Color)
SELECT Car_ID, Car_ID, 'Red' Color
FROM Car

UPDATE 1

use CROSS JOIN

INSERT INTO CAr_Detail(Car_ID, Color_ID)
SELECT  Car_ID, Color_ID
FROM Car CROSS JOIN Color

SQLFiddle Demo

edited Nov 22 '12 at 14:01

answered Nov 22 '12 at 13:25

John Woo

258,903
69
498
492

If we've to add from two table than? Though above code is working fine. – IT ppl Nov 22 '12 at 13:45
the source of records are from two table or the destination of the records is to the two tables? – John Woo Nov 22 '12 at 13:46
suppose car_detail contains tow FK – IT ppl Nov 22 '12 at 13:49
you can using `UNION` in your select or `JOIN`, can you show smple data? – John Woo Nov 22 '12 at 13:53
Ok, I m updating data in question – IT ppl Nov 22 '12 at 13:54
Updated data into question, please find in EDIT portion. – IT ppl Nov 22 '12 at 14:00
Can u please help with http://stackoverflow.com/questions/13697368/updating-inserted-record-within-merge-statement-in-sql-server-2008-r2 this question? – IT ppl Dec 04 '12 at 14:08

How to insert data from one table to other table with some static data in single statement?

EDIT

1 Answers1