IDENTITY function with INSERT

[ddeegan]

Hello

I am trying to use the IDENTITY function like....

insert into USR_COMPANY_HISTORY
select
DCT.CUSTOMER_ID as 'master_customer_id',
'0' as 'sub_customer_id',
identity(int,2464262,1) as sequence_no,
DCT.USR_DATASOURCE,
DCT.USR_USERID,
0 as concurrency_id,
null as prospect_id
from DCT_CUS_COMPANY_HISTORY DCT

But I receive an error?
"The IDENTITY function can only be used when the SELECT statement has an INTO clause."

Is there a way to use the IDENTITY function (or something equivalent) with a INSERT statement? I don't want to use "SELECT INTO" and recreate the table.

Thanks in advance
Dave

 

[SQLDenis]

How about having an identity field in the table?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[adamroof]

If destination table has Ident column...
and i specify field mappings...

SET IDENTITY_INSERT Formulations ON
INSERT INTO 	Formulations (FormulationID, ConfigurationID, MoldOption, MoldSize, FabThick, Program, Pressure)
SELECT		ID, ConfigurationID, Version, MoldSize, FabThick, Program, Pressure
FROM		OLDPOPSFormulations
SET IDENTITY_INSERT Formulations OFF

 

[ddeegan]

Hi Dennis

I'm not able to change the structure of the table I am inserting records into.

Thanks
Dave

 

[SQLDenis]

Then you will have to insert into a temp table with an identity field and then select from there

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[adamroof]

oops...missed out completely didnt i...

sorry

 

[ddeegan]

I didn't think of that- thank you very much!

Don't feel bad adamroof- SQLDenis is fast!

Thanks
Dave