identity_insert?

I need to archive from one table to another but the new table, which is a duplicate of the old one, won't allow inserts into the ID column.
I am using:
set identity_insert soldVehicles on
INSERT INTO soldVehicles
SELECT *
FROM vehicles
Where sent2sold = 'yes'
but I get this error:
Error -2147217900


An explicit value for the identity column in table 'soldVehicles' can only be specified when a column list is used and IDENTITY_INSERT is ON.

set identity_insert soldVehicles on
INSERT INTO soldVehicles
SELECT *
FROM vehicles
Where sent2sold = 'yes'

As I have turned ID_insert ON it must be the column list?...
not sure what to do next.good assumption -- the error message actually says that|||Are you sure both table structures are identical?
An insert without an explicit column list is never a good idea, just as a SELECT * should not be used for this. SELECT * does not necessarily return the columns in the order expected by an unqualified INSERT INTO.|||Yes, that's correct. When the identity_insert option is set to True, you must specify the column list.|||Having just read your question again, I think you are taking the wrong approach. I don't see why you need to explicitly turn on identity_insert. If you need an identity column for the soldVehicles which will contain values that are distinctly different from the identity values in the original table, then there is no need to use the identity_insert option.

If on the other hand, you only want a single identification column in the soldVehicles table which will have values corresponding to those in the vehicles table, then there is no need to use neither an identity column nor hence the identity_insert option. You would just create a standard column to represent the vehicle ID numbers from the original table and insert the values as you would with any other insert operation.

In the following code sample, constraint and index definitions have been specified in shorthand notation for convenience. In a production system, I recommend proper naming of all objects.

create table vehicles
(
vehicleID identity(1,1) not null unique,
columnA int not null
)

create table soldVehicles
(
vehicleID int not null unique,
columnA int not null,
foreign key vehicleID references vehicles (columnA)
)

insert into soldVehicles (vehicleID, columnA)
select
v.VehicleID, v.ColumnA
from
vehicles v

Although this method is easy to implement and can indeed provide the functionality that you have described, are you not just after a column to mark whether or not a vehicle has been sold? Do you really need an extra table?|||thanks guys,
As Rudy well knows, I am a DB dunce and my old tables are shockingly simplistic and er...large.
To help out in the short-term I am trying to strip out and archive as much unused data as possible, rather than just marking it.
The new ID doesn't need to relate to its old value.
Looks like I am going to have to explicitly write out the list of column names.|||Are you from Cornwall?|||more or less, Devon now|||Oh yes. It is lovely down there.|||Looks like I am going to have to explicitly write out the list of column names.
Which is exactly what the error message told you to do.|||listen guys, when a person knows jack about databases, an error message like that is still pretty cryptic. I still didn't know what to do next, OK?
Robert chose to help, and now I know what to do.

these 'stating the obvious' replies don't help anyone.

In any case I have learnt not to trust error messages explicitly.|||these 'stating the obvious' replies don't help anyone.

I believe that comment was referring to you Ivon.|||thanks Robert.
I am trying to implement an insert similar to your example but I am getting a syntax error.
INSERT INTO soldVehicles (alt01, alt02, alt03, alt04, alt05) VALUES (SELECT alt01, alt02, alt03, alt04, alt05 FROM vehicles WHERE sent2sold = 'yes')
GO
Incidentally, what does the V. stand for in your example? do I need that to do this sort of batch insert?|||When using a SELECT to provide the values for an INSERT the VALUES keyword is not allowed. INSERT INTO soldVehicles (alt01, alt02, alt03, alt04, alt05)
SELECT alt01, alt02, alt03, alt04, alt05 FROM vehicles WHERE sent2sold = 'yes'
GO
See the manual (http://msdn2.microsoft.com/en-us/library/ms174335.aspx) for details.|||excellent! that's cracked it.
Thanks shammat|||Which is exactly what the error message told you to do.
Um...no. That has little to do with what the error message was saying. The main point is the IDENTITY INSERT requirement.|||The main point is the IDENTITY INSERT requirement.but the error message said two things (unless it was misquoted, and i can't be bothered to set up the test situation to reproduce the exact wording): identity_insert must be on, and you must use a column list

seeing as how identity_insert was on, it seems natural to conclude from the error message the need to use a column list, which was missing|||That's absolutely true Rudy, but some of us guys who prefer colouring-in pretty pictures, are still a bit in the dark even after an apparently obvious error message. it seems obvious now, even to me, but when I started this, I didn't know what a column list was! I had an incling but I was fed up guessing, so... I asked the experts. I speculated that it must be the column list and indicated I wasn't sure with a lovely little question mark.
Que your chance to show how clever you are.
or take a cheap swipe at the looser with no DB knowlege.

Anyway, all done now, I now know a tiny bit more and until I manage to find and employ a DB expert locally, I am fractionally better at doing it myself.|||another happy customer.

the plan to kill all traffic to this site is almost complete.|||Que your chance to show how clever you are.
or take a cheap swipe at the looser with no DB knowlege.

Why don't you have any DB knowledge? Read the help files, SQL Server's are excellent and I know they would have shown you the correct syntax for an INSERT statment because that's how I learned it. In fact, you should read it right now (http://msdn2.microsoft.com/en-us/library/ms189826.aspx). They break down what each part of any SQL statement means.

And you should dig around here to see recommendations for books to read. Don't pity yourself, (that's Mr. T's job) educate yourself. And it's lose, not loose.|||I believe that comment was referring to you Ivon.
Probably, yes. But if it was obvious, why post the question here?
It wasn't clear from the OP that darkmunk was a 'dunce' and 'looser with no DB knowledge' (his words, not mine). The OP made him look like someone who simply didn't read the error message. And yes, my post was totally unnecessary and unhelpful. Apologies to everyone who wants them.

(Also, I hate forums that don't quote quotes. That used to work here, didn't it?)|||ah, the world is a funny place isn't it?
Full of strange people who will never in a million years be able to visit a microsoft site and come away educated.

Thanks for the spelling lesson tho', don't know how I'd have got thru life without that.

Thanks to Robert and 'shammat' and anyone else with an ounce of empathy.
The job was sorted ages ago.|||Apologies to everyone who wants them.I'll take a dozen. I use 'em up pretty fast. Do you have any extra-large?|||I'll take a dozen. I use 'em up pretty fast. Do you have any extra-large?
Sure. D'you want fries with that order?