473,551 Members | 2,803 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Indentity Insert

I'm doing a data transfer from Access to SQL Server, I wish to keep the
identity column (autonumber) values as all the data is already related. I
tried the first table append query including the identity column, it worked.
Was this fluke? Will it always work? I was under the impression that I would
have to issue a "set identity_insert on" before doing this. The SQL database
will have absolutely no data before the transfer routines are run.
Jul 20 '05 #1
15 11397
As long as there arent any duplicated values in what you are trying to
insert into the identity column you should have no problems.

You could also use Access' upsizing wizard to move the tables to SQL
server. This feature is available in A2k, I'm not sure about versions
below that.

http://www.cooldigitec.co.uk/njw/15-...SQLServer7.asp

Is an account of my upsizing experiences.

HTH
--

Nath
www.cooldigitec.co.uk
(Email mangling obvious)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #2
"Nath Wilson" <ch*******@a0l. com> wrote in message
news:3f******** *************** @news.frii.net. ..
As long as there arent any duplicated values in what you are trying to
insert into the identity column you should have no problems.
OK Thanks.
You could also use Access' upsizing wizard to move the tables to SQL
server. This feature is available in A2k, I'm not sure about versions
below that.


It's an app rewrite, the tables are already there and slightly different.
Jul 20 '05 #3
I'd hope that "so*****@micros oft.com" should know their own software.

Pretty much real Pavel

For example John Smith wrote:

I'm doing a data transfer from Access to SQL Server, I wish to keep the
identity column (autonumber) values as all the data is already related. I
tried the first table append query including the identity column, it worked.
Was this fluke? Will it always work? I was under the impression that I would
have to issue a "set identity_insert on" before doing this. The SQL database
will have absolutely no data before the transfer routines are run.

Jul 20 '05 #4

No, you can't rely on the Identity field keeping the same number as
the autonumber field.
On Fri, 29 Aug 2003 12:25:28 +0100, "For example John Smith"
<so*****@micros oft.com> wrote:
I'm doing a data transfer from Access to SQL Server, I wish to keep the
identity column (autonumber) values as all the data is already related. I
tried the first table append query including the identity column, it worked.
Was this fluke? Will it always work? I was under the impression that I would
have to issue a "set identity_insert on" before doing this. The SQL database
will have absolutely no data before the transfer routines are run.

--
You're Not Losing More Hair, You're Gaining More Scalp.

Jul 20 '05 #5
"Pavel Romashkin" <pa************ *@hotmail.com> wrote in message
news:3F******** *******@hotmail .com...
I'd hope that "so*****@micros oft.com" should know their own software.
It's a big company.
Pretty much real Pavel


A real what?
Jul 20 '05 #6
An update on this, it's now failing with the message:

