RE: Identity_insert

RE: Identity_insert

 

  

BOL may not mention it but I've never been able to perform an identity
insert without specifying the field names as part of the insert statement.


jf


-----Original Message-----
From: Norkett Margaret
[mailto:mssqldba-ezmlmshield-x30083811.[Email address protected]
Sent: Wednesday, March 28, 2007 8:37 AM
To: LazyDBA Discussion
Subject: RE: Identity_insert

It doesn't like that insert into after the set identity_insert statement.

BOL doesn't mention using the columns just the set statement.

Thanks for trying, though.


If you have any questions or concerns, please let me know them.

Margaret Norkett
Database Administrator
* 864-271-6522 ext 222
* 864-270-2884




-----Original Message-----
From: Antoine CARILLO
[mailto:mssqldba-ezmlmshield-x50868756.[Email address protected]
Sent: Wednesday, March 28, 2007 8:17 AM
To: LazyDBA Discussion
Subject: Re: Identity_insert

Hi,

I think you must define the field's name after "Insert".
I've had the same problem and after changes, that worked.

SET identity_insert mtest.dbo.svc00300 on
INSERT INTO MTEST.DBO
.SVC00300 (field1,field2...)
SELECT field1, field2
FROM TTC.DBO.SVC00300 T


Antoine Carillo
France
------------ Message original ------------

Norkett Margaret a écrit :

> Hi all - I'm trying to copy data from one table in my production
environment
> to a matching table in a test environment. The table has an identity
column
> and I'm using the following query to copy the data, but keep getting an
> error that I must set identity_insert to on in order to execute the query.
I
> have done this, but must have made some mistake as I still get the error.


> This is a SQL2005 SP2 database. Thanks in advance for your help

>

> [code]

>

> set Identity_insert mtest.dbo.svc00300 on

> INSERT INTO MTEST.DBO.SVC00300 SELECT

> t.equipid, T.SERLNMBR, T.ITEMNMBR,T.REFRENCE, T.VERSION, T.SRLSTAT,
> T.INSTDTE,

> T.CUSTNMBR, T.ADRSCODE, T.ADDRESS1, T.ADDRESS2, T.CITY, T.STATE, T.ZIP,
> T.COUNTRY, T.CNTCPRSN,

> T.LSTPMDTE, LSTSRVDTE, T.TECHID, T.TECHID2, T.OFFID, T.SVCAREA,
T.TIMEZONE,
> T.WARRCDE, T.WARREND,

> T.WARRSTART, T.SLRWARR, T.SLRWEND, T.SLRWSTART, T.SLRWEND, T.SLRWSTART,
> T.NOTEINDX, T.MTTR,

> T.MTBF, T.MTBI, T.LAST_CALC_DATE, T.METERS_1, T.METERS_2, T.METERS_3,
> T.METERS_4, T.METERS_5,

> T.DAILYS_1, T.DAILYS_2, T.DAILYS_3, T.DAILYS_4, T.DAILYS_5, T.MTBFS_1,
> T.MTBFS_2, T.MTBFS_3,

> T.MTBFS_4, T.MTBFS_5, T.METER_DELTAS_1, T.METER_DELTAS_2,
T.METER_DELTAS_3,
> T.METER_DELTAS_4,

> T.METER_DELTAS_5, T.SVC_PM_DATE, T.SVC_PM_DAY, T.VENDORID, T.ADDRESS3,
> T.USERDEF1, T.USERDEF2,

> T.USRDEF03, T.USRDEF04, T.USRDEF05, T.SVC_SERIAL_ID, T.QUANTITY,
> T.SVC_ASSET_TAG, T.SHIPPED_DATE,

> T.SVC_REGISTER_DATE,t.dex_row_id

> FROM TTC.DBO.SVC00300 T

>

> [/code]

>

>

> If you have any questions or concerns, please let me know them.

>

> Margaret Norkett

> Database Administrator

> * 864-271-6522 ext 222

> * 864-270-2884

>



>

>



> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html





MS Sql Server LazyDBA home page