How To Get The @@Identity For Multiple Inserts?

Jun 6, 2004

Hi All,

Iam in a situtation where i have a query Which Inserts into a table from Select statement. But there is another table which is dependent on the Primay key of the inserted table.
Since the insert is multiple iam not able to use the @@Identity.
Can some one suggest me How can i over come this situtation.
Also Triggers cant be used as the the records are of huge numbers.

Eg:-
INSERT INTO Users (FirstName, SecondName) SELECT FirstName, SecondName From Old_Users

INSERT INTO UserDependent(UserID,OtherFields)
VALUES(@@Identity,'SomeOtherValue')

Thanks
Tanveer

View 3 Replies


ADVERTISEMENT

Multiple Tables, Inserts, Identity Columns And Database Integrity

Apr 30, 2008

Hi all,
I am writing a portion of an app that is of intensely high online eCommerce usage. I have a question about identity columns and locking or not.
What I am doing is, I have two tables (normalized), one is OrderDemographics(firstname,lastname,ccum,etc) the other is OrderItems. I have the primary key of OrderDemographics as a column called 'ID' (an Identity Integer that is incrementing). In the OrderItems table, the 'OrderID' column is a foreign key to the OrderDemographics Primary Key column 'ID'.
What I have previously done is to insert the demographics into OrderDemographics, then do a 'select top 1 ID from OrderDemographics order by ID DESC' to get that last ID, since you can't tell what it is until you add another row....
The problem is, there's up to 20,000 users/sessions at once and there is a possiblity that in the fraction of a second it takes to select back that ID integer and use it for the initial OrderItems row, some other user might have clicked 'order' a fraction of a second after the first user and created another row in OrderDemographics, thus incrementing the ID column and throwing all the items that Customer #1 orders into Customer #2's order....
How do I lock a SQL table or lock the Application in .NET to handle this problem and keep it from occurring?
Thanks, appreciate it.

View 2 Replies View Related

Identity Inserts

Apr 15, 2006

Hey All,
I was trying to use a typed dataset to create a very simple DAL. I found that the code generated for the INSERT statement includes an identity field the table has. That can obviously never work (unless identity_insert is set, which it is not). My question is whether it is possible to control this insert statement generation? Is there a property I am missing somewhere? My solution was to change the INSERT statement on the DataTableAdapter, but that seems awkward for me to have to do that..
Thanks,
Yuval

View 3 Replies View Related

SQL Server CE 3.5, Typed Dataset, Retrieving @@IDENTITY Of Inserts?

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

Multiple Inserts

Jul 14, 2005

Hi, I'm trying to create a form where new names can be added to a database. The webform looks like this:<body MS_POSITIONING="GridLayout">        <form id="Form1" method="post" runat="server">         Name:<asp:TextBox ID="newName" runat="server" />         <INPUT id="NewUserBtn" type="button" value="Create New User" name="NewUserBtn" runat="server"            onServerClick="NewBtn_Click">&nbsp;     </form>And the code behind looks like this:Public Sub NewBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewUserBtn.ServerClick        Dim DS As DataSet        Dim MyConnection As SqlConnection        Dim MyCommand As SqlDataAdapter
        MyConnection = New SqlConnection("server=databaseserver;database=db;uid=uid;pwd=pwd")        MyCommand = New SqlDataAdapter("insert into certifications (name) values ('" & newName.Text & "'); select * from certifications", MyConnection)
        DS = New DataSet        MyCommand.Fill(DS, "Titles")
        Response.Redirect("WebForm1.aspx", True)    End SubWhen I try to insert one name it works. When I try to insert a second name, it overwrites the old one. Why is that?Thanks.James

View 3 Replies View Related

Multiple Dynamic Inserts With SQL

Sep 10, 2007

 I'm try to a multiple insert from one database to another by using this code:insert into [mpis].[dbo].[Residents] (acno,surname,name,ID,type)        (select top 30 acno,surname,name,id,type        from [PretoriaDB].[dbo].[WorkingDB])  but I keep on getting this error:Msg 8152, Level 16, State 9, Line 1String or binary data would be truncated.The statement has been terminated.  Can any one help!! 

View 7 Replies View Related

Advice For Multiple Inserts

Apr 11, 2007

hi I was just writing for some general advice. If I am to do many updates on a table is it okay to keep updating with many insert statements inside of a loop closing the connection each time? Here is some pseudo code to give you an idea:

cnn.open();
cmd = "insert into Cats values (red,4994,homer)";
for(int i=0; i<10000; i++)
{
cmd.executenonquery();
cnn.close();
}

View 2 Replies View Related

How To Do Multiple Inserts For One Row Of Data?

Aug 21, 2006

I need to insert multiple rows for input rows that meet certain conditions.

My input data is as follows.

ZIPCode, Plus 4, Range, City, State

What I need to happen is that if there is a value in the range column that is > 0 then I need to insert a row for every range item.

For instance say I have the following data.

54952, 1001, 10, Menasha, WI

What I need imported is :

54952, 1001, Menasha, WI
54952, 1002, Menasha, WI
54952, 1003, Menasha, WI
54952, 1004, Menasha, WI
54952, 1005, Menasha, WI
54952, 1006, Menasha, WI
54952, 1007, Menasha, WI
54952, 1008, Mensaha, WI
54952, 1009, Mensaha, WI
54952, 1010, Mensaha, WI
54952, 1011, Mensaha, WI

Any help in pointing me in the right direction would be great. Thanks for your help in advance.

View 2 Replies View Related

Multiple Inserts In Single Trigger

Feb 10, 2014

I have a requirement that if in a table update happened based on 1st condition then it should insert in one way and if update happened on second condition the insert statement will differ. That is it should insert the deleted records i.e., previous records existed in a table.The syntax is like

CREATE TRIGGER [dbo].[tr_a] on [dbo].[A]
AFTER UPDATE
AS
BEGIN TRY
IF NOT EXISTS

[code]....

I m getting some syntactical errors.

View 6 Replies View Related

Stored Procedure For Multiple Inserts

Feb 24, 2008

Hello all,

I'm working on a project for fun with some friends and have run into an issue with stored procedures. I've dealt with SQL quite a bit at my current job, but always from the perspective of somebody querying the database. The database was always managed by someone else and I never had to worry about the underlying code. Now, with my own project at home, I'm trying to deal with a situation and would like to use one, but I'm not sure if it is the best option and if so, exactly how to go about it.

Imagine a site that tracks movies. I have 3 tables:

Movies ( MovieID, Title, DirectorID, ActorID )
Actors ( ActorID, Name )
Director (DirectorID, Name)

This is an overly simple example, but it gets to the heart of my problem.

Okay, now what I'm wanting to do is to be able to write a procedure that would let me create my entries from just one call -- for instance

create_movie( 'Super Movie', 'directorJoe', 'actorJohn' )

that would do the following things:
-Look and see if the given director and actor already exist (from previous films)
-If they do, grab their ID values and use those in the new movie entry
-If they do not, create new entries and get THOSE ID values to use in the new movie entry