Error 544 - [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert
explicit value for identity column in table 'zstblApplicati onFunction' when
IDENTITY_INSERT is set to OFF. (ODBC.QueryDef) Error 3146 - ODBC--call
failed. (DAO.QueryDef)

In Profiler, the SQL trace shows:

<---------------------------------------------------
SET IDENTITY_INSERT "dbo"."zstblApp licationFunctio n" ON
go
exec sp_executesql N'INSERT INTO "dbo"."zstblApp licationFunctio n"
("ApplicationFu nctionID","Sort Order","ParentA pplicationFunct ionID","Applica t
ionFunctionName ","DisplayName" ,"Hyperlink","F ormHelpID","IsM enuItem","IsSub M
enuItem","IsEnf orced","SysIsIn sert","SysIsSel ect","SysIsUpda te","SysIsDelet e
","SysIsExecute ") VALUES
(@P1,@P2,@P3,@P 4,@P5,@P6,@P7,@ P8,@P9,@P10,@P1 1,@P12,@P13,@P1 4,@P15)', N'@P1
int,@P2 int,@P3 int,@P4 varchar(50),@P5 varchar(50),@P6 varchar(8000),@ P7
int,@P8 bit,@P9 bit,@P10 bit,@P11 bit,@P12 bit,@P13 bit,@P14 bit,@P15 bit',
1, 3, NULL, 'SECURITYMENU', 'Security', NULL, NULL, 1, 0, 0, 0, 1, 0, 0, 0
go
IF @@TRANCOUNT > 0 ROLLBACK TRAN
go
---------------------------------------------------->

If I copy and paste that into Query Analyser it inserts the record. When
Access issues these commands to the server it fails. Odd.
Jul 20 '05 #7
Hi

The problems is the GO after SET IDENTITY_INSERT .. ON

I suggest you do this in a stored procedure.

John

"For example John Smith" <so*****@micros oft.com> wrote in message
news:3f******** *************** @news.easynet.c o.uk...
An update on this, it's now failing with the message:

Error 544 - [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert
explicit value for identity column in table 'zstblApplicati onFunction' when IDENTITY_INSERT is set to OFF. (ODBC.QueryDef) Error 3146 - ODBC--call
failed. (DAO.QueryDef)

In Profiler, the SQL trace shows:

<---------------------------------------------------
SET IDENTITY_INSERT "dbo"."zstblApp licationFunctio n" ON
go
exec sp_executesql N'INSERT INTO "dbo"."zstblApp licationFunctio n"
("ApplicationFu nctionID","Sort Order","ParentA pplicationFunct ionID","Applica t ionFunctionName ","DisplayName" ,"Hyperlink","F ormHelpID","IsM enuItem","IsSub M enuItem","IsEnf orced","SysIsIn sert","SysIsSel ect","SysIsUpda te","SysIsDelet e ","SysIsExecute ") VALUES
(@P1,@P2,@P3,@P 4,@P5,@P6,@P7,@ P8,@P9,@P10,@P1 1,@P12,@P13,@P1 4,@P15)', N'@P1 int,@P2 int,@P3 int,@P4 varchar(50),@P5 varchar(50),@P6 varchar(8000),@ P7
int,@P8 bit,@P9 bit,@P10 bit,@P11 bit,@P12 bit,@P13 bit,@P14 bit,@P15 bit', 1, 3, NULL, 'SECURITYMENU', 'Security', NULL, NULL, 1, 0, 0, 0, 1, 0, 0, 0
go
IF @@TRANCOUNT > 0 ROLLBACK TRAN
go
---------------------------------------------------->

If I copy and paste that into Query Analyser it inserts the record. When
Access issues these commands to the server it fails. Odd.

Jul 20 '05 #8
"John Bell" <jb************ @hotmail.com> wrote in message
news:3f******** *************@r eading.news.pip ex.net...
Hi

The problems is the GO after SET IDENTITY_INSERT .. ON

I suggest you do this in a stored procedure.


I got another table with an Identity column and this works fine. The GO
doesn't seem to be a problem there (I think they're just put in by Profiler
anyway for the purpose of pasting into QA) and it works OK from QA with the
GOs in place..

A Stored procedure would be difficult as the data is coming from an Access
database although in the case of this particular table it comes from another
SQL database but I did try importing that one to Access and then running the
append query from the Access database. I can look at doing a SP for the ones
that do come from another SQL database (these are new ones added to the
application recently in the re-write) but since I'm on like table 3 out of
60 I'm not that confident the rest will go as smoothly as the first 2.

I'm pasting the troublesome table script below, this may provide a clue,
could be the self join FK that doesn't exist on any table I've transferred
thus far.

--- table script
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[zstblApplicatio nFunction_zstbl ApplicationFunc tion_FK1]')
and OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[zstblApplicatio nFunction] DROP CONSTRAINT
zstblApplicatio nFunction_zstbl ApplicationFunc tion_FK1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[zstblApplicatio nFunction_zstbl Permission_FK1]') and
OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[zstblPermission] DROP CONSTRAINT
zstblApplicatio nFunction_zstbl Permission_FK1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[zstblApplicatio nFunction]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[zstblApplicatio nFunction]
GO

CREATE TABLE [dbo].[zstblApplicatio nFunction] (
[ApplicationFunc tionID] [int] IDENTITY (1, 1) NOT NULL ,
[SortOrder] [int] NULL ,
[ParentApplicati onFunctionID] [int] NULL ,
[ApplicationFunc tionName] [varchar] (50) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[DisplayName] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL
,
[Hyperlink] [varchar] (512) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[FormHelpID] [int] NULL ,
[IsMenuItem] [bit] NOT NULL ,
[IsSubMenuItem] [bit] NULL ,
[IsEnforced] [bit] NOT NULL ,
[SysIsInsert] [bit] NOT NULL ,
[SysIsSelect] [bit] NOT NULL ,
[SysIsUpdate] [bit] NOT NULL ,
[SysIsDelete] [bit] NOT NULL ,
[SysIsExecute] [bit] NOT NULL ,
[SysTimeStamp] [timestamp] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[zstblApplicatio nFunction] WITH NOCHECK ADD
CONSTRAINT [zstblApplicatio nFunction_PK] PRIMARY KEY CLUSTERED
(
[ApplicationFunc tionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[zstblApplicatio nFunction] ADD
CONSTRAINT [PrimaryKey_UC1] UNIQUE NONCLUSTERED
(
[ApplicationFunc tionName]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IDX_ParentAppli cationFunctionI D] ON
[dbo].[zstblApplicatio nFunction]([ParentApplicati onFunctionID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [zstblApplicatio nFunction_AK2] ON
[dbo].[zstblApplicatio nFunction]([DisplayName]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

ALTER TABLE [dbo].[zstblApplicatio nFunction] ADD
CONSTRAINT [zstblApplicatio nFunction_zstbl ApplicationFunc tion_FK1] FOREIGN
KEY
(
[ParentApplicati onFunctionID]
) REFERENCES [dbo].[zstblApplicatio nFunction] (
[ApplicationFunc tionID]
),
CONSTRAINT [zstblFormHelp_z stblApplication Function_FK1] FOREIGN KEY
(
[FormHelpID]
) REFERENCES [dbo].[zstblFormHelp] (
[FormHelpID]
)
GO

Jul 20 '05 #9
For example John Smith (so*****@micros oft.com) writes:
An update on this, it's now failing with the message:

Error 544 - [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert
explicit value for identity column in table 'zstblApplicati onFunction'
when IDENTITY_INSERT is set to OFF. (ODBC.QueryDef) Error 3146 -
ODBC--call failed. (DAO.QueryDef)

In Profiler, the SQL trace shows:


Hm, this is certainly a desprate idea, but check that you have the
same value for SPID for the commands.

Or, um, wait there is one more possibility. Under which user do you
run this from Access? And from Query Analyzer? SET IDENTITY_INSERT
is not permitted for plain users. Of course you should get an error
message for that too, and maybe you are if you sift through the
error messages.

And, oh, it would be appreciated if you fixed your name and e-mail
address.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1776
by: Flare | last post by:
Hi, When i eg. manually ad entries to a table and, cancels the insert Ms SQL increment the counter on the ID anyway. Is there a way to avoid this behavior? Regards Anders
2
2087
by: Jashan | last post by:
Hi All I have a table in SQL Server with ID having indentity inrement by one. Table has not any trigger. Frequently ID in the table jumps. Any help !!! Thanks
3
2504
by: Howard Hinnant | last post by:
I recently asked for a survey of multimap insert with hint behavior, in support of a paper I'm writing concerning lwg issue 233. My sincere thanks to Beman Dawes, Raoul Gough, Russell Hind, Bronek Kozicki, Nicola Musatti, John Potter and Maxim Yegorushkin for helping with that survey. Since I started work on this paper at least two people I...
6
6987
by: Mark P | last post by:
Some time ago I posted here about inserting into a set with a hint: http://groups-beta.google.com/group/alt.comp.lang.learn.c-c++/browse_thread/thread/fb75b00f73e979db/018b8d0eadb38dbf?q=%22STL+insert+with+hint%22+%22Mark+P%22&rnum=1&hl=en#018b8d0eadb38dbf I quoted the SGI STL docs describing a.insert(p, t), where p is the hint iterator and...
14
4273
by: serge | last post by:
I have a scenario where two tables are in a One-to-Many relationship and I need to move the data from the Many table to the One table so that it becomes a One-to-One relationship. I need to salvage the records from the many table and without going into detail, one of the reasons I can't do the opposite as there are records in the ONE table...
11
9644
by: For example John Smith | last post by:
I'm doing a data transfer from Access to SQL Server, I wish to keep the identity column (autonumber) values as all the data is already related. I tried the first table append query including the identity column, it worked. Was this fluke? Will it always work? I was under the impression that I would have to issue a "set identity_insert on"...
4
5467
by: Chris Kratz | last post by:
Hello all, We have run into what appears to be a problem with rules and subselects in postgres 7.4.1. We have boiled it down to the following test case. If anyone has any thoughts as to why this would be happening, we would appreciate feedback. We have tested on 7.3.4, 7.3.6 and 7.4.1 and all exhibit the same behavior. Test case one...
2
3176
by: Geoffrey KRETZ | last post by:
Hello, I'm wondering if the following behaviour is the correct one for PostGreSQL (7.4 on UNIX). I've a table temp_tab with 5 fields (f1,f2,f3,...),and I'm a launching the following request : INSERT INTO temp_tab VALUES (1,2,3)
3
2302
by: MP | last post by:
Hi Posted this several hours ago to another ng but it never showed up thought i'd try here. using vb6, ado, .mdb, jet4.0, no access given table tblJob with field JobNumber text(10) 'The example I had to go by 'INSERT INTO tblCustomers (CustomerID, , )
0
7565
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7492
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7847
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6106
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5406
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5130
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3520
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1097
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
804
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.