Ok,
I just need to know how to get the last record inserted by the highest
IDENTITY number. Even if the computer was rebooted and it was two
weeks ago. (Does not have to do with the session).
Any help is appreciated.
Thanks,
Trint
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
Hi,
I have the following two tables:
Code:
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?
Thanks.
I am migrating a web application I wrote from ASP to ASP.Net, and fromAccess to MS SQL server.In the Access version, I did not use the auto number for creatinginvoices and other documents, because I heard somewhere (perhapsincorrectly) 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, wouldlook for the highest value in the table and create the new record withthe next number.So my question is, am I safe in assuming that in MS SQL that I can seta starting number for the next, let's say, invoice and that newnumbers will be issued in sequence, and that these numbers will neverchange? What happens if an invoice is deleted? is the number goneforever? Just wondering how others deal with these issues...thanks.Larry- - - - - - - - - - - - - - - - - -"Forget it, Jake. It's Chinatown."
View 2 Replies View RelatedHi ,,
How to write the Sql Query to return the next generated Identity from the Sql server database.
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:
Code:
ALTER PROCEDURE dbo.sp_NewPersonalPic
(
@photo_name VARCHAR(50) = NULL,
@photo_location VARCHAR(100) = NULL,
@photo_size VARCHAR(50) = NULL,
@user_name VARCHAR(50) = NULL,
@photo_caption VARCHAR(150) = NULL,
@photo_default BIT = NULL,
@photo_private BIT = NULL,
@photo_number INTEGER = NULL,
@photo_date DATETIME = NULL
)
AS
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:
photo_number photo_date
|1| 2006-06-26 21:43:36.653
|2| 2006-06-26 21:43:50.000
|3| 2006-06-26 21:45:25.217
|4| 2006-06-26 21:45:33.763
|5| 2006-06-26 22:39:42.670
|6| 2006-06-26 22:39:49.200
If |3| is removed above, the numbers are reordered based on the time of entry sequence.
Any suggestions on how to acheive this in my stored procedure? Currenly, i get the correct order, but it goes crazy when i delete and add.
Thanks and sorry for the verbose post.
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
ID A B C AVG
------------------------
1 08 09 10 -
------------------------
2 10 25 26 -
------------------------
3 09 15 16 -
------------------------
I want to calculate the average of the larges two number from the column A,B & C for particular identity and store that average in the AVG column....
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'.
View 2 Replies View RelatedI'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
* I can't change anything on T
* The PK is NOT an Identity
Example data
CA1000
CA10001
CA10002
CA10003
CA11597
CA11603
CA1001
CA998
CA999
As you can see, CA11603 is the largest number in this list.
When I try the follow sql code,
SELECT
MAX([MyCode])
FROM
[MyTable]
WHERE (SUBSTRING([MyCode], 1, 2) = 'CA')
The largest number comes back as CA997
When I try
MAX([MyCode])
FROM
[MyTable]
WHERE [MyCode] LIKE 'CA%'
The largest number comes back as CA997
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA') ORDER BY Schoolmastercode
The largest comes back as CA10001
When I try....
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA')
The largest comes back as CA1278
What am I doing wrong?
Is there a function or a code that I can use to get the value of the next Identity column number?? (the auto incrementing column)
thanks a lot!
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
hi.i am using ms sql server 2000. can somebody tell me what the code would be to remove all the values in a given column and replace them with the associated number of the row with each execution. so, if i have a column:nums|1||2||3||4|and somebody deletes record |2|i would like the nums colum to update to|1||2||3|not:|1||3||4|it seems simple but i am having a hard time with this. how is it done?thanks.
View 14 Replies View RelatedAll of my tables in my database have keys that are autonumbered (datatype int with identity set to 1).
Whenever I go to insert a new entry into the table I execute an INSERT INTO command and leave off the key field, so that it is automatically inserted with the new row.
However, I need to that number, so that I may insert it as foreign key into another table. How would I go about retrieving this number? I thought about doing a Max() on that field, but I am not confident that SQL server would always use a higher number than everything previous. Is there a better way of accomplishing this? Is my design flawed from the start? Any feedback would helpful. Thank you.
How do I output a row number for a table solely for the purpose ofquerying for a unique row?In my problem, the table from a legacy system does not have a primarykey, so it limits various querying I'd like to do that identifiesuniqueness in the table.The problem is that since I'm using DTS to simply copy the table toSQL, I don't want to create identity rows.
View 2 Replies View RelatedI 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?
View 4 Replies View RelatedTable structure: col1 IDENTITY (seed=1 increment=1) + few other columns (col2...col7) + one text column (col 8)
I have around 50,000,000 rows per day inserted in the table T1. At the end of the day 40,000,000 rows are deleted. I have to keep the records for 12 months and then archive it. Database is 24/7 web serving and there is no down time allowed. IDENTITY column will go out of range (overflow) after less than two years, unless the identity seed is reset to the start value (seed=1, increment=1).
At the end of 12th month data is archived in another table and only last month is kept in the table T1. So table T1 enters new year with data from last month of the previous year. There are few other tables that refer to this table by using there own field with values from T1.IDENTITY column (referential integrity is not enforced). Identity column in T1 is needed as a unique id for some search actions. Performance is an issue therefore bigint data type is used for this identity column rather than decimal.
Another problem I have is how to do table update on one column (1 mil rows to be updated out of 2 mil of rows) with the minimum impact on the users who are querying this table heavily. Not need to mention that it is web app 24/7 no down time.
Thank you in advance.
Goran
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;
View 1 Replies View RelatedI have been trying to get the new sol_id that is added so I can use it in other code, but it is only returning 0. The new record is getting added to the table, but my varaiable myNewSolID is 0 in my response.write(myNewSolID)
I appeaciate any help!
Here is my steored procedure and my code:
ALTER PROCEDURE [dbo].[AddTrackings]
@DropDate datetime,
@Comment nvarchar(100),
@DateEntered datetime,
@EnteredBy nvarchar(50),
@Sol_ID Int OUTPUT
AS
INSERT INTO tblTrackings (DropDate, Comment, DateEntered, EnteredBy)
VALUES (@DropDate,@Comment,@DateEntered,@EnteredBy)
SET @Sol_ID=SCOPE_IDENTITY()
RETURN
Sub ProcessTracking()
Dim myNewSolID As Integer
Dim ConnectStr As String = _
ConfigurationManager.ConnectionStrings("2ConnectionString").ConnectionString
Dim MySQL As String = ""
If Me.chkDropSave.Checked Then
MySQL = "AddTrackings"
Dim MyConn As New SqlConnection(ConnectStr)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Dim InsertedInteger As New SqlParameter("@Sol_ID", SqlDbType.Int)
InsertedInteger.Direction = ParameterDirection.Output
Cmd.Parameters.Add(InsertedInteger)
Dim MySqlParamStart As New SqlParameter("@DropDate", SqlDbType.DateTime)
Cmd.Parameters.Add(MySqlParamStart)
MySqlParamStart.Value = Me.BasicDatePickerDropDate.SelectedValue
Dim MySqlParamAmtTo As New SqlParameter("@Comment", SqlDbType.NVarChar, 100)
Cmd.Parameters.Add(MySqlParamAmtTo)
MySqlParamAmtTo.Value = Me.txtComment.Text
Cmd.Parameters.AddWithValue("@DateEntered", Now())
Cmd.Parameters.AddWithValue("@EnteredBy", Profile.UserName)
Try
MyConn.Open()
myNewSolID = Cmd.ExecuteScalar()Response.Write(myNewSolID)
Catch ex As Exception
Response.Write(ex.Message)
Finally
MyConn.Close()
End Try
Else
End If
End Sub
Hi to all, Using sql query i want to check whether the column is Identity column or not? Please members reply me.
View 2 Replies View RelatedHaving an identity column in target table, how do I use SQL 7 DTS ?
In Import wizard, I chose 'ignore' in source, checked Enable Indenity Insert - DTS failed with a msg like - a column cannot have NULL...
In DTS package, had Fast load and Enable Indenity checked - no luck !
Unchecked Enable identity, do luck.
What would be the standard procs for this since this sounds like a very common
senario.
What I am expecting is to insert from source and SQL would take care identity column automatically.
Appreciate your help very much !
-Ivan
--------------------------------------------------------------------------------
I want to know how to reset the identiy column when i delete a row from the existing table.
Example:
I have a table a with id (identity incr value 1) and name
I have 10 records inserted into it.,which would be from 1...thru...10 i delete a record say 5 then i have 1,2,3,4,6,7,8,9,10, now i want them to be rearranged to systamatic order like 1,2,3,4,5,6,7,8,9 how can i do this.
Thanks in advance,
Shaheen
"God Bless America"
I have a table that has a field that will not accept null values. I was trying to create a new table with a new field that would accept null values. I would then drop the original table and rename the new table. The problem is my key field has the Identity column (Autonumber) turned on. This allows the recordID to be automatically generated. Is there anyway to bypass the Identity Column feature and still keep it there future records? I already tried the "with Nocheck Clause" but does not work.
View 2 Replies View RelatedHow does one bcp data INTO a MS SQL Server table that includes an IDENTITY column from a text file that does NOT include a value for the IDENTITY column? Surely, this must be a common process?
View 3 Replies View RelatedKudos to y'all! On most of my tables, I'm using a column of data type numeric and properties set as Identity with no replication. Now I know that it can handle only a length of 9 and obviously not nullable. Now by length of 9, does it mean in bytes or literal digits. If it is, then that would mean that it would reach only up to 999,999,999. What happens then if it reaches that digit?
View 7 Replies View RelatedHow do you insert into a table that has an identity column aset of rows with predefined values for the identity column.
I have tried to Set Identity_insert off and then insert but it didnt work.
any ideas?
can i add null to identity type column
View 5 Replies View RelatedHi frnds
I have a table with two columns eid , ename . eid set identity(1,1).
After inserting 10 rows how can we insert a user defined eid ?
need help
Thanks & Regards
Zakeer
Hi,I would like a piece of advice.I have 3 foreign keys in a table used as primary keys for this table.Is it useful in that case to have just one identity column that wouldbe used as the unique primary key, thus no need to have 3 primary keys?Advantages vs Drabacks ?Regards
View 7 Replies View RelatedHi Everyone:
I am new to Mobile programming. I am now working on a mobile project. I encounter an issue when I sync the data:
cause I can't modify the schema, so I have to use RDA instead of Merge replication on sql server 2005.
However, There is an identiy column on each table I will pull them down to local mobile database. And I will use those identity columns to connect tables. Even worse, the photo's new name will combine the photoID which is an identity column. There would an issue, if i sync data, the photoID would be same on different local mobile databases. And there would be generate same identity value when users sync data.
How can I avoid those issues? If you have any good ideas, please help me out so that I can meet the project deadline.
Thanks
James
Hi,
I need to turn off Identity of a column which already has records, i have tried SET IDENTITY_INSERT TableName OFF but it is not working. can any one please help me.