I am migrating a web application I wrote from ASP to ASP.Net, and from
Access to MS SQL server.
In the Access version, I did not use the auto number for creating
invoices and other documents, because I heard somewhere (perhaps
incorrectly) that if the db was ever compacted or otherwise changed,
it could change the values of the auto-numbers. Not a good thing.
So I wrote a routine that, just before creating a new record, would
look for the highest value in the table and create the new record with
the next number.
So my question is, am I safe in assuming that in MS SQL that I can set
a starting number for the next, let's say, invoice and that new
numbers will be issued in sequence, and that these numbers will never
change? What happens if an invoice is deleted? is the number gone
forever? Just wondering how others deal with these issues...thanks.
create table RECORD ( ID int not null, Issue_descr varchar(256) not null, Priority varchar(5) not null, Status varchar(12) not null, Date_add varchar(10) not null, Date_due varchar(10) not null, Date_complete varchar(10), PName varchar(32), primary key(ID), foreign key(PName) references PROJECT(PName) );
and
Code:
create table STEPS ( ID int not null, Num int not null, Descr varchar(256), Date_due varchar(10) not null, Date_complete varchar(10), Status varchar(12), primary key(ID, Num), foreign key(ID) references RECORD(ID) );
I have set PK "ID" in table RECORD to auto identity(1,1). I have done the same for PK "num" in table STEPS. However I am seeking this behavior in STEPS: ID num -- ---- 19 1 19 2 19 3 20 1 20 2 21 1
but what I'm getting is PK num doesn't "reseed" or reset to 1 as "ID" changes. PK num just auto-increments regardless of ID. Is there a workaround?
I am attempting to import data from a Lotus Notes database using DTS. The SQL table I am importing to has an identifying auto-number. I can't insert directly into it because the SQL server should, however I get an error if I ignore it in the DTS package. Is there any way to get around this?
Need help on the Auto Number or Identity Seed on the Oracle DatabaseI got an Access database that need to be converted to Oracle 9i.Somehow the Trigger we created to simulate the "AUTO NUMBER" on Accesscould not create the sequence number as soon as the value has beeninserted. The sequence number can only be created after we go to thesecond line. Please see the trigger below.Is there anyway we could create a trigger that could create thesequence number as soon as we enter a value? It should be verysimilar to the "Auto Number" on Access, or "Identity Seed" on SQLServer.----------------------------------------------------------1. sequence SNP.SECTION_ID_SQ:CREATE SEQUENCE SNP.SECTION_ID_SQSTART WITH 1INCREMENT BY 1NOMINVALUENOMAXVALUENOCYCLECACHE 20NOORDER/GRANT SELECT ON SNP.SECTION_ID_SQ TO "PUBLIC"/2. Trigger SNP.SNP001_T_I_GET_NEXT_SECTION_ID:CREATE OR REPLACE TRIGGER SNP.SNP001_T_I_GET_NEXT_SECTION_IDBEFORE INSERTON SNP.SNP001_SECTIONREFERENCING OLD AS OLD NEW AS NEWFOR EACH ROW WHEN (new.section_id IS NULL)BEGINSELECT section_id_sq.nextvalINTO :new.section_idFROM dual;END;
Hi, I have an auto incrementing int column setup which serves as my unique primary key. Just wondering what happens when the auto increment reaches the limit? Will it recycle numbers from the begining (who's rows have obviously been deleted by this stage)?
I'm a bit new to SQL Server, but here is the problem I am trying to getaround:1. I have lets say an Employee table where the emp_id is an identitycolumn that is auto assigned by the db.2. I wish to insert values into the tables, but sometimes I may wish touse the value of the emp_id that i supply while inserting (andsometimes not).Is there any way in MS SQL to do this? For e.g. is it setup like aconstraint that I can temporarily drop. I do not want to alter thetable, so that option is ruled out.Any other ways to do this (I know bcp is one of them that allows me todo this, but looking for ways in the db itself using SQL if possible)Thanks,Bharat
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
Hi. I am trying to figure out the code for sorting a manual (non-identity) number column in my table. the purpose is to show the user's pictures in perfect order (1,2,3,4,5,6...).
The Jist of my problem... When a user first inserts six pictures, he gets:
|1| |2| |3| |4| |5| |6|
All is good. But, say he deletes picture |3|. Now the list order looks like this:
|1| |2| <- |3| is removed |4| |5| |6|
And, then he inserts two more pictures, now he his this:
|1| |2|
|4| |5| |6| |7| <- |7| & |8| are added |8|
What i want to acheive is a "reshuffling" of the number order every time a picture is removed. So, when |3| is removed, |4| becomes |3|, |5| becomes |4| and so on. There should never be a gap in the order.
I am new to stored procedures, and have been trying to figure this out. Below is my guesswork:
BEGIN SELECT @photo_date = CONVERT(DATETIME,convert(char(26), getdate(), 109)) END
BEGIN SET @photo_number = 1 SELECT
@photo_number = ( SELECT COUNT(*) FROM dbo.PersonalPhotos b WHERE a.photo_date < b.photo_date ) FROM dbo.PersonalPhotos a ORDER BY a.photo_date END
BEGIN
My thinking is that it would be a safe bet to use the "photo_date" column as a litmus for my "photo_number" column (ie, the most recent record inserted by the user will always be at a later date than the previously inserted record). So:
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?
Hi, I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time. thanks. varun
I am working on an accounting system using VB.NET and sql server 2005 as a database. the application should be used by multiple users. i have a the following structure: Voucher: ID (primary), Date,TypeID, ReferenceCode, ..... Type: ID, Code, Name. (the user can add new type anytime!) (Ex: PV- payment voucher, JV - Journal Voucher ,....)
When adding a voucher the user will choose a type, according to this type (for each year) a counter will be increminted. for example: PV1, PV2....PV233,... the other type will have its separate counter JV1, JV2 ,...JV4569,.. I am using the sqlTransaction cause i am doing other operations that should be transactional with the insertion of the Voucher.
The question is : What is the best solution to generate a counter for each type?(With code sample)
My Identity value column is not autoincrementing, its seed is set to 1, and increment set to 1. yet when i click the auto generated 'Add New Record' button in the BindingNavigator control set (in a form where it is populated with databound text boxes), this Identity column is not auto incremented?
And if i try to write a value to this column, i get the error telling me this column cannot be written to. Yet if i do not write to it, then i get "This column does not allow nulls"
To load my data i am using:
Code Snippet 'clear dataset Me.DsQuote.Clear() 'fill the datagrid with data from tblQuoteID Me.TblQuoteIDTableAdapter.Fill(Me.DsQuote.tblQuoteID)
and to save data im using:
Code Snippet Me.Validate() 'end data edit Me.TblQuoteIDBindingSource.EndEdit() 'update the dataset with table data Me.TblQuoteIDTableAdapter.Update(DsQuote.tblQuoteID)
anything im doing wrong? this data is being viewed in a datagrid btw.
I'm having a problem with merge replication. Â My publisher and subscriber are SQL 2008 SP1, and my distributor is SQL 2008 R2. Â Currently, there is no access to the subscriber data aside from the replication agents, so there are no data changes going on there, data is only being modified on the Publisher. Â My plan was to get everything setup and do some internal testing before releasing the subscriber for use.
when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
can anyone give suggestions how to generating a number starting with certain numbers, example 33###, because when i insert new record into datatabase i want the number start 33111, or something and next record is 33112 thanks
Hi,I want to create a random unique auto generated number. dont wantto use GUIDs. Any other way to create it in isert statement itself,apart from storing a number in a table and increment while inserting.Help me!
I have a table namely Product with the following identity modifiers:- Column- "Num" Identity-Yes Identity Seed-1 Identity Increment-1
My problem is everytime i delete a row in the table Product, the Num column after the deleted row will not automatically replace the deleted Num column's data. Let say:
Num Name 30 Apple 31 Orange 32 Pineapple
If I delete row with Num 31, the Num column after the deleted row will not change to 31 but remain 32.
Num Name 30 Apple 32 Pineapple
What can I do to ensure that the Num column (Num 32) will change to Num 31?
I am inserting some data from a temp to an existing table. The existing table has a primary key that is a number that just appears to be number 1-460 (there are 460 rows in the table). The data type for the primary key is int.
When I insert from my temp table to the existing I want to be able to add the next number in but am not sure how I can set that up?? I have pasted my code below. I am inserting into Lab_test_add_conf and the primary key value is lab_test_conf_id. In my select statement where I am getting the values to input I just put a number for now but that isn't going to work. You can ignore the rest of the numbers I have inserted and commas with no values I actually need to go add those to the temp table and just set their default values but I just first wanted to determine how I can create an autonumber. This is a third party software so I don't want to change the actual datatype of the existing table.
Insert Into Lab_Test_Add_Conf (lab_test_conf_id,system_id,labtest_key,value_code, value_description,value_code_system,value_type,units,sequence_number,table_name, field_name,created_by,create_timestamp,modified_by,modify_timestamp,row_timestamp)
select '460','2250',labtestkey,valuecode,value_description,'L',value_type,units, sequence_number,table_name,field_name,'58',,'58',, from #TempLabTestConfigImport
How can I create a number sequence starting at a certain number and continue on for the number of records I have.
For example I have 3000 records in my table and a field named I created called RecordId which I'd like to start at number 1 and goto 3000 (or maybe even start at 9000 and goto 12000 or however many records there are).
In my pseudo SQL code Im guessing it would be something like...
select * from Incident
update Incident set RecordId( i=9000; i<=Number of Records in Table; i++)
Ok, let's say that I do a following inline INSERT statement..... INSERT INTO Car ( carMake, carModel ) VALUES ( 'Honda', 'S2000' ) When I do this, a carId int value will automatically be generated because it is the Car table's PK and is an int identity. How do I obtain this value so I can immediately use it in let's say, the following example. INSERT INTO Person ( carId ) VALUES ( @carId ) ..... where @carId is the value I need Maybe you would say I should do something like..... SELECT carId FROM Car WHERE carMake = 'Honda' AND carModel = 'S2000' However, let's assume that the carMake and the carModel fields are NOT unique. I'm sure there's an easy way to extract the new identity value (maybe even in the same query) as the INSERT, but I just don't know how. Thanks.
I need some help for designing the IDs / Primary keys for some master tables in my database. Following are master tables. Client_Master, Buyer_Master & Seller_Master; I want to set Client_Id, Buyer_Id & Seller_Id as their respective primary keys and they should have following properties
Client_Id :- a) should be auto-incrementing value, b) unique & c)should be of the format – CLXXXXXX, where “CL” {Constant start characters} & “X” {any number 0-9} Similarly:: Buyer_Id :- BYXXXXXX Seller_Id :- SLXXXXXX
We are implementing the database in MS-SQL 2005 & MySQL
Can anyone help me find a solution to this, especially in MS-SQL.
Is there any option to get the velue of auto increment number before inserting record.
My problem is that I want to get the auto increment number, for this I am using MaxId function, but If i have deleted the some rows from table, I could not get the actual.
For example there are 20record s in the table I have delete the last 3 records now I have the last value of Identity is 17. When I used the Maxid function It gives me the 18 number. But I need 21.