INSERT SELECT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

INSERT SELECT

Author

Topic

studentbelfort
Starting Member

12 Posts

Posted - 2008-01-31 : 10:22:13

Hi guys, I have the following problem :

I have basically 3 tables, Customer, Car and Rental. Customer is composed of Customer_Id and Customer_Name. Car is composed of Car_Id and Make. And to finish Rental is composed of R_Id (primary key), Customer_Id, Car_Id and Number_Days.

Ok, what I've been trying to do is to add a new field in Rental (using only 1 SQL Statement). Car_Id, Number_Days, and Customer_Name are given. So I tried :

INSERT INTO RENTAL (Customer_Id, Car_Id, Number_Days)
VALUES ((SELECT DISTINCT Customer.Costumer_Id From Costumer, Rental WHERE Rental.Costumer_Id=Costumer.Costumer_Id AND Costumer_Name="Müller"), 5, 4);

But then I got an error message. So I tried it again without VALUES

INSERT INTO RENTAL (Customer_Id, Car_Id, Number_Days)
SELECT (SELECT DISTINCT Customer.Costumer_Id FROM Costumer, Rental WHERE Rental.Costumer_Id=Costumer.Costumer_Id AND Costumer_Name="Müller"), 5, 4
FROM Rental;

But then more than 70 rows were added to the table. So I tried again :

INSERT INTO RENTAL (Customer_Id, Car_Id, Number_Days)
SELECT DISTINCT (SELECT DISTINCT Customer.Costumer_Id FROM Costumer, Rental WHERE Rental.Costumer_Id=Costumer.Costumer_Id AND Costumer_Name="Müller"), 5, 4
FROM Rental;

This time I could accomplish what I originally wanted but I still believe that there is an easier way to do this, right ? Could someone please help me ?

Thank you very much and cheers from Switzerland

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-31 : 10:34:37

This?

INSERT		Rental
		(
			Customer_ID,
			Car_ID,
			Number_Days
		)
SELECT DISTINCT	c.Costumer_ID,
		5,
		4
FROM		Costumer AS c
INNER JOIN	Rental AS r ON r.Costumer_ID = c.Costumer_ID
WHERE		c.Costumer_Name = 'Müller'

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-01-31 : 10:35:35

am a bit confused....

if you're adding a column (or field), do you really want to insert rows, or update existing ones?

if it's really an insert why the join to the existing rental records?

why use distinct?

why the 'sub-select'?

...and watch your spelling... customer / costumer?

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-01-31 : 10:45:59

i'm gonna guess you used the distinct because it joins to the rental table lots, so actually you don't want the join at all, i.e.

insert rental (Customer_Id, Car_Id, Number_Days)
select customer_id,5,4
from Customer
where customer_name = 'Müller'

Em

studentbelfort
Starting Member

12 Posts

Posted - 2008-01-31 : 10:54:17

Thanks for the replies. Yes, elancaster, your statement gives me exactly what I wanted. I'm new to SQL and as the first time the SELECT (SELECT DISTINCT) didn't work, I tried SELECT DISTINCT (SELECT DISTINCT) and then I could add this new row to the table.As I'm new to it I didn't know one can/shall do it the way you did, which is indeed much easier to visualize. Anyway, thanks for the explanation !

studentbelfort
Starting Member

12 Posts

Posted - 2008-01-31 : 10:55:52

Ah and sorry for the wrong spelling, it was a fast translation, it should be "Kunde" after all

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-01-31 : 10:59:09

which does that translate to? costumer or customer?

studentbelfort
Starting Member

12 Posts

Posted - 2008-01-31 : 11:03:53

quote:
Originally posted by elancaster

which does that translate to? costumer or customer?

Em

Customer, you see, I learn English and SQL at the same time :)
Costumer should be "Schneider" I guess.......and I think costumers are not really interested in renting cars at all hehe

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources