Retrieving The BigInt Value From The Identity Column After Inserting
Jul 26, 2007
I have a database that has a tble with a field that autoincrements as a primary key. meanig that the field type is BigInteger and it is set up as my Identity Column. Now when I insert a new record that field gets updated automaticly.
How can I get this value in the same operation as my insert? meaning, in 1 sub, I insert a new record but then need to retieve the Identity Value. All in the same procedure.
Waht is the way to achive this please?
Marc
View 2 Replies
ADVERTISEMENT
Mar 11, 2015
I am using following queries in a stored procedure.This stored procedure is executed through a dot net application.
DECLARE @DEPTNBR BIGINT
SELECT @DEPTNBR = DEPTNBR
FROM DEPARTMENT_DETAILS WITH (UPDLOCK,READPAST)
WHERE STATUS= 1
UPDATE DEPARTMENT_DETAILS SET STATUS= 0 WHERE DEPTNBR = @DEPTNBR
SELECT DEPTNBR,DEPTNAME,DEPTLOC FROM DEPARTMENT_DETAILS WHERE DEPTNBR = @DEPTNBR ​
From my queries,I am providing a available department information.Each user needs to get unique available department information.But when more number of users using the application concurrently, multiple users getting same department information.How to solve my problem?I always wants to get unique department information even though multiple users using the application concurrently.
View 6 Replies
View Related
Jan 2, 2001
Hi Friends
Happy new year 20001.
Is it requires DBO Permission for do 'SET IDENTITY_INSERT FARRentalUnit ON'
or Write permission is enough.
I don't want give dbo perivilage to user.
Please reply soon.
Thanks for reply in advance.
Murali
View 3 Replies
View Related
May 2, 2007
Hi All
Is it possible to insert the identity cloumn in a table via SSIS. I've an ID (Identity) column is a table and I am importing data in the table using Excel sheet. I want to insert the value of ID column as Identity(1,1).
View 10 Replies
View Related
Apr 21, 2008
Hi,
I am using SQL Server 2005 Mobile Edition & Merge Replication
in this I want to insert a record into table,
in that table I have taken UserID coloumn as auto incrementing Identity type.
In a book I read that,
If you are using an Identity column, you must find the next available number and reseed before an insert can be successful. You will also have to set up ranged identity columns on the published database to prevent errors when the new data is merged.
Now I want to ask here that, how should I ressed that value before inserting?????
any help in a form of CODe will be appriciated.....
thanks in advance...
View 8 Replies
View Related
Jan 16, 2006
Hi, All:Please help. I use sql server as back end and Access 2003 as front end(everything is DAO).A table on SQL server has an identity column as the key.We have trouble on adding records to this table using the following SQL.strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,E FROM myTableonAccessLocal"db.execute strSQLThe schema of the table "myTableOnSQLServer" and the schema of the table"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" andthe table "myTableonAccessLocal" does not have a key.When we try to run the query, it gives errors indicating the key is violatedor missing.Should I figure out the autonumber for it first and then add to the SQLserver table?Many thanks,HS
View 1 Replies
View Related
Apr 24, 2015
I am writing an Instead of Insert trigger. I would like to fire an error when inserting into an 'Identity' column. Since UPDATE([ColumnName]) always returns TRUE for insert statements, is there an easy/fast way around this? I don't want to use:Â
IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NULL))
here is my pseudo-code...
CREATE VIEW [org].[Assets]
WITH SCHEMABINDING
[Code] .....
-- How does this statement need to be written to throw the error?
--UPDATE([AS_ID]) always returns TRUE
IF(UPDATE([AS_ID]))
RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;
-- Is there a faster/better method than this?
IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NOT NULL))
RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;
-- Do Stuff
END;
-- Should error for inserting into [AS_ID] field (which is an identity field)
INSERT INTO [org].[Assets]([AS_ID], [Tag], [Name])
VALUES(1, 'f451', 'Paper burns'),
(2, 'k505.928', 'Paper burns in Chemistry');
-- No error should occur
INSERT INTO [org].[Assets]([Tag], [Name])
VALUES('f451', 'Paper burns'),
('k505.928', 'Paper burns in Chemistry');
View 7 Replies
View Related
Jun 19, 2008
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
View 6 Replies
View Related
Apr 21, 2008
Hi,
I am using Microsoft SQL Server 2000. I am inserting cyrillic characters in my table by using query analyzer. See sample below:
Column "LangText" is nvarchar data type.
INSERT INTO Sentences (LangText) VALUES ('Поиск')
Problem occurs when I retrieve via query analyzer by using select statement I got "?????" question marks.
View 6 Replies
View Related
Mar 19, 2008
Folks:
I need help with this. When I run the below script (only select) it retrives around 130K records and gives me the output within 2 mins. Whenever I try to put the same output in a temp or permanent table it takes hours. Any Idea why?
SET NOCOUNT ON
DECLARE @ImportId INT
SET @ImportId = 5151
DECLARE @ResultXML XML
SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId)
SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName,
resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP,
CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage,
resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType,
CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff
FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode)
CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)
===================================================================================
Same Query when trying to insert the records in a temp table it takes hours.
===================================================================================
SET NOCOUNT ON
DECLARE @ImportId INT
SET @ImportId = 5151
DECLARE @ResultXML XML
SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId)
create table #TResults
([ID] [INT] IDENTITY(1,1) NOT NULL,
DealName VARCHAR(200),
CUSIP VARCHAR(100),
Vintage INT,
PoolType VARCHAR(100),
PaidOff BIT)
INSERT into #TResults (DealName,CUSIP,Vintage,PoolType,PaidOff)
SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName,
resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP,
CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage,
resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType,
CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff
FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode)
CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)
SELECT * FROM #TResults
============================================
Thanks !
View 7 Replies
View Related
Nov 14, 2006
Hey, I've been having problems - when trying to insert a new row i've been trying to get back the unique ID for that row. I've added "SELECT @MY_ID = SCOPE_IDENTITY();" to my query but I am unable get the data. If anyone has a better approach to this let me know because I am having lots of problems. Thanks,Lang
View 2 Replies
View Related
Apr 8, 2008
Hello there!
I've been sitting here thinking how to work through this problem but I am stuck. First off I have two tables I am working with.
Request TablerqstKey (this is the primary key and has the identity set to yes)entryDte datetimesummary nvarchar(50)etryUserID nvarchar(50)rqstStatusCdeKey bigint
ReqStatusCode TablerqstStatusCdeKey bigintstatusCode nvarchar(50)
I have webforms that are using Session variables to store information from the webpages into those variables and insert them into these tables. However, I am wanting to insert the rqstStatusCdeKey (from the ReqStatusCode Table) value into the Request Table. The rqstStatusCdeKey value i am wanting to insert is '3' which stands for "in Design" (((this is going to be the default value))). I need help :-/ If you need me to explain more/ clarify, and show more information I can. Thank you so much!!!
View 4 Replies
View Related
Oct 2, 2007
The following code inserts a record into a table. I now wish to retrieve the IDENTITY of that entry into a variable so that I can use it again as input for other inserts. Can someone offer assistance in handling this.... I tried several alternatives that I found on the internet but none seem to work...
Thanks!
Dim objConn3 As SqlConnectionDim mySettings3 As New NameValueCollectionmySettings3 = AppSettingsDim strConn3 As StringstrConn3 = mySettings3("connString")objConn3 = New SqlConnection(strConn3)Dim strInsertPatient As StringDim cmdInsert As SqlCommandDim strddlSex As StringDim strddlPatientState As StringDim rowsAffected As Integer
strddlSex = ddlSex.SelectedItem.TextstrddlPatientState = ddlPatientState.SelectedItem.TextstrInsertPatient = "Insert ClinicalPatient ( UserID, Accession, FirstName, MI, " & _"LastName, MedRecord, ddlSex, DOB, Address1, Address2, City, Suite, strddlPatientState, " & _"ZIP, HomeTelephone, OutsideNYC, ClinicalImpression, Today_Date_Month, Today_Date_Day, " & _"Today_Date_Year) Values (@UserID, @Accession, @FirstName, @MI, @LastName, @MedRecord, " & _"'" & strddlSex & "', @DOB, @Address1, @Address2, @City, @Suite , '" & strddlPatientState & "', " & _"@ZIP, @HomeTelephone, @OutsideNYC, @ClinicalImpression, @Today_Date_Month, @Today_Date_Day, " & _"@Today_Date_Year)SELECT @@IDENTITY AS NewID SET NOCOUNT OFF"
cmdInsert = New SqlCommand(strInsertPatient, objConn3)
cmdInsert.Parameters.Add("@UserID", "Joe For Now")cmdInsert.Parameters.Add("@Accession", Accession.Text)cmdInsert.Parameters.Add("@LastName", LastName.Text)cmdInsert.Parameters.Add("@MI", MI.Text)cmdInsert.Parameters.Add("@FirstName", FirstName.Text)cmdInsert.Parameters.Add("@MedRecord", MedRecord.Text)cmdInsert.Parameters.Add("@ddlSex", strddlSex)cmdInsert.Parameters.Add("@DOB", DOB.Text)cmdInsert.Parameters.Add("@Address1", Address1.Text)cmdInsert.Parameters.Add("@Address2", Address2.Text)cmdInsert.Parameters.Add("@City", City.Text)cmdInsert.Parameters.Add("@Suite", Suite.Text)cmdInsert.Parameters.Add("@strddlPatientState", strddlPatientState)cmdInsert.Parameters.Add("@ZIP", zip.Text)cmdInsert.Parameters.Add("@HomeTelephone", Phone.Text)cmdInsert.Parameters.Add("@OutsideNYC", OutsideNYC.Text)cmdInsert.Parameters.Add("@ClinicalImpression", ClinicalImpression.Text)cmdInsert.Parameters.Add("@Today_Date_Month", Today_Date_Month.Text)cmdInsert.Parameters.Add("@Today_Date_Day", Today_Date_Day.Text)cmdInsert.Parameters.Add("@Today_Date_Year", Today_Date_Year.Text)
objConn3.Open()cmdInsert.ExecuteNonQuery()objConn3.Close()
View 1 Replies
View Related
Aug 27, 2015
I found this to work:
SELECT uri, evFieldUri, evFieldVal
, CAST(evFieldVal
AS BIGINT)
FROM TSEXFIELDV
[Code] ....
It Returns:
uri         Â
evFieldUri         Â
evFieldVal         Â
(No column name)
224016Â 3267Â Â Â Â Â Â
+000089243829 89243829
224019Â 2717Â Â Â Â Â Â
+000089243825 89243825
224472Â 3333Â Â Â Â Â Â
+000000000000000000000017Â Â Â Â 17
225052Â 3267Â Â Â Â Â Â
+000089243829 89243829
225055Â 2717Â Â Â Â Â Â
+000089243825 89243825
So, then I went back to:
SELECT uri, evFieldUri, evFieldVal
, CAST(evFieldVal
AS BIGINT)
FROM TSEXFIELDV
[Code] ....
And it returns this error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
So, I tried again, and this worked…
SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal
AS BIGINT),
ISNUMERIC(evFieldVal)
FROM TSEXFIELDV WHERE URI
> 0 AND evFieldUri
IN ( SELECT URI
FROM TSEXFIELD WHERE exFieldFormat
IN (1,11))
I logged out and came back and tried again, and it still worked. So then I tried…
SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal
AS BIGINT)
FROM TSEXFIELDV
WHERE URI
> 0
[Code] ...
And it fails.
View 5 Replies
View Related
Feb 29, 2008
I have 2 tables - tblOrders and tblOrderDetails. Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders table:INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate)The primary key in this table is OrderID which is an Identity column. This is the foreign key in the tblOrderDetails table.I'm trying to get the Identity value from the first INSERT statement to use in the second INSERT statement:INSERT INTO tblOrderDetails (orderid, productid, productcost) VALUES (@orderid, @productid, @productcost) How do i obtain this value and how would I supply it to the second INSERT statement?
View 3 Replies
View Related
Aug 22, 2005
I'm using transactions with my SqlConnection ( sqlConnection.BeginTransaction() ).
I do an Insert into my User table, and then subsequently use the
generated identity ( via @@identity ) to make an insert into another
table. The foreign key relationship is dependant on the generated
identity. For some reason with transactions, I can't retrieve the
insert identity (it always returns as 1). However, I need the
inserts to be under a transaction so that one cannot succeed while the
other fails. Does anyone know a way around this problem?
So heres a simplefied explanation of what I'm trying to do:
Begin Transaction
Insert into User Table
Retrieve Inserted Identity (userID) via @@Identity
Insert into UserContact table, using userID as the foreign key.
Commit Transaction.
Because of the transaction, userID is 1, therefore an insert cannot be
made into the UserContact table because of the foreign key constraint.
I need this to be a transaction in case one or the other fails. Any ideas??
View 5 Replies
View Related
Jul 11, 2006
Hello!I use a procedure to insert a new row into a table with an identitycolumn. The procedure has an output parameter which gives me theinserted identity value. This worked well for a long time. Now theidentity value is over 700.000 and I get errors whiles retrieving theinserted identitiy value. If I delete rows and reset the identityeverything works well again. So I think it is a data type problem.My Procedure:create procedure InsertProduct@NEWID int outputasbeginset nocount oninsert into PRODUCT(D_CREATED)values(getdate()+'')set nocount offselect @NEWID = @@IDENTITYendMy C# code:SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);comm.CommandType = CommandType.StoredProcedure;comm.Parameters.Add(new SqlParameter("@NEWID",System.Data.SqlDbType.Int)).Direction =System.Data.ParameterDirection.Output;try{SqlDataReader sqlRead = comm.ExecuteReader();object o = comm.Parameters["@NEWID"].Value;//...}catch ( Exception ex ){throw ex;}The object o is alwaya System.DbNull. I also tried to use bigint.Any hints are welcomeCiaoSusanne
View 3 Replies
View Related
Mar 19, 2008
I'm at loss how I'm supposed to work with typed datasets and Sql Server Compact 3.5, when inserting records and I need to update my datatables with the primary key of newly inserted rows.
I've tried adding a RowUpdated handler to all tableadapters that look like this:
Code Snippet
void Adapter_RowUpdated(object sender, System.Data.SqlServerCe.SqlCeRowUpdatedEventArgs e) {
if(e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert) {
if(e.Row.Table.PrimaryKey.Length > 0) {
SqlCeCommand selectIdentityCommand = new SqlCeCommand("SELECT @@IDENTITY",e.Command.Connection);
if(e.Command.Connection.State == ConnectionState.Open) {
e.Row[e.Row.Table.PrimaryKey[0].Ordinal] = (int)selectIdentityCommand.ExecuteScalar();
}
}
}
I've previously used this type of approach when working with an OleDbDatabase, which works just fine. But it doesn't work with Sql Server CE 3.5, and since it doesn't support stored procedures I can't fix it that way either. And it doesn't support commands in a batch (i.e appending the Insert command of the adapter with ";SELECT @@IDENTITY") so that doesn't work either...
So how are we supposed to use Sql Server CE 3.5? It's impossible together with datasets? Or am I missing something obvious?
Any hints would be greatly appreciated! Thanks!
Cheers!
View 3 Replies
View Related
Dec 11, 2007
I recently converted a column that was once an int to an bigint on one of my tables. The modified column provided a generic row id information and there are duplicates within this column. I am trying to perform a self join via the following:
SELECT a.row_id FROM test_db a INNER JOIN test_db b ON b.row_id < a.row_id.
This code use to work when the column was an int but now I am getting high CPU issues since I converted to bigint. I am unsure on why the change to bigint will cause such an issue. The OS/SQL is 64BIT.
Thanks for the help in advance.
View 7 Replies
View Related
May 12, 2015
UPDATE P Â Â
SET Â
P.IsError=1
,P.IsDrawingRevNo=1Â
,ErrorMessage=ISNULL(ErrorMessage,'')+'| DrawingRevisionNumber DataType Is Not Valid, smallint expected(-32768 AND 32767)'
FROM ZPTSMGR.ProjectDrawingRaw P
WHERE  P.LogId=@LogId AND  P.ProjectId=@ProjectId AND  P.Revision > 32767  (P.Revision  NOT BETWEEN  -32768 AND 32767)  --SMALLINT RANGE  -32768 to 32767.
--DataType Range
--tinyint DataType  (MinVal: 0, MaxVal: 255). Its storage size is 1 byte.
--smallint DataType from -2^15 (-32,768) through 2^15 - 1 (32,767) and its storage size is 2 bytes.
--int DataType  -2^31(-2,147,483,648) to 2 ^31-1(2,147,483,647). Its storage size is 4 bytes.
--Bigint DataType -- from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Its storage size is 8 bytes.
The SQl statement fails, and not able to update it. The IsError flag need to set since the value does not lies in given range of smallint.--------say 457896523 which is not a small int value
View 7 Replies
View Related
Aug 12, 2009
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 Related
Sep 27, 2007
Someone asked me the other day about creating a table with one column that is an identity field and doing an insert so they can capture the SCOPE_IDENTITY() to use across seferal tables. I've never done anything like it and it seemed trivial at the time, but I'm not seeing how to make an insert work. So, for example, we have a table defines as: DECLARE @Bar TABLE (ID INT IDENTITY(1, 1) NOT NULL)
Now, how do you write a simple insert statement that will add a row to the table?
Basicaly, this is an acedemic exercise, but I can't seem to make it work unless I add another column to the table. Any ideas? Maybe I'm not caffinated enough..? :)
View 14 Replies
View Related
Apr 26, 2002
I have an odd issue with a stored procedure using output parameters and a database text field.
I am using custom VB6 COM+ objects in the middle tier and SQL Server 2000 on the back end. The stored procedure has an int as it's first parameter; it is an output parameter which gets set to @@IDENTITY after the data is written to the tables. The last parameter is a text.
The COM+ object executes the proc; ADO 2.7 incorrectly identifies the text parameter as an adVarChar, so I explicitely convert it to an adLongVarChar when I detect that the incoming data is > 8000 characters.
The proc writes the data correctly to the database in all cases. However, when the data is > 8000 characters, SQL doesn't appear to correctly set the output variable. No errors are generated, I simply don't get any value written to the variable. I've searched through MS's documentation, but can't seem to find anything on this issue.
Any help or thoughts are appreciated.
View 1 Replies
View Related
Oct 12, 2006
Hi
I need to insert values from a text-file to a table with a primary key as identity. In the text file I have no idea of the primary key values and i get "foreign key constraint violation" when trying to import null values into the column.
How can I solve the problem? With ordinary insert-statement there is no problem since the table generates identity- key values automatically. Is there a possibility to generate identity values with DTS-import?
Björn
View 1 Replies
View Related
Nov 15, 2006
I have a table with the following schema:
CREATE TABLE [itis].[wrk_taxon_authors] (
[wb_taxon_author_id] [int] NOT NULL ,
[taxon_author_id] [int] IDENTITY (1, 1) NOT NULL ,
[taxon_author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[kingdom_id] [smallint] NOT NULL
) ON [PRIMARY]
GO
I am trying to insert the following data (as you can see fields are seperated with the | ):
19||Flores-Villela and Sánchez-H., 2003|5|
20||Wiegmann, 1828|5|
16|17482|Gray, 1838|5|
17|9823|(Wiegmann, 1828)|5|
I get the following error:Server: Msg 4869, Level 16, State 1, Line 1
Bulk Insert failed. Unexpected NULL value in data file row 1, column 2. Destination column (taxon_author_id) is defined NOT NULL.
Server: Msg 4869, Level 16, State 1, Line 1
Bulk Insert failed. Unexpected NULL value in data file row 2, column 2. Destination column (taxon_author_id) is defined NOT NULL.
Since I have defined column 2 as an identity column, I don't understand why SQL Query analyzer is upset when I do not have a value in that field. To me, I would think it would auto-insert an integer (starting at seed 1 and incrementing by 1), but it doesn't. Could someone tell me what I'm doing wrong?
View 7 Replies
View Related
May 5, 2008
I am trying to build a Windows application using: Windows XP Pro ; VS Pro 2005, C# and SQL2005.
I have a database table as follows:
eg
1) myGameRecency which contains columns : GameId (identity specification column/primary key/not null), Date (not null), [1], [2], [3], [4]
Using the myGameRecencyAllBalls table ---
I wish to insert a date into a new row but have not been able to determine how to with the identity specification on the GameId column.
Can anyone please assist?
Thank you.
lpbcorp
sqlCmd.CommandText = "DECLARE @date datetime SET @date = '" + Date +
"DECLARE @lastRowGameId int " +
"DECLARE lastrow_gameidcursor CURSOR SCROLL FOR " +
"(SELECT GameId FROM " + DBGameName.ToString() + "RecencyAllBalls) " +
"OPEN lastrow_cursor " +
"FETCH LAST FROM lastrow_gameidcursor INTO @lastRowGameId " +
"' INSERT INTO " + DBGameName.ToString() + "RecencyAllBalls.Date VALUES (@date) WHERE GameId = @lastrow_gameidcursor + 1";
sqlCmd.ExecuteScalar();
View 6 Replies
View Related
Jan 25, 2015
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?
View 6 Replies
View Related
Sep 19, 2005
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
View 2 Replies
View Related
Jul 14, 2014
We are in the conversion process of making the database ints.This is a change from a guid PK to an integer based PK that uses an int Identity. The program still uses the guid, and we are trying to map that guid to the databases int.We insert using TVPs passed from code. Since the identity is being set upon insert I have three things to accomplish:
1) Insert all the data into the dbo table
2) Update the parent Id in the table
3) Pass the SetsId guid, Sets_Id int, ParentSets_Id int back to the program
This is a high transaction table that will have a lot of records (millions).
--Sample table creation. There is a FK between Sets_Id to ParentSets_Id, Clustered PK on the Sets_Id
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JSets]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[JSets](
[SetsID] [uniqueidentifier] NOT NULL,
[Sets_Id] bigint Identity (1,1) NOT NULL,
[code]...
View 0 Replies
View Related
Apr 2, 2008
I am having troubles trying to copy some rows from a table on my local computer to a table on a remote SQL Server 2005 that is being hosted by one of thos web hosting companies. The problem is that the table has an identity column. I first tried using the the following command:
SET IDENTITY_INSERT [remoteservername].Library2005.dbo.tblLanguages ON
but that results in the error:
Msg 8103, Level 16, State 1, Line 1
Table 'remoteservername.Library2005.dbo.tblLanguages' does not exist or cannot be opened for SET operation.
I read in another topic, that I should change this into the following:
EXECUTE [remoteservername].Library2005.dbo.sp_executesql N'SET IDENTITY_INSERT dbo.tblLanguages ON'
That command executes without error, but the problem is that I cannot perform the actual insert, because it is not within the execute statement. In other words, the following doesn't work:
EXECUTE [remoteservername].Library2005.dbo.sp_executesql N'SET IDENTITY_INSERT dbo.tblLanguages ON'
INSERT INTO [remoteservername].Library2005.dbo.tblLanguages
(colLangID, colEnglish, colGerman, colSpanish)
SELECT colLangID, colEnglish, colGerman, colSpanish FROM tblLanguages
This results in the error:
Msg 7344, Level 16, State 1, Line 2
OLE DB provider 'SQLOLEDB' could not INSERT INTO table '[remoteservername].[Library2005].[dbo].[tblLanguages]' because of column 'colLangID'. The user did not have permission to write to the column.
The remote server is linked correctly on my end via the sp_addlinkedserver and sp_addlinkedsrvlogin. Is there any way to force the remote server to turn IDENTITY_INSERT ON permanently and then let me execute as many INSERTS as I want and then turn it back OFF?
View 2 Replies
View Related
Feb 20, 2007
Hi, I am having a bit of hassle with trying to enter details to multiple tables in SQL Server 2005.
I have four tables, an
Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
Resistance Table (ResistId(PK Identity specific), Weight , Reps, Sets)
Aerobics Tables(AerobicsID(PK Identity specific), MachineID, Intensity, Time)
and a linking table for all of them.... ExerciseMaster(AttendanceID,ResistanceID,AerobicsI D)
My problem is that I can insert data to each specific table by itself using seperate insert statements.....eg....
//insert an attendance record to the attendance table
string userID;
userID = Session["User"].ToString();
SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@MembershipNo, @Date)";
pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);
int RowsAffected = 0;
try
{
RowsAffected = pgpDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}
finally
{
pgpDataSource = null;
}
//insert an aerobics record into the aerocibs table
SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([MachineID], [Intensity], [ExerciseTime]) VALUES (@MachineID, @Intensity, @ExerciseTime)";
pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);
int RowsAffected = 0;
try
{
RowsAffected = pgpDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}
finally
{
pgpDataSource = null;
}
//same code as above for the resistance table
However, i am facing the problem where this does not populate the link table(ExerciseMaster) with any information as i am unable to write the relevant IDs into the table that have been auto generated by SQL Server for each of the subTables.
I have read several forums where they recommend using something called @@IDENTITY but i have no idea how or where to use this in order to fill my exercise table...
Any help would be so much appreciated.... Also, hopefully what i have said all makes sense and someone will be able to help me...oh and one more thing...this is an ASP.NET page coding in C#
Cheers
Scotty
View 8 Replies
View Related
Aug 1, 2014
I'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
View 4 Replies
View Related
Jun 11, 2007
I know I can iterate through the schema table using the following.
Can I grab the actual value of each field while looping through the rows and columns?
schemaTable = reader.GetSchemaTable();
foreach (DataRow myDataRow in schemaTable.Rows)
{
foreach (DataColumn myDataColumn in schemaTable.Columns)
{
Console.WriteLine(myDataColumn + "= " + myDataRow[myDataColumn.ColumnName].ToString() );
}
}
An example is if one of the columns in the schema is called Firstname I would like to return:
Row 1
column name = Firstname
value= Bob
column name = Lastname
value= Smith
Row 2
column name = Firstname
value= Greg
column name = Lastname
value= Jones
View 3 Replies
View Related
