[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

From MS Access 2003 table to "identical" MS SQL Server 2005 table


Hello there!

When I try

SET IDENTITY_INSERT Stocks ON
INSERT INTO Stocks
SELECT Access.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\PathToDatabase\DatabaseName.mdb';'admin';'',Stocks) As Access

I get the following error:

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

Please explain the error and correct what is wrong.

Note: there are 2 identical Primary Key´s at Access and SQL Server, both called StockID.

Thanks,
fskilnik.


0
fskilnik
Asked:
fskilnik
1 Solution
 
Brian CroweCommented:
you need to specifically name your columns

SET IDENTITY_INSERT Stocks ON
INSERT INTO Stocks (StockID, columnA, columnB, ...)
SELECT StockID, columnA, columnB, ...
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\PathToDatabase\DatabaseName.mdb';'admin';'',Stocks) As Access
0
 
fskilnikAuthor Commented:

Perfect, BriCrowe. Thanks for the quick and explicit reply.

Please have a look at my other (I guess harder) question, if you don´t mind!
Here: http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21902340.html

Thanks a lot,
fskilnik

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now