Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Get Next Unique ID From A Table Before Insert @@identity / Sequence


How do I get the next int value for a column before I do an insert in
MY SQL Server 2000? I'm currently using Oracle sequence and doing
something like:

select seq.nextval from dual;

Then I do my insert into 3 different table all using the same uniqueID.

I can't use the @@identity function because my application uses a
connection pool and it's not garanteed that a connection won't be used
by another request so under a lot of load there could be major problems
and this doens't work:

insert into <table>;
select @@identity;

This doesn't work because the select @@identity might give me the value
of an insert from someone else's request.

Thanks,

Brent


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Insert Row In Table With Identity Field, And Get New Identity Back
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 insertionas 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 avariableand not make it part of the data stream?



View Replies !   View Related
Problem When From Sequence File Insert Into Oracle Destination Table In SSIS Package
Hi ,



i was used the Follwing DataFlow for my Package.using Oracle 8i



FalteFile Source -------------> Data Conversion --------------->OLEDB Destination (Oracle Data table)

using above control flow to map the Source file to Destination . When i run the SSIS Package teh Folwing Error i got

"Truncation Occur maydue to inserting data from data flow column "columnName " with a length of 50 "

regarding this Error i i understood its for happening Data Length . so that i was changed the Source Column Length Exactly Match with the The Destination table.

still i am getting this Error. pls any one give me a solution . SHould i Change the DataType also?

pls give your suggestion



Thanks & Regards

Jeyakumar.M

chennai



View Replies !   View Related
Problem In Using Sqlbulkcopy To Insert Data From Datatable(no Identity Column) Into Sql Server Table Having Identity Column
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 Replies !   View Related
Unique Number/sequence
Hallo,Hot to get unique, sequential number during executionof stored procedure ?I can create table with autoincrement column,add record, get ident_current and delete recordeach time i need the number.However its not elegant i guess.best regardspluton

View Replies !   View Related
Generating Unique Sequence Number
Is there wa way to generate unique sequence numbers in SQL server?
(just like the way it is in Oracle i.e. seqeuence and then use nextval)

View Replies !   View Related
Last GASP On &&"Insert Row In Table With Identity Field, And Get New Identity Back &&" ?
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 OLEDBprovidercould write the second destination row before the first, that the proper parent-child relationship would begenerated 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 theapproach ofusing 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 fromt-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 Replies !   View Related
Insert Unique Rows In Temp Table
i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query.  I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table.
MemberID + Month + Year should ne unique in Temp table

View Replies !   View Related
Assigning Unique Sequence Numbers Across Different Tables
I have a procedure which updates a sequence number in a table such as the one below.


SeqSequence_Id

------------------------
NextNum 1


This is the procedure ...

create procedure DBO.MIG_SYS_NEXTVAL(@sequence varchar(10), @sequence_id int)
as
begin

update mig_sys_sequences
set
@sequence_id = sequence_id = sequence_id + 1
where
seq = 'CSN'

return(@sequence_id)
end


The purpose of this is to generate a sequential number each time the procedure is called. This number would then be used in a number of different tables to allocate a unique id so that the id is unique across the different tables.


1). What is the most efficient way of allocating these unique ids? The tables that I plan to update will already be populated with data.

2). How would I call the above procedure from an UPDATE statement?

Many thanks,

Fred

View Replies !   View Related
To Get An Unique Sequence Number (record Locking)
can someone pls show me a way to get an unique sequence at below senario:

PC1& PC2 using their own local client progam to access to Database Server at SERVER1.
In the SERVER1, there is a table SEQUENCE in a database DATABASE1.
And the table's structure of SEQUENCE are SeqType & SeqNo.
Here is the sample data:

SeqType SeqNo
Invoice 100
DeliveryOrder 200

Now, how to prevent PC1 & PC2 to get a sameInvoiceNo. if they request the Invoice No. at the same time?
Is it possible to lock the record Invoice when i perform a SELECT statement, then i update the Invoice to 101, lastly release the lock for Invoice?

pls advise. thanks.

View Replies !   View Related
Insert Into A Table With Identity
I have 2 tables with same structure.only difference is, TableA has key colum which is declared as identity starting from 1.

I want to insert data from TableB into TableA where TableB.Key is all null with TableA.Key starting from 9000001

when i try to do that iam getting an error
"An explicit value for the identity column in table 'lEADlOAN' can only be specified when a column list is used and IDENTITY_INSERT is ON.
"
even when I turned IDENTITY_INSERT ON for TableA

View Replies !   View Related
How To Insert A Row In A Table With 1 Identity Column
HI,I have an SQL Server table with only 1 column. That column is anidentity column. How can I insert a row in this table using SQLsyntax?I tried insertinto T_tableName () values ()and a few other options, but I can't seem to get it to insert.ThanksAlain

View Replies !   View Related
INSERT Into Table With IDENTITY Column
I'm sure this is a common problem but I can't find any relevant info on this site

I have a table that I would like to insert values into. I want to take the values from a secondary table using a select statement.

e.g. a simplified version...


TABLE1
(
Id INTEGER IDENTITY,
UserName VARCHAR(50),
Description VARCHAR(50)
)

TABLE 2
(
UserName VARCHAR(50),
Description VARCHAR(50)
)

Runing the insert statement

INSERT Table1 (UserName, Description)
SELECT *
FROM TABLE2

results in the error
erver: Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'Id', table 'CDS_Live.dbo.Table1'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I was under the impression that an identity column would be automatically inserted by SQL server. Now I know I could write a piece of anonymous Transact SQL which declares a cursor by selecting all rows from table 2 and inserting rows into table 1 on a row by row basis, but is there any way I could do the Insert with a single INSERT statement?

thanks

neill

View Replies !   View Related
Insert A Field (IDENTITY) Into Table
Hi,

I have a table with 1000000 records i try to add a field with following`spec.

[mkey] [int] IDENTITY(1,1) NOT NULL

I get the following meassage:-

yearly' table
- Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.

Then I Have the following error:-
Time out error

- How is the best way to copy a large table from one to anther.

regards

View Replies !   View Related
Retrieving Identity Field From Table On INSERT
 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 Replies !   View Related
BULK INSERT Into Table With Identity Column?
I have a file I'm trying to do some non-set-based processing with. Inorder to make sure I keep the order of the results, I want to BULKINSERT into a temp table with an identity column. The spec says thatyou should be able to use either KEEPIDENTITY or KEEPNULLS, but I can'tget it to work. For once, I have full code - just add any file of yourchoice that doesn't have commas/tabs. :)Any suggestions, folks?--create table ##Holding_Tank ( full_record varchar(500)) -- thisworkscreate table ##Holding_Tank (id int identity(1,1) primary key,full_record varchar(500)) --that doesn't workBULK INSERT ##Holding_TankFROM "d: elnet_scriptspsaxresult.txt"WITH(TABLOCK,KEEPIDENTITY,KEEPNULLS,MAXERRORS = 0)select * from ##Holding_tank

View Replies !   View Related
Insert Query On Table With Identity Column
I cannot insert into my appointments table because the primary key and identity column, appt_id, cannot be added. What do I have to change in my SQL statement to add new records into this table? I'm using SQL Server 2000 BE with Access Data Project FE.tbl_appointment-------------------1. appt_id (pk) --- identity column, seed 25, increment 12. date_id3. time_start4. time_end5. appt_details6. lkp_emp_idPrivate Sub btnAddAppts_Click()On Error GoTo Err_btnAddAppts_ClickDim strsql As StringDoCmd.SetWarnings Falsestrsql = "INSERT INTO [tbl_appointments] (lkp_emp_id, date_id, time_start, time_end, appt_details) values ('" & txtLkpEmpID & "', '" & txtDateID & "', '" & txtStartTime & "', '" & txtEndTime & "', '" & txtApptDetails & "')"DoCmd.RunSQL strsqlDoCmd.SetWarnings TrueDoCmd.CloseExit_btnAddAppts_Click:Exit SubErr_btnAddAppts_Click:MsgBox Err.DescriptionResume Exit_btnAddAppts_ClickEnd Sub I did check through Access and through Enterprise Manager and it is setup correctly. So I returned all rows in enterprise manager to manually enter an appointment to the table. I get the same error when doing data-entry straight to the table. [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity column 'appt_id'. It does not automatically populate the appt_id field the way it's supposed to. When I try to manually set a value in there, i get an error: "Cannot edit this cell."

View Replies !   View Related
Insert Values In To A Table Which Is Having Identity Column
Arif writes "I want to insert values in to a table which is having identity column, how? can you help me out from this problem.


Thanks in advance

Jamal"

View Replies !   View Related
Insert Data Into A Table Without Identity Column


Hi,
I need to insert data into a table using data flow task. Unfortunately this table's priamry key column (integer column)is not identity column. I am looking a way to get the primary key value for the new records. Please advice. Thanks

View Replies !   View Related
Can't Insert Rows In Table With Identity Column
Hello, I have a problem. I am trying to pull data out of one system and bring it into a SQL Server database for faster retrieval. The original table does not have an identity column and has a composite primary key. The table I am inserting the data into matches the original table exactly except I have an Identity column that I need for the removal of duplicates. The task gets to the final commit and then fails telling me that it can't insert a NULL into an identity field. Why is it trying to insert a NULL, the field should be auto-populating. I did add the Identity field to the table after the SSIS package was already built, but I did go into the destination and fix the column mappings. For the Identity column I just selected the "skip" option or whatever it was. Why are the identities not being auto-inserted and why is SSIS throwing this error?

Can anyone help???

Thanks!

View Replies !   View Related
Identity Sequence Of Multi-threads Insertion
Recently I'm working on a multi-thread solution based on SQL-Server, now I'm facing such a problem:

    Suppose I have process No.1(with multi-threads) inserting data to Table A, which has its identity column auto generated. And process No.2(also with multi-threads) retrieving data from Table A ,generate some records and insert the result into Table B. Both of these two processes are doing batch processing(batch retrieving and batch writing), and they are running parallelly.

    Now since process No.2 retrieve data sequencely by the identity of Table A, it found there exists missing results. This is due to that records with bigger identities are not necessarily commited earlier than those who have smaller identities.

    One direct solution is add one flag field in Table A indicating whether this record has been processed by process No.2, and each time it was processed , the field will be set. But unfortunatelly the table structure is not supposed to be modified.

    So is there any other good solutions for this problem? Thanks.

View Replies !   View Related
Cannot Insert Explicit Value For Identity Column In Table 'SS_Messeges' When IDENTITY_INSERT Is Set To OFF
 I use SQLExpress2005 and I search about this problem , this is a BUG in MsSql 2000 but I use sql Express 2005.although  in my  table I set IDENTITY_INSERT on (master Key)Please help me

View Replies !   View Related
Select Based On User Identity Name Included In Insert Into Second Table
I am having issues getting this to work.  I have the user login to a page to
put in a request for vacation.  When they login, I have a label that isn't
visible that is equal to their User.Identity.Name.
I select the user from the employee table where the username = the label User
Identity Name and pull in the emp_id which is the primary key that identifies
the user. 
I need to insert the request into the request table with the emp_id from the
select statement, without showing the em_id on the screen.  I tried using a
hidden field and assigning the emp_id as the value, but it isn't working. 
Not sure if this is the best way to do this.  Really new to ASP.NET 2.0 so I
really appreciate any help.
Thank you!

View Replies !   View Related
Cannot Insert Explicit Value For Identity Column In Table 'Gallery' When IDENTITY_INSERT Is Set To OFF.?
Hello!

Do anybody know how to fix this error?


Cannot insert explicit value for identity column in table 'Gallery' when IDENTITY_INSERT is set to OFF.?

Thanks!
Varcar!

View Replies !   View Related
Cannot Insert Explicit Value For Identity Column In Table 'Clients' When IDENITY_INSERT Is Set To OFF
Code Snippet

Private Sub AddClientToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddClientToolStripMenuItem.Click
Dim addclient As New newclient()
newclient.ClientID = 0
Dim result As DialogResult
result = addclient.ShowDialog
If result = DialogResult.OK Then

Dim Client As clients_and_sitesDataSet1.ClientsRow
Client = Clients_and_sitesDataSet1.Clients.NewClientsRow
Client.ClientID = addclient.ClientID
Client.Clientname = addclient.Clientname
Client.Street_and_number = addclient.Street_and_number
Client.Zipcode = addclient.Zipcode
Client.Place = addclient.Place
Client.Phone = addclient.Phone
Client.Email = addclient.Email
Client.Contact_person = addclient.Contact_person
Clients_and_sitesDataSet1.Clients.AddClientsRow(Client)
ClientsTableAdapter.Update(Client)------------------------------problem
MessageBox.Show("New Client is saved")
Else
MessageBox.Show("User cancelled operation")
End If
addclient = Nothing
End Sub






Code in VB(2005 express edition)


Hi all above is aa snipped from the code I am using. all works fine exxept the ------problem line.
when i try to run the program, i get an exeption

Cannot insert explicit value for identity column in table 'Clients' when IDENITY_INSERT is set to OFF


how can i turn this on???? I seem to be stuck, vieuwed the msdn video on the subject and cant find what I am doing wrong. I hope u guys can help me out.



And please give me specific guide lines on howto put in the code and code lines. I tried using
SET IDENTITY_INSERTClients ON
But than I get all errors bout things not being declared, set not being supported anymore, things like that.

Thanks for the effort you will put into helping me out and becomming a "self supported programmer"

View Replies !   View Related
Identity Or Unique Identifier
Hi there,I'm new to sql server. I've created a table which can be updated through an aspx form. However coming from an access background I don't know how to generate an auto number. I've read through a number of the threads on here and keep coming across Identity or unique identifier. However I can't actually find out how to implement these.Any help would be greatCheersStu

View Replies !   View Related
Use Identity For Unique Columns? Or Not?
I use the identity = yes for my unique columns in most of my tables that need it. I am trying to decide if I should change identity = no, and instead manually update my unique number column myself by adding one when I insert new rows.

The reason I want to do this is for maintainabilty and ease of transfering data for backup to other sql servers. I always have trouble keeping the identity numbers to stay the same as they are in the original database because when they are transfered to a db that has identity = yes, the numbers get rearranged.

It will also make it easier to transfer data from original db to another sql server db and use the data right away without having to configure the destination db to disable identity and then enable it, etc.

Any pro's con's appreciated,

Dan

View Replies !   View Related
How To Random Increment Unique Identity
Hi, for some reason I want to have a unique ID with a seed and random Identity increment (I want an ascending ID's but without able to know how many objects there are).

any ideas?
thanks in advance

View Replies !   View Related
Is @@IDENTITY Really Unique In Multi-sessions Scope
I have a table EugeneTest(id_num, fname, minit, lname)where field "id_num" is type IDENTITY and has UNIQUE constraintLet's say 2 user execute this code at the same time:DECLARE @return integeruse EugeneTestINSERT employees( fname, minit, lname)VALUES( 'Eugene3', 'F', 'Josephs')SET @return = @@IDENTITYIs is not possible to get duplicated value in id_num column becuase ofUNIQUEconstraint, but is it possible the both user get the same @@IDENTITYnumber( for example, if @@IDENTITY is evaluated and kept somewhere in thebuffer before the data actually get written to the disk )Thanks, Eugene

View Replies !   View Related
Insert Into Sequence Field
What I want to do is: insert into newtable (field1, field2, sequence) select
(fielda, fieldb, #) from oldtable and have the sequence field "re-seed" to 1 every time the value in fielda/field1 changes. This added requirement makes this not an identity field problem, if I understand identity fields. This is a data conversion problem, so I'm converting 1000's of rows from oldtable to newtable and want the sequence to re-seed at every change in value of the fielda/field1 data; in fact fielda/field1 is a simplification as there could be multiple controlling fields forcing a re-seed.

TIA...Al

View Replies !   View Related
Fastest Way To Insert Range/sequence
I'd like to use a stored procedure to insert large amounts of recordsinto a table. My field A should be filled with a given range ofnumbers. I do the following ... but I'm sure there is a better(faster) way:select @start = max(A) from tbl where B = 'test1' and C = 'test2'while @start <= 500000begininsert into tbl (A, B, C)values (@start, 'test1', test2')set @start = @start +1endanother question is, how to prevent that another user inserts the samenumbers into the field A?Thanks a lot for any help!ratu

View Replies !   View Related
How To Insert Sequence Number With Group
SOURCE TABLE
ID________COMMENT
123_______I am joe
123_______I am programmer
124_______I am Wang
124_______I am programmer
124_______I like cricket

DESTINATION TABLE

ID_____SEQ______COMMENT
123_____1_______I am joe
123_____2_______I am programmer
124_____1_______I am wang
124_____2_______I am programmer
124_____3_______I like cricket
can somebody please advise the easiest way to do this in sql 2000?

View Replies !   View Related
Insert By Key Or Sequence? What's The Default &"insert&" Feature?
I tried to insert large pool of data from table A to table B. Table B is then exported to Excel for viewing purpose. However, i found that some of the rows are not inserted in order, they seemed to be inserted in between other rows that are inserted before them. May I know what is the problem? There is no key assigned to table B. Do we need to disable key in order to have items inserted in sequence order?

in other word, instead of "insert", can we "append" records?

View Replies !   View Related
How To Create A Sequence Invoice Number And Insert Or Update To A Column?
Hi, can anyone teach me how to automatic create a invoice number and insert or update it to a column?

View Replies !   View Related
How To Insert A New Unique INT If None Is Given
I've got a table name Messages and each message has a MessageID (the primary, auto-generated column) and a ThreadID (for grouping replies together). When I create a new message, I want to insert a new ThreadID if none is given to the stored procedure. I'm looking for something like NewID() as the default value but that inserts a NewInt. Do I have to write my own trigger?And I know ThreadID should be a foreign key to a Threads table but I'm keeping it simple for now. I have the option of making that later if I like. Thanks. 

View Replies !   View Related
How To Get Unique ID After Insert
<disclaimer>I'm only using Web Matrix and have very little SQL knowledge</disclaimer>

I am doing an INSERT into a file where the primary key (UniqueID) is automatically generated. However, as soon as the record is inserted I need the UniqueID for further processing.

The only way I can see for me to get the UniqueID is to do a SELECT immediately after the INSERT, which seems a bit of an overhead.

So, just checking really, is this my only option or is there a super impressive way of passing the UniqueID back via the INSERT.

Thanks

Mark

View Replies !   View Related
Max Value For Sequence Table
Hi,
How do I determine the max value for Sequence table in SQL Server?
Regards

View Replies !   View Related
How To Insert Only Unique Data In Database
hello,
i have two datasets. i want to insert all data from one dataset to other.
i am using this:DataSet old = new DataSet();
download dd = new download();old = dd.contactlist("select", "admin", "001");
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Project1ConnectionString"].ToString());
try
{string cmd = "select * from contact";
SqlDataAdapter danew = new SqlDataAdapter(cmd, conn);DataSet dsOld = new DataSet();DataSet dsNew = new DataSet();
dsOld = old.Copy();
danew.Fill(dsNew);
 DataTable dtOld = dsOld.Tables[0];DataTable dtNew = dsNew.Tables[0];
 
//int i;foreach (DataRow objRow in dtOld.Rows)
//for(int i=0; i<dtOld.Rows.Count; i++)
{DataRow row;
row = dtNew.NewRow();
 row["company"] = objRow["company"];
row["cust_no"] = objRow["cust-no"];row["sman"] = objRow["sman"];
row["first_name"] = objRow["first-name"];row["contact_title"] = objRow["contact-title"];
row["type"] = objRow["type"];row["contact_loc"] = objRow["contact-loc"];
row["cust_name"] = objRow["cust-name"];row["addr1"] = objRow["addr1"];
row["addr2"] = objRow["addr2"];row["city"] = objRow["city"];
row["state"] = objRow["state"];row["zip"] = objRow["zip"];
row["territory"] = objRow["territory"];row["phone"] = objRow["phone"];
row["fax"] = objRow["fax"];row["extension"] = objRow["extension"];
row["email"] = objRow["email"];row["rec_key"] = objRow["rec_key"];
 
dtNew.Rows.Add(row);
}DataSet nds = dsNew.GetChanges();SqlCommandBuilder bld = new SqlCommandBuilder(danew);
danew.Update(nds);
}
here rec_key is the primary key.
this code works fine but it will insert all data from one dataset to other each time i click a button but i wanted that if data with a rec_key already exists will not insert. only unique values of rec_key will be inserted.
 i am using this code for this:foreach(DataRow rr in dtNew.Rows)
{
if (rr["rec_key"] == objRow["rec_key"])
{
 Response.Write("same");
}
else
{
 dtNew.Rows.Add(row);
}
}
but this doesn't work.
please guide me how can i do this.
thanks

View Replies !   View Related
Unique Index Conflict On Insert Into
I get this message when doing an insert into :

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.ElementLocalCharacterised' with unique index 'ElementLocalCharacterised_uq'.
The statement has been terminated.

Now my question is : does this error mean that all inserts are done, despite those where the unique key produced a conflict? Or does it mean that none of the inserts are done (all are rejected because at least one gave a conflict) ?

If the second is the case I would like to know if it is possible to execute the insert into in a way that all inserts are done despite those that raise a conflict?

Thanks a lot for any suggestion !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

View Replies !   View Related
Inserting Values In A Table In A Sequence
i have a table whose Primary Key is "UserID". the sample "UserID"  are M1,M2,M3,M4,B1,B2,B3 .
i want that when i insert a valuse "M4" in the table ,by pressing Submit Button.
it should not be at the end or at the start of table.
Rather it should be next to  M3. like the following
M1
M2
M3
M4
M5
B1
B2
B3
i need the C# code of how to do that !!!!
Thanks

View Replies !   View Related
How To Get The Sequence Number Without Using Temp Table
Here is my problem:

I have a table with following columns:

PersonID    FirstName    LastName
102                  John             Ben
103                  Josh              Parker
104                  Mark            Ben

Now if I type SELECT * FROM Person WHERE LastName = 'Ben' these two records will be displayed

PersonID    FirstName   LastName
102                   John            Ben
104                   Mark           Ben

But I want this to return with one additional Sequence column like this:

New Column   PersonID    FirstName   LastName
          1
              
102                   John
           Ben
          2
             
104                   Mark
          Ben

How can I add this so called "New Column" ?
  

View Replies !   View Related
How To Return Primary Unique Index Key On Insert
Hi,

I am making a program in Visual Basic .NET with SQL Server 2000.

I have a table "MyTable" with a primary key named "Id". The primary key is 'Create Unique' checked and 'Index' selected. When I insert all the fields required, except "Id" of course, I need the new record's "Id" in my VisualBasic program, but I don't know how...

I must do one of them, but don't know how either of them:

-Create a trigger on insertion that will send to the user that sended the insert command the "Id" of the record just created.

or

-get the command in Visual Basic that will send the Insert command with a return field ("Id")

Thanks in advance,
Sebastien Anselmo

View Replies !   View Related
Wat's The Syntax To Insert Unique Identifier Data?
May I know what does the syntax of inserting data into a field of type Unique Identifier look like?

[code]
INSERT INTO THAI_MK_MT_Log(GUID, Status) VALUES ('2331486348632', 'S')
[/code]

The "2331486348632" is to be inserted into a unique identifier field.
If i coded the insert statement as the above, I got an error saying that "Syntax error converting from a character string to uniqueidentifier".....

Can anyone help?

View Replies !   View Related
Sequence Number In A Year Without Auxiliary Table
Hi,I've
this problem. I've a table to manage spedition with an identity column
(id), a year field and a numberInYear field. The identity is the pk of
the table and I want to make the numberInYear growing sequentially and
restart every change year. I don't want to make a table to manage this
sequence.I thought this alghoritm:1) Extract the max identity value (id) of the year before this, before the insert of the new record;2) Insert the new record;3) NumberInYear = the id just inserted minus the max id extract before.The
problem is the NumberInYear cannot be null, so I've to know the id of
the record inserting before the inserting itself... Is it possible?

View Replies !   View Related
Generating Sequence Numbers In Target Table
Hi,
What transformations can be used to generate sequence numbers in a data flow?

View Replies !   View Related
SP Causes The Error Violation Of UNIQUE KEY Constraint Cannot Insert Duplicate Key
The following SP causes the error "Violation of UNIQUE KEY constraint 'AlumniID'. Cannot insert duplicate key in object [table name].
The statement has been terminated." AlumniID is the table's PK and is set to autoincrement. I'd appreciate any help or suggestions.

1 ALTER PROCEDURE dbo.sp_CreateUser
2
3 @UserID uniqueidentifier,
4 @UserName nvarchar(128),
5 @Email nvarchar(50),
6 @FirstName nvarchar(25),
7 @LastName nvarchar(50),
8 @Teacher nvarchar(25),
9 @GradYr int
10
11 AS
12 SET NOCOUNT ON;
13 --DECLARE @UserID uniqueidentifier
14 --SELECT @UserID = NULL
15 --SELECT @UserID = UserID FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName-- AND @ApplicationId = ApplicationId
16 INSERT INTO [table]
17 (UserID,UserName,Email,FirstName,LastName,Teacher,GradYr)
18 VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr 

View Replies !   View Related
BULK INSERT Ignores UNIQUE Index With IGNORE_DUP_KEY Set?
All,Just want to make sure that I understand what's going on here.I have a table with IGNORE_DUP_KEY set on a unique, multi-columnindex.What I'm seeing is this:1) When performing a BULK INSERT, the UNIQUE index is not beingrespected and rows which violate the unique index are inserted.2) When performing a regular INSERT, the UNIQUE index is beingrespected and rows which violate the unique index ARE NOT inserted.Is this expected behavior.Also, I have some questions, given the index described.Q1) Will a regular INSERT that attempts to insert duplicate data getan error back or just a warning?Q2) How can I set things up so that a BULK INSERT would NOT allowduplicates to be entered into the table?Thanks,Wes Gamble

View Replies !   View Related
Insert Diffrent Columns,Determine Non Unique Fields
andre writes "I have two questions:

1.)How do i determine if fields are not unique??
2) How to update one table with values from another table when the row names are diffrent??

Thank you!!"

View Replies !   View Related
SP Causes The Error Violation Of UNIQUE KEY Constraint Cannot Insert Duplicate Key


I have a table with 0 records. When I try to insert records using a SP, it gives the following error.
Violation of UNIQUE KEY Constraint 'constraint name'. Cannot insert duplicate key in object 'Objectname'.
How do I resolve this.
Thanks.

View Replies !   View Related
Import Data Where Current Data Has Unique Identity
I have data for online catalogue in SQL 7.0. The web grogrammer asked me to add a unique key for reference. I used int datatype with identity seed of 1 and increment of 1. This works fine BUT when I try to import new data I get an error because the csv file has no column and therefore no value for the unique field which will not allow null by definition.

How can I maintain a unique field to act as primary key in my data when
I want to add (and delete) data that doesn't have this field.

I tried adding the uniqueidentifier field but this gives error message.

The only work round is to delete the unigue field altogether and then add the new data and afterwards create a new unique field. At 600000 + lines of data, this is time and memory consuming

Any help appreciated,Thanks, Keith

View Replies !   View Related
How To Archiv Table To Another Table With Unique Number For All Rows Once + Date
need help
how to archiv table to another table with unique number for all rows once + date time (not the second only day time +minute)
i need whan i insert to the another table add2 more fields (unique number, date_time )

this is the table 1i select from
ID fname new_date val_holiday
----------------------------------------------------

111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1

222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3

333 ccc 03/04/2008 4
333 ccc 04/04/2008 4

this is the table 2i insert into
----------------------------------
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 66615/04/2008 17:03
111 aaaa 16/03/2008 1 66615/04/2008 17:03
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 66615/04/2008 17:03
111 aaaa 20/03/2008 1 66615/04/2008 17:03
111 aaaa 21/03/2008 1 666 15/04/2008 17:03

222 bbb 02/05/2008 3 666 15/04/2008 17:03
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3 666 15/04/2008 17:03
222 bbb 05/05/2008 3 66615/04/2008 17:03
222 bbb 06/05/2008 3 666 15/04/2008 17:03
222 bbb 07/05/2008 3 66615/04/2008 17:03
222 bbb 08/05/2008 3 666 15/04/2008 17:03
222 bbb 09/05/2008 3 666 15/04/2008 17:03

333 ccc 03/04/2008 4 666 15/04/2008 17:03
333 ccc 04/04/2008 4 66615/04/2008 17:03

for evry archiv table to another table (insert) i need to get a unique number + date time (not the second only day time +minute)

next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 166715/04/2008 17:15
111 aaaa 16/03/2008 166715/04/2008 17:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 166715/04/2008 17:15

.........................
.....................................................................66715/04/2008 17:15


next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 166815/04/2008 08:15
111 aaaa 16/03/2008 166815/04/2008 08:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 166815/04/2008 08:15

.........................
.....................................................................66815/04/2008 08:15



TNX

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved