copy data - 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

copy data

Author

Topic

ikeguluo
Starting Member

15 Posts

Posted - 2008-10-27 : 08:34:56

Hi

I am trying to copy data from one table to the other and getting the error

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'dbo.tblStaffDirectory_Area' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Can someone please help with this

Regards

Obi

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-27 : 08:45:17

Use export/Import wizard and enable Identity insert on

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-10-27 : 09:09:00

quote:
Originally posted by sodeep

Use export/Import wizard and enable Identity insert on

No offence but I don't like this suggestion. Enabeling identity_insert is very simple:



SET IDENTITY_INSERT mytablename ON

INSERT INTO mytablename (IdentityColumn, SomeOtherColumns, ...)
SELECT ID, ...
FROM table
WHERE Something > 54

SET IDENTITY_INSERT mytablename OFF

- Lumbago

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-27 : 09:36:19

Lumbago,

Try it yourself with million records and see which is faster.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-10-27 : 09:54:40

Well...I'm sure it's alot faster as it's using a totally different method, but my point was to inform the OP about identity_insert. It's impossible to say which is the preferred method from the information provided...

EDIT: my initial thought was that he was doing this using sql code, but there is now way of telling so when it comes down to it your suggestion is as good as mine

- Lumbago

ikeguluo
Starting Member

15 Posts

Posted - 2008-10-27 : 11:34:25

thanks both of you for you quick response, I did it using the export wizard and im now getting the error
Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)

Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_tblStaffDirectory_Area'. Cannot insert duplicate key in object 'dbo.tblStaffDirectory_Area'.".
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)
I think its complaing about the primary key im tring to insert.

Do I just remove the primary key

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-10-28 : 03:54:17

quote:
Do I just remove the primary key

Well...this depends on your business needs. The primary key is probably there for a reason so removing it might cause some unexpected results. I'd suggest you either remove the conflicting data from your source/make sure it doesn't get imported (you said that this was a table so you could just make sure that the conflicting data is not a part of the select from the source table) or you could insert all the data in to a staging table and filter out any conflicting data when inserting in to the "real" table from there.

- Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources