Determing The Identity Number After An INSERT INTO
Jan 5, 2006
All 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.
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
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?
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 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'm in the process of writing some utility scripts which will interface SQL2K to our backup storage system, Tivoli. In our shop we "fire" the backup process from the Tivoli storage server by invoking a command script on the client server. The issue I've run into is that some of our servers were installed with Named Instances, either instead of or in addition to the default instance.
Rather than hard coding these values in the scripts I'm looking for a way to determine all the instance names on a server. The scripts could then be generic without custom tailoring for current and future servers (and instances).
Sort of new to MSSQL and I have a problem with my maintenance script not deleting files on 2 out of 3 servers so I am trying to determine what patch level I am at. 'Server A' works as expected:
Microsoft SQL Server 2005 - 9.00.2050.00 (Intel X86)
Feb 13 2007 23:02:48
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
These two servers don't: Server B.
Microsoft SQL Server 2005 - 9.00.2050.00 (Intel X86)
Feb 13 2007 23:02:48
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Server C. Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
So I can see where I should patch server B to SP2 but why does Server C report as 9.00.1399 SP2?
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.
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?
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.
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."
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset. I have teh following code but it works fien when column names are given ahead of time. If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match. Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition. here is my code... SET @sql1='select * from table1'SET @sql2='select * from table2' IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn -- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
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?
Table 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.
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:
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;
I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.
after i have inserted a row into the DB i am trying to get the last identity like this : Dim myCommand2 As New SqlCommand("SELECT @@IDENTITY AS 'Identity'", ) Dim myReader2 As SqlDataReader myReader2 = myCommand2.ExecuteReader() where myConnection2 is the connection objectafter i did this i dont underdsand how do i get the 'Identity' value?i tried myReader2 ('Identity') but no luckany 1 can helpthnaks in advancepeleg
Let me start off by saying I have posted this on: comp.databases.ms-sqlserver
My apologies, try not to do that again.
I have a table that I am trying to insert into with data from another table. Here is that Query:
INSERT INTO item (identifier, name, customlgtxt1, weight,mainprice, customnumber3, customnumber4) Select partno, name, description, weight,price, qtyprice, qty From Import2
This seems to work fine.
My dilemma has to do with an 'id' column in that item table. This is incrementally updated by one..ie Natural Key. I have set it to Identity and used Set @@IDENTITY AS 'id'...worked like a charm. But for reasons that have to do with a front end admin tool used by the home office I can't set this column to have an Indentity property. Screws up the insert done with the admin tool.
This is a item/price database by the way. Name of item, price, description, qty price..etc.
So I've tried to put this trigger on the item table CREATE TRIGGER auto_fill ON [dbo].[item] FOR INSERT, UPDATE AS BEGIN declare @maxc int set @maxc = (select Max(id) from item) set @maxc = @maxc +1 Select 'id' AS '@maxc', notorderable = '0', createdon = getdate(), createdat = getdate(), createdby = 'Steve', modifiedon = getdate(), modifiedat= getdate(), modifiedby = 'Steve'
END
But the Insert Into statement (see above) will not work...giving the error "Cannot insert the value NULL into column 'id', table 'new_Catalog1v1.dbo.item" Which I know...that's why I set it to Identity..but that cannot be.
So the question is how to set an autonumber (or natural key or I'm not sure of the name) that updated from the max(id)table when inserting from another table.
And then...one more.
I have to update a table named UNIQUEIDS with the lastest value of the id column (max(ID))..UNIQUEIDS keeps track of the latest value inserted into the id column for a number of tables. Here is another trigger I put on the item table to update the UNIQUEIDS table.
create trigger upUniqueIds_item on [dbo].[item] for update,Insert, delete as begin Select @@Identity as 'id' from inserted Update uniqueids set id = @@identity Where tablename = 'item' End
But of course this doesn't work if I can't set the columns to IDENTITY.
I hope someone can help and I hope my explanation had made sense. Need to increment the id field using max(id) and update another table with the last imported value of max(id). One occurs during insert..another after the insert..i think?
i have a question: i have a table that has an identity id column called pId and also a column called ParentPid. now my question is that i want to insert the value of pId into the ParentPid when i'm adding a new Property to a table. any idea? thanks
