473,883 Members | 1,576 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
15 11416
Hi

Sorry, this is not transaction specific, but you should still put this in a
SP to solve the problems!

John

"For example John Smith" <so*****@micros oft.com> wrote in message
news:3f******** *************** @news.easynet.c o.uk...
"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 #11
On Mon, 1 Sep 2003 20:11:40 +0000 (UTC) in comp.databases. ms-access,
Erland Sommarskog <so****@algonet .se> wrote:
And, oh, it would be appreciated if you fixed your name and e-mail
address.


I bet it's not the first time someone followed the instructions in
Outlook Express to the letter <g>

--
A)bort, R)etry, I)nfluence with large hammer.

(replace sithlord with trevor for email)
Jul 20 '05 #12
"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...
Hm, this is certainly a desprate idea, but check that you have the
same value for SPID for the commands.
Bang! Hit the nail squarely on the head, thanks for that pointer. It appears
that Access does issue a SET IDENTITY_INSERT ON command prior to the insert
but doesn't turn it off again afterward. My own passthrough query to turn it
off is running under a different SPID.

So it appears to be an Access bug.
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.
sa. I did find an error message to that effect, level 16 but it appears
Access ignores anything lower than 18.
And, oh, it would be appreciated if you fixed your name and e-mail
address.


I don't wish to dwell on this but...
The fake email is clear, I don't want spam nor do I want to enter into email
correspondance, I've answered as many if not more questions in these groups
than I've asked and have fallen into the trap of taking problems into email
and then the other party expecting me to be their personal programming slave
or I have to ignore or tell the person outright that they're overstepping
the bounds of my generosity, either way it can end in tears (for them, not
me, you see it's for eveyone else's protection <g>). As for the name, one
could use an obvious fake, a fake that looks real or a real name (or a real
one that looks like an obvious fake), you still wouldn't know me from Adam.

Some people post anonymously because they know they are acting the fool and
even use remaillers to hide all trace (I don't BTW), others perhaps,
hypothetically (nudge wink) using a company account don't want bosses or
clients to see them asking questions on a public forum.

Jul 20 '05 #13
For example John Smith (so*****@micros oft.com) writes:
I don't wish to dwell on this but...
The fake email is clear, I don't want spam nor do I want to enter into
email correspondance, I've answered as many if not more questions in
these groups than I've asked and have fallen into the trap of taking
problems into email and then the other party expecting me to be their
personal programming slave or I have to ignore or tell the person
outright that they're overstepping the bounds of my generosity, either
way it can end in tears (for them, not me, you see it's for eveyone
else's protection <g>). As for the name, one could use an obvious fake,
a fake that looks real or a real name (or a real one that looks like an
obvious fake), you still wouldn't know me from Adam.


I also answer lot of questions, and sometimes get them in the mail
too, but I have never found it to be a problem with people asking for
too much.

A lot of people these days uses fake names and addresses, which in my
opinion is a pity, because being a long-timer in news I recall the days,
when replying by mail was the natural thing when the discussion was not
really of general interest. This is why I insist on using my real name and
mail address in public.

But even if you use a fake address, there are fake address which are less
appropriate than others. Unless you are a Microsoft emplyoess, I don't
you should use an fake address their domain.
--
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 #14
On Tue, 2 Sep 2003 20:44:17 +0000 (UTC), Erland Sommarskog
<so****@algonet .se> wrote:
But even if you use a fake address, there are fake address which are less
appropriate than others. Unless you are a Microsoft emplyoess, I don't
you should use an fake address their domain.


Or at least, add ".invalid" to the end of the email address, which is
the (RFC) accepted method for saying "this email address is fake".
--
You Will Become Rich And Famous Unless You Don't.

Jul 20 '05 #15
"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...
But even if you use a fake address, there are fake address which are less
appropriate than others. Unless you are a Microsoft emplyoess, I don't
you should use an fake address their domain.


Noted.
Jul 20 '05 #16

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

Similar topics

1
1790
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
2095
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
2530
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 respect very much have expressed interest in nailing down the "insert without hint" function a...
6
7031
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 t is the inserted object: "Insert with hint is logarithmic in general, but it is amortized...
14
4309
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 that I need to keep even if they don't have any child records in the MANY table. Below I created...
11
9666
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" before doing this. The SQL database will have absolutely no data before the transfer routines are run.
4
5490
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 tries to populate table2 from table1 with records that are not in table2 already. Table2 gets...
2
3214
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
2315
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
9777
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11109
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10405
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7959
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7114
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 into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5782
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5980
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4602
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4200
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.