Insert query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Insert query

Author

Topic

Willio
Starting Member

20 Posts

Posted - 2007-08-09 : 09:01:33

This is a question about an insert query i like to build. I searched the internet and it looks like i can't be done. Still i don't know how to solve the problem.

In my database there is a table period_car

A row looks like:
ID carID fuelprice
734 3 1.05

When there comes a new period then i want to copy all data from the old one but NOT the old ID. That need to become a new number.

I thought i could use the insert statement but when there are multiple rows to copy i get the following:

INSERT INTO period_car
VALUES (new_ID, (SELECT carID, fuelprice
FROM period_car
WHERE period_car = old_id))

I am not surprised it doesn't work but doesn't know either how to solve te problem so i can insert the rows.

Thanks

sshelper
Posting Yak Master

216 Posts

Posted - 2007-08-09 : 09:36:26

Here's how your query will look like:

INSERT INTO period_car (carID, fuelprice)
SELECT carID, fuelprice
FROM period_car
WHERE ID = old_ID

I am assuming that your ID column is an identity column.

SQL Server Helper
http://www.sql-server-helper.com

Willio
Starting Member

20 Posts

Posted - 2007-08-09 : 09:54:57

quote:
Originally posted by sshelper
I am assuming that your ID column is an identity column.

Actually it is but i need to insert it manually. Because it links two tables. Is it possible to insert one field manualy?

sshelper
Posting Yak Master

216 Posts

Posted - 2007-08-09 : 10:19:54

Yes, you can insert a field manually. Assuming that you have the new ID in a variable called @NewID:

INSERT INTO period_car (ID, carID, fuelprice)
SELECT @NewID, carID, fuelprice
FROM period_car
WHERE ID = old_ID

Also, since it is an identity field, make sure to set the IDENTITY_INSERT to ON for that table.

SQL Server Helper
http://www.sql-server-helper.com

Willio
Starting Member

20 Posts

Posted - 2007-08-09 : 10:45:57

Well i am programming it in VBA. But i wouldn't think it is possible to replace the variable by an VBA variable. So if a can fill the @New_id with the old id value it is perfect. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources