Adding An Auto-increment Column To Existing Table With A Particular Order
Oct 19, 2005
Hello all,
I'm using SS2K on W2k.
I'v got a table say, humm, "Orders" with two fields in the PK:
OrderDate and CustomerID. I would like to add an "ID" column which
would be auto-increment (and would be the new PK). But, I would really
like to have orders with the oldest OrderDate having the smallest ID
number and, for a same OrderDate, I'd to have the smallest CustomerID
first. So my question is:
How could I add an auto-increment column to a table and make it create
its values in a particular order (sort by OrderDate then CustomerID
here)?
In the real situation, the table I want to modify has around 500k
records and the PK has 5 fields and I want to sort on three of them.
Thanks for you help
Yannick
View 7 Replies
ADVERTISEMENT
Mar 7, 2011
Due to localization I have the need to make child tables, where there is a composite Primary Key, between the Id column and the LanguageSign column. On the parent table the Id column is Identity column with auto increment.
The problem is that during the select into query to copy columns from parent to child, this auto increment behaviour of the parent-Id is copied to the child-Id. However I do not want that, because the same Id will be used by different LanguageSign entries
Is there a way to use 'select into' without copying the auto increment, or is my only option to make a whole new column without auto increment on the child and copy the records?
btw I have used this statement
SET
IDENTITY_INSERT MyTable
ON , so that inserting into the Id column is possible. I can see however that this does not take away the auto increment...
View 4 Replies
View Related
Jul 26, 2007
Hi guys,I followed the ASP.net official tutorial to create a DAL & Business Logic Layer (http://www.asp.net/learn/dataaccess/tutorial02cs.aspx). I have a table with a int ID field. I wish to write a function to add a new entry into this table but have the ID field auto-increment.The ID field is set as the Identity Column and has a Identity Increment & Seed of "1". If I manually go to the table and insert a new record leaving the ID value null it automatically increments. But if I create a C# function to add a new entry I get an error saying that the ID field can't be Null. Is there any way to use the Update method as shown on line 14 below to add a new entry but with it automatically incrementing? I did create a function called InsertDevice that simply inserts the other fields using a SQL INSERT and it auto-increments fine, just wondering if there is a way to do it using the DataTable and the Update method? Thanks for any help!!! 1 public bool AddDevice(string make, string model)
2 {
3 //cannot have the same device entered twice!
4 if (Adapter.FillDeviceCountByMakeModel(make, model) == 1)
5 return false;
6
7 RepositoryDataSet.DevicesDataTable devices = new RepositoryDataSet.DevicesDataTable();
8 RepositoryDataSet.DevicesRow device = devices.NewDevicesRow();
9
10 device.make = make;
11 device.model = model;
12
13 devices.AddDevicesRow(device); << Error thrown Here!
14 int rows_affected = Adapter.Update(devices);
15
16 return rows_affected == 1;
17 }
View 3 Replies
View Related
Dec 5, 2005
Sorry I'm pretty new to SQL so I don't know if this is a simple question. I have a table, and I am trying to add a column to the table and populate this column using what would be called an 'IF' function in Excel.
Basically 'column A' has numbers in it. I want SQL to look at 'column A' and if the first 5 digits of the number in 'column A' are 00001, then put 'description A' into new column 'column B'. If the first 5 digits of the number in 'column A' are 00002, then put 'description B' into 'column A' etc.
Any ideas?
View 2 Replies
View Related
Jan 21, 2014
I want to add new primary key into existing table which already has a primary key. But,I do not want to remove the old primary key, since there are many records and the old primary key also have relationship with other table
When I am using this query:
alter table hem154
add indexNO uniqueidentifier default newid()
alter table hem154
add CONSTRAINT pk_hem154_indexNo PRIMARY KEY (PK_indexNO)
go
Note:
Hem154 ~ Table name
indexNo ~ Column Name
I get this runtime error:
Msg 1779, Level 16, State 0, Line 1
Table 'hem154' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
View 4 Replies
View Related
Sep 11, 2006
I removed all constraints in order to load a bunch of data into a table, now I'm wondering if I can add an identity column to this table which does contain data or if I have to create a new table with the identity column and insert the data into that.
thx
Kat
View 8 Replies
View Related
Feb 20, 2008
Dear Friends,
I need a SQL Query to add a identity coloumn for anExisting table. (ie) when i try to alter the table i want to add an identity coloumn.
Thanks in advance.
View 8 Replies
View Related
Feb 28, 2007
After i run the sql which adds some columns on one particular table.I am getting this Warning
Warning: The table 'usac499_499A' has been created but its maximum row size (9033) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
I got a series of the above warning message , but the coulmn wa created.
View 10 Replies
View Related
Mar 30, 2004
I simply need the ability using SQL to add columns in an existing table before (or after) columns that already exist.
The MS SQL implementation of ALTER TABLE doesn't seem to provide the before or after placement criteria I require. How is this done in MS SQL using SQL or is there a stored procedure I can use?
Thanks.
View 5 Replies
View Related
Feb 9, 2015
I have a scenario where I need to add a blank column to a table that is a publisher. This table contains over 100 million records. What is the best way to add the column? In the past where I had to make an update, it breaks replication because the update would take forever as jobs are continuously updating the table so replication can't catch up.
If I alter a table and add a column, would this column automatically get picked up in replication?
View 0 Replies
View Related
Jul 23, 2015
In y sql server table has millions of records available. I don't want to drop the tables.
My requirement is I want to change the column order of an existing table. some tables I am able to saving on design window Like Below image.
Even I had generate a script and using that script trying to execute on Management Studio but unable to saving the new column orders. I am getting the Timeout expired error after couple of minutes. How can we save the orders without dropping the table !
View 13 Replies
View Related
May 2, 2008
How can I put an Auto increment on a non PK field?
for ordering contents items?
View 9 Replies
View Related
Oct 16, 2014
I have a column that is not set to auto increment "IDX and Im inserting 800 part numbers but i want the IDX column to start at IDX 400 and increment 1 time per part number that is inserted. how can i accomplished that task.
EXAMPLE:
IDX PART#
400 abcde
401 fghi
402 jklm
etc. and so forth until the last part# will have IDX 1200...
View 1 Replies
View Related
Aug 22, 2006
Hi there,
With a Union all Query is there a way to have it also generate
an Auto Increment (number) column that is appended to the Union all results
?
View 2 Replies
View Related
Feb 8, 2008
i've posted in the wrong forum, so im posting here
hi, im having problems to import data from my excel to a sql table.
in the excel file i have exact the same fields that i have in the table excepts the primary key which is an auto increment. When i try to import data, an error that i can't insert nulls into my auto increment column.
I put enable identity insert in the edit options, but still doesnt work.
can anyone help?
thanks in advance
View 1 Replies
View Related
Aug 29, 2007
I have a column with consist of customer number.e.g.
c001
c002
c003
How do I add the character "c" to the auto-incremental number everything I add?
View 7 Replies
View Related
Oct 27, 2006
trying to change a column that is just a INT NOT NULL column, to have an auto-increment.
Code:
ALTER TABLE [ImpExpTables].[dbo].[ImpExp_eBayLocalNeedsDeleted]
ALTER COLUMN ID int IDENTITY(1,1) NOT NULL
just gives error at IDENTITY
View 7 Replies
View Related
Mar 15, 2007
Hi members,
I am facing a grange error, I hv an asp .net application running with sql server 2000 on a web server.
one table say tblProducts, I have few colums in it with an ID Primery Key field which is auto increment.
I have around 1500 records in it, what i am seeing is that autonumbers are not comming in sequence. it is skiping few numbers after rendom intervals.
like
1
2
3
4
5
7
9
12
13
14
like this there is no error in the code as its only a simple insert query. can anybody tell me what could be the reason.
regards
Aloha
View 3 Replies
View Related
Jul 20, 2004
Hi,
How to Change Increment Value for existing Identity Column (MS SQL2000) ?
I know how to change the seed :
DBCC CHECKIDENT (activity, RESEED,4233596)
but I need the future id generated with step 2
4233596
4233598
4233600
I would like to do it using T-sql because I will need to do it every day after syncronising with another SQL server .
Thanks,
Natalia
View 1 Replies
View Related
Jun 27, 2007
I currently working asp.net with c# and inserting data from webpage into sqlserver 2000 data table. I have an auto increment ID in a table. Let's say 10 are inserted. Then you delete those 10. You add another and the auto increment sets the id at 11. How can I get that table to start back to 0? Plz help me and give code for increment id programmatically using c#. Thanks in Adv,
View 2 Replies
View Related
Dec 28, 2007
i would to make a column contains of 3 characters and 6 auto increment numbers (example: "DLL - 123456")
and made it primary key and which data type i should use. i do not know whether i use after insert trigger in two columns one for three characters and another for code which has identity property >>>so please help me
View 4 Replies
View Related
Aug 16, 2005
What is the best way to duplicate table row with auto increment?
Is there a way to do this without selecting the data and reinserting it?
View 2 Replies
View Related
Oct 20, 2004
I have a table with two primary key fileds. I will be running a process to populate the data into the table.For each process the first field is constant and second field is auto increment one.
My statements are insert into select ** from ** type one.
How do I auto increment second filed per run. Any delivered function available or any suggestion Pl.................
View 4 Replies
View Related
Jun 17, 2008
Asalam o alykum!!!
i just want to know tht i have table which have large record almost 7 lakh round about , when i set auto increment on it gives me timeout error, is der any easy way to ON auto increment ??
Thank you
View 7 Replies
View Related
Dec 11, 2007
Hello,
I Have a table that needs to have 2 unique number.
detail_id and detail_print_id.
detail_id is already an IDENTITY.
both fields need to be different, because when importing, it imports the same data into a table twice, with only a slight data change (and id is not one of the changes).
So I thought i could do the following:
detail_id INT NOT NULL IDENTITY(1,2),
detail_print_id INT NOT NULL IDENTITY(2,2),
--blah blah
that way, the detail_id will always be odd, and the detail_print_id will always be even. however SQL Server 2005 only allows 1 identity per table, and both these fields need to be auto generated when the field is inserted, so as to prevent double data.
is there anyway I can create a int column to auto increment, without the column being an IDENTITY??
also, I would prefer to not have to create a second table with a single column just for this work.
Thanks,
Justin
View 5 Replies
View Related
Mar 9, 2000
I cannot figure out how to add a default constraint to an existing column. The syntax I'm using is :
ALTER TABLE table_name WITH NOCHECK
ADD CONSTRAINT column_name DEFAULT (0)
This gives me a syntax error.
The column was originally added with a default constraint of 1 to a 2.6 million row table.
I dropped the existing constraint and need to add the new default constraint of 0 for that column.
Anyone have any ideas? Thanks in advance.
View 2 Replies
View Related
Mar 13, 2008
Is it possible to add new data to an existing report. I already updated the SQL query, but the new data does not appear within the report. How can I modify the rows, columns and data fields???
Thanks in advance!
View 5 Replies
View Related
Jun 25, 2014
I need to have a script where it ask the user for a value, the script will search for all records that match the value. Then it will display the numbers of records found and ask the user to enter a different value. The rest of the script will use this new value and increment by 1 n times as the number of records found. I started the script where it will ask for "HANDLE" and display the number of records found with that "HANDLE"
declare @HANDLE as varchar(30)
declare @COUNT as varchar(10)
declare @STARTINV as varchar(20)
set @HANDLE = ?C --This is the parameter to search for records with this value
set @STARTINV = ?C --User will input the starting invoice number
SELECT COUNT as OrderCount FROM SHIPHIST
where HANDLE = @HANDLE
I just can't figure out how to proceed to use the entered invoice # and increment by 1 until it reach the number of records found.
This will be the end results:
Count=5 --results from query
STARTINV=00010 --Value entered by user
Handle,Inv_Num
AAABBB,00010
AAABBB,00011
AAABBB,00012
AAABBB,00013
AAABBB,00014
View 9 Replies
View Related
Jan 23, 2004
I have an MS SQL Server table with a Job Number field I need this field to start at a certain number then auto increment from there. Is there a way to do this programatically or within MSDE?
Thanks, Justin.
View 3 Replies
View Related
Jan 13, 2005
I want to add a computed field to an existing SQL table:
where Field1 is >0 and field2 is not null
set newfield = 'Y'
else set newfield = 'N'
I want to keep this existing table because I'm using it as the basis for an Access Report that is nearly complete.
BTW this table is the result of a DTS package that is comprised of several SQL executables so I need to be able to repeat this as part of a larger process.
Thanks!
sgreene
View 2 Replies
View Related
Apr 26, 2008
Hi Friends,
I have a existing table named as activity, and have the column like ID,Description. I want to add the Identity for the ID column using script only.. Have any ideas how to do in sql query analyser?
Thanks in Advance
View 7 Replies
View Related
Dec 18, 2007
how do i add columns to an existing table?
View 1 Replies
View Related
Oct 18, 2007
Dear Friends,
what is best and quicker way to add 500 columns to existing table having 145 columns already.
Is there any way to avoid manual work of adding columns one by one in design mode or using script.
I have a TXT file (comma delimited) that contains all those columns names as a first row,but I am not sure if i can use DTS package to create table design having such sourcre TXT file.
Any advice?
Thanks,
View 1 Replies
View Related