Can this be done in a stored procedure (I'm pretty sure it can be) and what sort of commands should I look into -- I'm not looking for a complete solution, cause I want to learn, but I am having trouble finding examples that fit my scenario.

Thanks.

View 3 Replies View Related

ROLLBACK TRANSACTION - MULTIPLE INSERTS

May 28, 2008

Hello,
I have a stored procedure that updates my table with values entered in a datatable in my windows app.

An error occurs 1/2 way through the update process. I assumed that by implementing the rollback transaction command that the inserted lines would not be saved to my db. This is not the case.

I will elaborate a little more on what I need. From my windows app I have a datatable with 100 new rows that need to be written to my db. This I can do. However, a problem crops up should there be an error during the transfer. Let's say I have 100 rows in my datatable in my windows app, and row 57 causes an error, what is happening is that rows 1-56 are committed and 57-100 are not. What I need is for 1-100 to NOT be committed to my DB should there be a snag along the way. I need a code sample as I'm having way too much difficulty with this as is.

The basic code that copies to my db(sans rollback):
BEGIN
SET NOCOUNT ON
INSERT INTO userprofile (uid, uname, ustatus)
VALUES @userid, @username, @userstatus;
END


Thank You.

View 3 Replies View Related

Performing Multiple Inserts In Mssql Sproc

Apr 27, 2005

I am trying to insert a message into my database for every "league" I have in the database. I would like to do this in one sproc but am not sure how this would be done. Here is the loop if it has to be done outside of sql

Code:


message = "This is a test message."
leagues[] = getAllLeaguesInSite()

begin transaction
for each league in leagues
insertLeagueMessage(league, message)

if noErrors
commit transaction
else
rollback transaction


-----
as you can see it would be nice to be able to do this all in mssql . If it can be done please let me know.

Thanks

View 4 Replies View Related

Transact SQL :: Multiple Inserts Different Columns And Tables Into One Table

Oct 12, 2015

I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.

If object_ID(N'Bezeichnungen') is not NULL
   Drop table Bezeichnungen;
GO
create table Bezeichnungen
(
 Artikelnummer nvarchar(18),
 Artikelbezeichnung nvarchar(80),
 Artikelgruppe nvarchar(13),
 
[code]...

View 19 Replies View Related

How Can I Do A Multiple Insert Or Multiple Updates Or Inserts And Updates To The Same Table..

Oct 30, 2007

Hi...
 I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
 
this is my sproc...
 ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.

AS

SET NOCOUNT ON

DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int

SET @Sproc = OBJECT_NAME(@@ProcId)

SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END

SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END

SET NOCOUNT OFF
 
So how do i do multiple inserts and updates using this stored procedure...
 
Regards
Karen

View 5 Replies View Related

TRIGGER: Help With 2 IFTHEN Statements Driving Multiple Inserts Into B_items Table...

Jul 30, 2007

Assuming I should be using values from temp inserted to insure correct record...
Need help coding IF...THEN INSERT statements in following After TRIGGER:

Create TRIGGER trg_insertItemRows

ON dbo.a_form
AFTER INSERT

AS
SET NOCOUNT ON
-- Checkbox Driven:
IF a_form.missingCheckbox = -1 THEN
Insert into b_items (form_ID, parent_ID, ItemTitle)
Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ 'User checked Missing Data')

-- Textbox Driven:
IF a_form.incorrectTxtbox <> 'na' THEN
Insert into b_items (form_ID, parent_ID, ItemTitle)
Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ Correction: Replace '+ incorrectTxtbox + ' with '+replaceWithTxtbox)


Sample code below:

-- Source table the Trigger acts on
Create Table a_form (
form_ID int Not Null,
parent_ID int,
missingCheckbox bit,
missingNote varchar(100),
incorrectTxtbox varchar(50),
replaceWithTxtbox varchar(50)
)

--Target table Trigger inserts into
Create Table b_items (
items_ID int Not Null,
form_ID int Not Null,
parent_ID int,
ItemTitle varchar(150)
)

View 5 Replies View Related

@@IDENTITY Problem With Multiple Recordsets

Apr 24, 2008

Can anyone help with this problem.

I am doing some mods to an existing db web app in asp. I am providing a facility to copy a subset of related records in a few tables. I am using one recordset to read in the selected records and then copying the data into another recordset creating a new record in the same table. I have to then pick up the @@IDENTITY of the new record so that I can update linked records in other tables.

The @@IDENTITY value returned is null unless I close the selection recordset.

I have created a simplified version of the code to demonstrate this.

The underlying table is called 'test' and it has 2 fields, 'test_id', integer, identity and 'test_desc' varchar(50)

ASP Code


<%
dim objConn, objRS1, objRS2, objRS3

sub OpenDatabase()
'open database object here instead of in page
Set objConn = Server.CreateObject("ADODB.Connection")
'Connect object to database through global.asa application variable
objConn.Open = Application("test_ConnectionString")
'Create recordset object to use
Set objRS1 = Server.CreateObject("ADODB.Recordset")
Set objRS2 = Server.CreateObject("ADODB.Recordset")
end sub

sub CloseDatabase
Set objRS1 = Nothing
Set objRS2 = Nothing
objConn.Close
Set objConn = Nothing
end sub

function UnNull(numval)
if isnull(numval) then
UnNull="NULL"
else
UnNull=cstr(numval)
end if
end function


OpenDatabase

strSQLQuery = "select * from test where test_id in (1,2) order by test_id"
objRS1.Open strSQLQuery, objConn, 1, 3

do while not objRS1.eof
objRS2.Open "test", objConn, 1, 3
objRS2.AddNew
old_test_id = objRS1("test_id")

objRS2("test_desc") = objRS1("test_desc")
objRS2.Update
objRS2.Close

' *1 objRS1.Close

strSQLQuery = "select @@IDENTITY as new_id"
objRS2.Open strSQLQuery, objConn
new_test_id=objRS2("new_id")
objRS2.close

response.write("Old ID = " & UnNull(old_test_id) & ", New ID = " & UnNull(new_test_id))
response.write("<p>")

' *2 response.end

objRS1.movenext
loop

objRS1.close

CloseDatabase

%>



Output

Old ID = 1, New ID = NULL
Old ID = 2, New ID = NULL

If I unrem the lines *1 and *2 then the output is

Old ID = 1, New ID = 11

I realise that there are other ways of doing this but this is only a simplified example whereas the actual application is far more complicated.

Thanks is advance

Kennedy

View 3 Replies View Related

Multiple Updates And Identity Fields

Sep 27, 2006

I have a table used by multiple applications. One column is an Identify field and is also used as a Primary key. What isare the best practices to use get the identity value returned after an INSERT made by my code.. I'm worried that if someone does an INSERT into the same table a "zillionth" of a second later than I did, that I could get their Identity value.



TIA,



Barkingdog

View 4 Replies View Related

Identity, Default Values And Multiple Rows

May 27, 2008

Howdy all,
I've run into an interesting scenario that I can't seem to resolve.

We have a table that we are using to create sequence ids. It's literally a table with a single field with the identity value turned on.

create table ident (seq_id int identity(1,1) primary key NOT NULL)


If I want to grab a new sequence id, I can execute

INSERT INTO
ident
DEFAULT VALUES
SELECT scope_identity() AS seq_id


All is well and good. However, now let's suppose that instead of just one, I'd like to get a range of values.

If the table had one other field in it, I could do the following

create table ident2 (seq_id int identity(1,1) primary key NOT NULL, placeholder char(1))
GO
;
WITH RECORDS AS
(
SELECT
CAST('A' as char(1)) AS col1
UNION ALL
SELECT
char(ascii(col1) + 1)
FROM
RECORDS
WHERE
col1 < 'C'
)
INSERT INTO
ident2
OUTPUT
INSERTED.seq_id
SELECT
NULL
FROM
RECORDS


Can this be done if there is only the identity column and if so, someone care to educate me?

View 10 Replies View Related

Identity, Default Values And Multiple Rows

May 27, 2008

Howdy all,
I've run into an interesting scenario that I can't seem to resolve.
We have a table that we are using to create sequence ids. It's literally a table with a single field with the identity value turned on.




Code Snippet
create table ident (seq_id int identity(1,1) primary key NOT NULL)





If I want to grab a new sequence id, I can execute




Code Snippet
INSERT INTO
ident
DEFAULT VALUES
SELECT scope_identity() AS seq_id






All is well and good. However, now let's suppose that instead of just one, I'd like to get a range of values. If the table had one other field in it, I could do the following




Code Snippet
create table ident2 (seq_id int identity(1,1) primary key NOT NULL, placeholder char(1))
GO
;
WITH RECORDS AS
(
SELECT
CAST('A' as char(1)) AS col1
UNION ALL
SELECT
char(ascii(col1) + 1)
FROM
RECORDS
WHERE
col1 < 'C'
)
INSERT INTO
ident2
OUTPUT
INSERTED.seq_id
SELECT
NULL
FROM
RECORDS





Is there a way to do this if there is only the identity column and if so, someone care to educate me?

From a design perspective, all I can say is "it's not mine" but I do have to live with it, or at least my coworker has to live with it. I'm merely trying to prevent them from opening a cursor and calling the default values version 45M times and no, that's not an exageration.

View 2 Replies View Related

Fuzzy Lookup Error - Multiple Identity Columns?

Apr 3, 2007

I'm developing an ETL solution that needs to look for duplicate records using a fuzzy lookup. If the lookup table has an identity column, I get the following error. I can get this to work on a local desktop instance of SQL server, but not on my development server or production box. Any help is greatly appreciated.


Also I've stripped down the incoming data for the lookup to a very simplified version of what I'd like to use, but if I can't get it to work then I can't add addtional columns to match with.



It's like it's trying to add it's own Id to the temp table created for the tokens



An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Multiple identity columns specified for table '##FLRef_070403_10:09:36_3532_aeac56a4-8bc0-4ff4-ac41-0984e293261a'. Only one identity column per table is allowed.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Database name 'tempdb' ignored, referencing object in tempdb.".

Error: 0xC004701A at Move Clean Records into Clean Enrollment, DTS.Pipeline: component "Fuzzy Lookup" (300) failed the pre-execute phase and returned error code 0xC0202009.

View 1 Replies View Related

Inserting To Multiple Tables In SQL Server 2005 That Use Identity Specification

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

SQL Server 2008 :: Retrieving Same Identity Column Value By Multiple Users

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

Last GASP On Insert Row In Table With Identity Field, And Get New Identity Back ?

Jul 9, 2006

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

View 10 Replies View Related

Insert Row In Table With Identity Field, And Get New Identity Back

Jun 30, 2006

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?

View 12 Replies View Related

T-SQL (SS2K8) :: How To Update Identity Column With Identity Value

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

Identity...I Need To Get The Last (or Highest Number In Identity Column)...

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

Problem In Using Sqlbulkcopy To Insert Data From Datatable(no Identity Column) Into Sql Server Table Having Identity Column

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

How To Use Identity On Non-identity Column (with Concurrence)

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

Inserts Per Second

Mar 3, 2006

Just curios,

I have four 72 GB Drive on a RAID 5

Disk Specs
IO/Second = 130 per disk
Speed RPM = 15 K

When I did a load test of inserting data into a table
with four Columns

Col1 INT
Col2 VARCHAR(32)
Col3 VARCHAR(4000)
Col4 DATETIME

I could insert around 1044 Inserts per second where as
I thought I could do max of 520 Inserts ( 130 * 4 ) because
each disk can only take 130 Inserts multiplied by 4 Disks
gives me a theoritical limit of 520.

Also How does the Query Analyser Connects to the datbase
Server.. does it use ODBC

Thx
Venu

View 1 Replies View Related

No Of Inserts

Mar 21, 2006

I am doing a simple IO Test with the below script ...

Just wanted to keep things simple and to check how many Inserts I can do on a given SQL Server.
I am running the below script from QA for 1 minute and then divide the No or rows inserted by 60.

Will it give me approximate results by duing this?

Actually the datafiles .MDF files are sitting on a single drive where the manufacturer specs shows that it will handle 130 IO's per disk. With the below script I am getting around 147 Inserts per second.

But my boss says that he is getting 2000 inserts per second on his laptop from a ...Am I missing some thing?

DECLARE @lnRowCnt INT
SELECT @lnRowCnt = 100000

WHILE @lnRowCnt > 0
BEGIN
SET NOCOUNT ON
INSERT INTO CTMessages..Iotest
SELECT @lnRowCnt , 'VENU' , REPLICATE ( 'V' , 4000 ) , 1000000

SELECT @lnRowCnt = @lnRowCnt - 1
END

Thx
Venu

View 3 Replies View Related

Two Inserts In One InsertCommand

Apr 11, 2007

I'm using a SQLDataSource and trying to do two inserts into two different tables with one InsertCommand, but it's not working. Here's the code I'm trying to use. Do you see anything wrong with the syntax? I keep getting an error that says error near ','  but I can't figure out why. Thanks
 
InsertCommand="INSERT INTO [OurProjects] ([Title], [Description], [Location], [Anchors], [Size], [Developer], [DesignBuilder], [Architect], [ImageName], [MapName], [ProjectTypeAbbrev], [Deleted]) VALUES (@Title, @Description, @Location, @Anchors, @Size, @Developer, @DesignBuilder, @Architect, @ImageName, @MapName, @ProjectTypeAbbrev, @Deleted),
INSERT INTO [OurProjectsImages] ([OurProjectsID], [ImageMonthName], [SwfName]) VALUES (@OurProjectsID, @ImageMonthName, @SwfName)"

View 2 Replies View Related

SQL TimeSpan And Inserts

May 27, 2008

I need to Add a Check in the database to ensure that user can only enter up to 20 entries to Database in a period of 10 minutes. Basically, to guard against people using scripts to add data to the database ( instead of using CAPTCHAE on the front end) what we want to do is restrict user to entering at most 20 transactions in 10 Minutes.How do I handle or do this in SQl Server 2005??
 
What I figure to do is right after I do an INSERT into the table, I Select the last 20 entries into that same table and then Calculate the Total time it took to add those 20 transactions and set the righ flag.
1) How do I select last 20 entries into a table??
2) How do I calculate the total time that elapsed between adding the first of those 20 records and the last??
Thanks in Advance

View 6 Replies View Related

Next Inserts ID Number, Someone Please Help

Sep 21, 2004

G'day,
I have a table with a primary key being a bigint and its set to auto increment (or identity or whatever ms calls it). Is there anyway I can get the ID number that will be assigned to the next Insert before I insert it? I want to use that ID number within another field when inserted.

I hope that makes sense.

Thanks for any help.

Robbo

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved