Modify Table To Turn OFF Autoincrement ID
Oct 4, 2013
I've a table with an ID column, it is an auto increment id column that increases by 1.
ID
--
9724
9725
When my webhost restarts SQL, or some other random act of God, this ID jumps by 1000. Today it leapt from 9725 to 10725, which just doesn't work for me. It results in my having to upload the last known good copy of the database.
My host says they can't change the 1000 row buffer, MS apparently doesn't deem this an issue, and I'm quite frustrated. I used to be able to modify certain settings in SQL since I was using an SQL 2008 database under SQL Management Console but since my host upgraded to 2012, and I'm running XP, there is no forward compatibility and many options are no longer possible (eg. modifying a column, now it has to be done via clicking query and entering a SQL command rather than just right clicking on the column)
So long story made short... is there a way to modify my table (named "places") to turn OFF the autoincrement Id and perform a simple means to figure out the next possible value?
example:
id, place, locstion, gps, v2, v3, v4, comment
================================================
9720, Bill's House, Toronto, 43.4453, y, n, c, This was a good place to eat
9721, Dime a Dozen, Cambridge, 42.4453, n, e, d, A good place to take photos
I'd have to pull the highest value of ID (+1 to it) and then do a standard INSERT into places (id, place, location, gps, v2, v3, v4, comment) values (9722, etc...)I guess my concern is if the autoidentity is turned off, will SQL allow a new value to be entered via INSERT? I'm also using the @@identity to find the last known ID when I want to direct the user to the entry that they've just created and I don't know what that # is just yet. Would the @@identity still work?
View 5 Replies
ADVERTISEMENT
Nov 15, 2006
I want to access sqlserver table properties from asp.net .How do i know that a table defined in sqlserver has autoincrement field or not.Actually i have to access all the tables in a database and execute different function for table with auto increment on and off.
I am not being able identify this property from codes.Please help.thank you
View 6 Replies
View Related
Jan 28, 2000
Is there a way to use the SQL alter table command to modifiy the identity
increment?
View 2 Replies
View Related
Jan 3, 2014
How to Increment Data of the Same table with AutoIncrement .i have a table with 4 columns
id type no amount
1 type1 a1 1000
2 type1 a2 2000
3 type2 b1 3000
4 type3 c1 4000
using Loop how can i increment them to hundered rows same data .
id type no amount
1 type1 a1 1000
2 type1 a2 2000
3 type2 b1 3000
4 type3 c1 4000
5 type1 a1 1000
6 type1 a2 2000
7 type2 b1 3000
8 type3 c1 4000
9 type1 a1 1000
10 type1 a2 2000
11 type2 b1 3000
12 type3 c1 4000
View 4 Replies
View Related
Apr 10, 2015
I created am inventory table with few columns say, Servername, version, patching details, etc
I want a tracking of the table.
Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.
View 1 Replies
View Related
Jul 20, 2005
Hello!I have an MS SQL-server with an database, that runs replication. In thisdatabase there is an table with an columni want to extend; varchar(50)->varchar(60).But I get this error (using design window of Enterprise Manager): Cannotdrop the table 'MytableName' because it is being used for replication.Thanks for helpBjoern
View 1 Replies
View Related
May 29, 2008
I want to update my Table but it has 30 fields so how can i modify my table i have to add some fields in it
I need that query for alter table
in it
reply me soon
View 14 Replies
View Related
May 31, 2002
Hello
I have replicated 1 database.Now i need to change the datatype length of one feild from varchar 12 to varchar 20.But when i try to do that it shows error ODBC error !Cannot modify database because it is in replication mode.
Can any body help me to sort out this problem.
Thx
Bilal
View 1 Replies
View Related
Nov 22, 2007
Hi AllAm new to sql server to sobear with me, have checked around but cantfind an answer to this.I want to change fieldname from nvarchar(50) to nvarchar(255) as partof a scriptAm running this command :ALTER TABLE MYTABLE MODIFY fieldname nvarchar(255) nulli test this out in the sql window but cant get it to run error is:Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'MODIFY'.Can anyone help ?
View 1 Replies
View Related
Mar 19, 2008
I have a 3rd party program that creates some tables in my sql server2005 database, then it does inserts on the tables. Is there a way Icould create a system level trigger that would immediately andautomatically modfiy the table structure after the table is created(hopefully before the inserts occur)? I need to change a field fromnumeric(5, 0) to numeric(15, 0).Thanks,Roger
View 3 Replies
View Related
Jan 19, 2006
hi!
i encountered problems when running this code in SQL Query
ALTER TABLE [dbo].[amsSchedule]
MODIFY(CutOff1 datetime NULL,
[FileName] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)
my aim is to modify the two fields to change its data type. BUt when im trying to run this command in the query analyzer, itsays "incorrect syntax error '(' "
What do i have to do? please help me...thanks
View 3 Replies
View Related
Apr 10, 2008
Hi all I was wondering how to do an ALTER command on a Table but without specifying Column Names but rather attempting to overwrite the Table itself with the new fields specified? For instance if I have Table_1 consisting of the following fields:
IDFirstNameSurname
Then use the following ALTER command:
Code Snippet
ALTER Table Table_1
(
ID Int,
FirstName VarChar(50)
)
This would then drop Surname from the Table and leave only ID and FirstName inside it. Is this possible? I have been searching google but can't seem to find what I am looking for.
Thank for comments + suggestions. Regards, Onam.
View 2 Replies
View Related
Mar 2, 2007
Dear all
I have a problem about replication, so I want to modify some columns in a table, but my database using replicate. How to do it?
Please solution.
Thank.
PC Kaveesin.
View 4 Replies
View Related
Jun 9, 2008
Our ERP vendor is helping us migrate from one version of their product to another. In the new version, they have added some columns and changed a few data types in several existing tables.
They sent us a list of the modifications that were necessary, which we did (painstakingly) by hand. But, for other reasons the update failed and we have to start anew.
This got me thinking: is there any way to do this kind of update via a script/stored procedure? I don't have their exact changes handy, but it was stuff like the table "inmast" needs a column of varchar(50) called "lotc" added to it, and the "intime" table needs the datatype of columns "Mon" "Tue" "Wed" changed from text to varchar(50), stuff like that.
Is it possible to do stuff like that in a SP?
View 1 Replies
View Related
Feb 3, 2006
I am quite new to MS SQL, and I want to read rows from a todo table, andwhen a row has been processed, I want to delete that row.Ages ago in MySQL I would probably have locked the table, select, processa row, delete a row, unlock the table.I have been reading through the documentation from MS SQL, but it's notclear what exactly I should do.Since I want to lock only one table in the select (the others just providedata, and are not modified), what's a good solution?--John MexIT: http://johnbokma.com/mexit/personal page: http://johnbokma.com/Experienced programmer available: http://castleamber.com/Happy Customers: http://castleamber.com/testimonials.html
View 2 Replies
View Related
Jul 23, 2007
In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.
In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?
I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.
Please help, it seemed so much easier in 2000!
View 7 Replies
View Related
Mar 20, 2008
What's the answer for this and how ? If it is yes then How ?
View 4 Replies
View Related
Jun 16, 2005
does anyone know how to modify table structure in sql server management console(2005)? thanks
View 1 Replies
View Related
Aug 3, 2006
Hi All,
Is there a way by which we can modify the width of a column of a table which is being replicated without touching the ongoing transactional replication? This is for MSSQL2000 Transactional Replication.
I know (and successfully tried) that we can add a column to a table and that gets propaged to the replicate database and indeed the added column gets reflected there. How to add a column? sp_repaddcolumn or Right Click on the Publication-Properties and it shows a button to Add a Column.
This is what I have tried for modifying the width of a column of a table participating in Transactional Replication from varchar(10) to varchar(100)
MH (source) -> MH1 (Replicate)
The column €ścol1€? had width of varchar(10) and this was altered to varchar(100).
insert into MH..test_mh values(4,'abcdeabcdefff')
select * from MH1..test_mh
exec sp_dropsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2', @destination_db = N'MH1'
go
exec sp_droparticle @publication = N'MH', @article = N'test_mh'
go
alter table test_mh alter column col2 varchar(100) null OR
MH1..sp_help test_mh
exec sp_addarticle @publication = N'MH', @article = N'test_mh', @source_table = N'test_mh'
go
exec sp_addsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2' , @destination_db = N'MH1'
go
Needless to say, help would be apreciated -
~Mihir
View 4 Replies
View Related
Jun 10, 2015
Need a script to capture current date modify table list(12 AM to 11:59 PM PST) in a database.
View 7 Replies
View Related
Sep 20, 2006
hi,
does anybody know how to create an identity column in a query without having to use the into clause?
for example, i want to do the following:
select *,identity(int,0,1) as myId
from myTable
but this is not allowed
thank you
View 10 Replies
View Related
Mar 26, 2007
i have a table for storing username and password.
the following are the fields in table
ID -int
UserName-nvarchar(50)
Password-nvarchar(50)
i dont want to have a separate field for id
i want to autincrement them
plz give me an idea
am using SQLServer Express
am new to this field so plz forgive me for this simple question
thanks
Shruthi
View 8 Replies
View Related
Apr 4, 2006
in mysql the primary key has the possibility tu autoincrement herself
can it be done in MS SQL ??? how
View 9 Replies
View Related
Apr 10, 2008
Hi All I am using SQL server Database in one of my table there is a column which is set to Identity=Yes i.e., The ID is increment by one on every insert and if the insertion failed then the id generated goes off then in the next generation it uses new id ..........EXfirst insertion id=1 then in the second insertion if while adding data to other rows if i get some error then the id 2 is not used and when i correct the error and insert it then id=3? can any one give me the solution for this and NextWhen i delete the datafrom the table see the ids are upto 20 and i delete all the records from the table after insertion of new record the id will be 21plese help me in this
View 3 Replies
View Related
Dec 10, 2004
hi,
Is their anybody knows how to make field in Sql Server autoincrement?
pls. help me.
View 3 Replies
View Related
Jun 24, 2008
Hi All,
This may sound quite easy for you, however I am a newbie in SQL.
So I have an identity field, which I wish to increment automatically every time I do an insert. However the starting integer has to be the MAX value from another table.
So for example I am doing an insert in a #temp table
INSERT INTO #temp(name, surname)
SELECT name, surname from table1
Now the personId of the #temp table has to start from the MAX of table2
Ie SELECT MAX(personId) from table2
The SELECT MAX(personId) from table2 can also be NULL, ie the first time I am inserting, so I also have to cater for this scenario.
can anyone help?
Thanks
Johann
View 12 Replies
View Related
Oct 26, 2005
How can I know that a column is auto incremented or not? What are the commands used to know this??
Thanks guys
View 1 Replies
View Related
Dec 12, 2007
Hi all, i am deleting rows of a table and reinserting new ones. This table has an auto increment primary key. what is the function used to set it to zero each time i delete the rows in order to rebegin counting from 1 when i refill the table. Thanks in advance...
View 7 Replies
View Related
Mar 17, 2008
Hi,
I have an existing table (without PK). I want to output my SELECT statement with an auto-increment on the 1st column like this:
# Lastname Firstname Address
1 Obama Clinton xxxxx
2 Hillary xxxx xxxxx
...
Can someone help me on this pls?
Appreciate it.
Thanks
joseph
View 6 Replies
View Related
Apr 7, 2006
I've been searching for an answer a long time now... but nowhere I found a clear solution...
Is it possible to implement autoincrement with SQLexpress edition?
View 8 Replies
View Related
Jun 17, 2004
I need to get rid of the first char of data in a field f1 (in sql server)
I tried to use:
update myTable
set f1= substring(f1,1,f1.Len -1)
and get error "The column prefix 'f1' does not match with a table name or alias name used in the query.
Could anyone help? Thanks.
View 2 Replies
View Related
Dec 25, 2006
I created a new table in sql server 2005. Primary key is autoincrement value of 1. But when I insert the row, it increments by 4. It goes like 1, 5, 9 etc..what could be the problem ?how can I correct it? Do I need to reseed it?
Thanks!!
View 14 Replies
View Related
Jul 3, 2000
looking for necessary syntax to alter table id to autoincrement adding identity statement, not sure on syntax for seed an increment, or if it is possible at all.
View 2 Replies
View Related