In my eternal saga to insert 1.4 million rows of data from a SQL script, I've written a basic WinForms app that takes each line of the script and executes it individually. However, because the original script contained SET IDENTITY_INSERT [Table] OFF and SET is a session-wide command, this setting is getting lost on every SQL call, m...
I'm using Linq to SQL. I have a DataContext against which I am .SubmitChanges()'ing. There is an error inserting the identity field: Cannot insert explicit value for identity column in table 'Rigs' when IDENTITY_INSERT is set to OFF. The only identity field is "ID", which has a value of 0. It's defined in the DBML as: [Column(Storage=...
hi there got an issue with identity columns in Sql Server CE when using Server explorer, in VS2008, executing the following script SET IDENTITY_INSERT testTable ON; Insert into testTable (id,name) values (1,'Something') SET IDENTITY_INSERT testTable ON; sends the follow message error 'The Set SQL construct or statement is not supporte...
I'm a little rusty with my triggers and what not and am trying to figure out this problem for a class: In a database TEST, tables do not have the option of the IDENTITY feature. In other words, when we insert a row into the table “Users”, we would like the primary key “UserID” to auto-increment. Please suggest a workaround to implement ...
I need to copy data into an MSSQLServer 2005 database table which has an identity column. I've seen how to disable the identity column by executing SET IDENTITY_INSERT <table> ON before the insert queries. How can I do this when I'm using PreparedStatements to do batch inserts and I can't change the statement during the operation? ...
Hi, I have several tables within my database that contains nothing but "metadata". For example we have different grouptypes, contentItemTypes, languages, ect. the problem is, if you use automatic numbering then it is possible that you create gaps. The id's are used within our code so, the number is very important. Now I wonder if it i...
hi, i used 'SET IDENTITY_INSERT OFF' and this is executing successfully but in the table still identity on only please help any one ...
We have a fairly robust system using NHibernate, we're in the middle of migrating from a single database server to having two servers, one for administration and one for our public facing web site. This is mainly so that we can do things like content management with workflow that won’t affect the current site. As of today, they are tw...
I'm trying to grab thousands of invoices (and other stuff) from a text file and insert them into SQL Server 2008. I wrote a little console app to do this and it uses LINQ to SQL. After I insert all the existing invoices I want the Invoice_ID to be an identity column and auto-increment, so I have it designed as such: CREATE TABLE [dbo]...
Hello, I have to migrate this query (simplified here) from T-SQL to ORACLE SET IDENTITY_INSERT table ON INSERT INTO table (id, value) VALUES (1, 2) SET IDENTITY_INSERT table OFF id being an Identity field in SQLServer. I have the same table with a sequence in ORACLE, I couldn't find a snippet that shows how to disable the sequence ...
I am trying to set some id's for a bunch of rows in a database where the id column is an identity. I've created a cursor to loop through the rows and update the ids with incrementing negative numbers (-1,-2,-3 etc). When I updated just one row turning on the IDENTITY_INSERT it worked fine but as soon as I try and use it in a cursor, it...
Hello, I recently migrated from a PostgreSQL database to a SQL Server database. To switch the data over I had to enable IDENTITY_INSERT. Well come to find out that I get all sorts of strange errors due to duplicate identity values(which are set as primary keys) upon doing an insert in any of the tables. I have quite a few tables. What ...
I am trying to create a record in the db that has a predefined primary key value. I know how to do this with sql, but I was wondering if EF can do this for me? Otherwise, I will have to create a stored proc for the inserts. ...
With following code,I have identity_insert turned on to be able to insert some value explicitly: SET IDENTITY_INSERT dbo.myTable ON Go the identity insert is set to be primary key and not null in my c# program,when i want to insert a value,this error is generated: IDENTITY_INSERT is turned off but i have turned it on what's the pr...
What I need to do is have a SET IDENTITY_INSERT dbo.myTable ON statement be part of my C# app, but I don't know how to do this. What's the syntax of using the above statement in a c# app? ...
i have a front end in access and back end is sql server 2008 one of the fields is the account number and here are the rules it is a zipcode like 92111 plus a dash plus a number. so the first one would be 92111-1, the second 92111-2 this has to do with how many clients we have in the zip code i would like this zip code to be automati...
For a sql script I'm working on, I need to programmatically remove the identity, identity seed, and identity increment for a column in an existing table, then add them back to the table at the end of the script. Does anyone have a reference or an example on how to do this? ...