declare @newAnswerId int declare @newQuestionId int
set @QuestionName='New Question'
BEGIN TRANSACTION Q1
--Creates New QuestionId with AnswerId 0 INSERT INTO Questions(QuestionId,Name,AnswerId) SELECT 1 + COALESCE(MAX(QuestionId), 0),RTRIM(@QuestionName),0 FROM Questions
--QuestionId just now created SELECT @newQuestionId=QuestionId FROM Questions WHERE Name=@QuestionName
BEGIN TRANSACTION QA1
--Create an AnswerId INSERT INTO Answers(AnswerId) SELECT 1 + COALESCE(MAX(AnswerId), 0) FROM Answers
--AnswerId just now created(I hope not the best way to do like this) SELECT @newAnswerId=MAX(AnswerId) from Answers --is it the best way to call statement like this or any other way better than this
--update Questions Table with this new Answerid UPDATE Questions set AnswerId=@newAnswerId where QuestionId=@newQuestionId
COMMIT TRANSACTION QA1 COMMIT TRANSACTION Q1
I think the second Transaction is not locking the table.so some how i should be able to get the newly create AnswerId
i can't use the identity column in my tables
Can some one please have a look at it and suggest me how do we go about it..
Requirements •ALPHANUMERIC FORMAT – > AA00AA00………..ZZ99ZZ99 Last 8 bytes will alternate between 2 byte alpha/2 byte numeric •Generate from Alphabets – A through Z Numbers -0 to 9 •Generate Unique Sequence (No Duplicates). •Must Eliminate letters I and O Output Expected •AA00AA00………..ZZ99ZZ99 •Using 24 alphabets & 10 digits , 24*24*10*10*24*24 = 3 317 760 000 records
Below is my Sql Function -
CREATE function [dbo].[SequenceComplexNEW] ( @Id BIGINT ) Returns char(8) AS BEGIN DECLARE @OUT AS CHAR(8)--,@Id as Bigint
Hi all anbody can help me writing sql code for this. All i need is to generate sequence basing on id_no Ex: if ID=ABC(twice) in seq_col as abc --1 abc ---2 Tables which I have Uniques_No ID_NO SEQ --------------------------------------------- 1 ABC 2 ABC 3 ABC 4 BBC 5 BBC
Expected results as below : ------------------
Uniques_No ID_NO SEQ --------------------------------------------- 1 ABC 1 2 ABC 2 3 ABC 3 4 BBC 1 5 BBC 2
I'm trying to do a simple insert into a table, something like this:
insert into sometable (ID, somecolumn) select 'Task-ID', somevalue from SomeOtherTable where something = 'someothervalue' (or something to that effect)
So, the SELECT would generate something that looks like this:
What I don't know is, how do I programatically generate the number sequence? Note: I do not have admin rights to the table, i.e. I cannot just change a column to IDENTITY.
Also the 'Task-ID' must remain part of the ID; in other words, I can't just generate a GUID, and it needs to be easily identifiable.
What I'm hoping to do is rewrite my SQL like this:
insert into sometable (ID, somecolumn) select 'Task-ID.' + (generated seq #), somevalue from SomeOtherTable where something = 'someothervalue'
Requirements • ALPHANUMERIC FORMAT – > AA00AA00………..ZZ99ZZ99 Last 8 bytes will alternate between 2 byte alpha/2 byte numeric • Generate from Alphabets – A through Z Numbers -0 to 9 • Generate Unique Sequence (No Duplicates). • Must Eliminate letters I and O
Output Expected • AA00AA00………..ZZ99ZZ99 • Using 24 alphabets & 10 digits , 24*24*10*10*24*24 = 3 317 760 000 records
Below is my Sql Function -
CREATE function [dbo].[SequenceComplexNEW] ( @Id BIGINT ) Returns char(8)
Hello everybody,I was configuring a SqlDataSource control using SQL Authentication mode.I first added a database file (testdb.mdf) through Solution Explorer-Add New Items. Then through Database Explorer I created a table named "info"Then while configuring the SqlDataSource control I used the SQL Authentication mode and attached the "testdb.mdf" database file.Test Connection showed success. But when I hit the Ok button of the wizard it displayed the following error message:Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance.While configuring the SqlDataSource control I clicked "New Connection". Under Data Source section I tried both Microsoft SQL Server and Microsoft SQL Server Database File. And in both the cases I attached a databese file(testdb.mdf). Plz enlighten me on this.Thanks and Regards,Sankar.
Hi, How can I generate a sequence No. using a simple SELECT statement.
like
declare @key set @key = 1
SELECT @key, e.name from Employee
Now I want to display name of the employee and Key value which should get incremented automatically for each employee.. Is there any way? Please help me..
i've been asked to write a sql sequence for a database i'm building but i haven't been using SQL very long and i have no idea how to write a sequence. Does anyone know anything about sql sequences?
Hi,I need little help with Ms SQL Server 2000. I would like to know how tocreate sequence or something like that. I want to have an automatic counterfor each row in one entity, so then I can do something like this:INSERT INTO table VALUES (use sequence(something), value, value, .... )Can you please help me?thxTomas
I have a SQL 2005 stored procedure to generate an email when passed parameters such as receipient, subject etc One of the paramteres passed to it is @body which is the body text of the message. I want to be able to add a couple of blank lines and then some footer information. This is working right now except I can't find the right way to add newlines into the string within the store procedure, so my footer information just tags right on after the bodytext. I have tried but that literally adds the two characters and n Can anyone advise how to generate newlien sequences in T-SQL. Regards Clive
Hi, I've a question, if I've the LSN (Log Sequence Number) of a transaction, keep with the program "Log Explorer", can I know which is the IP of the user that have do the transaction (perhaps serching in a log file of the Win 2000 Server) ? 10x, Clara
I would like to know how it is possible to create a sequence with sql server 2000 With Postgres SQL i create the sequence essai : create sequence essai START 1; but i don't know what is the sql command.
SELECT @ddo = a.DDO_DSCR_SHORT FROM dbo.DIM_DDO a JOIN dbo.Temp_ABR_HDR b ON a.DDO_ID = b.DDO WHERE b.SESSIONID = @sessid
SELECT @ay = AY FROM dbo.Temp_ABR_HDR WHERE SESSIONID = @sessid
-- set the default seq_no SELECT @seq_no = 1 -- get the max abrid. if no record return the seq_no will be 1 SELECT @seq_no = convert(integer, max(right(abrid, 4))) FROM dbo.ABR_HDR WHERE left(abrid, 7) = @ay + @ddo
-- convert @seq_no to string prefix by 0 SELECT @abrid = @ay + @ddo + right('0000' + rtrim(convert(char(4), @seq_no)), 4)
SELECT @ddo = a.DDO_DSCR_SHORT FROM dbo.DIM_DDO a JOIN dbo.Temp_ABR_HDR b ON a.DDO_ID = b.DDO WHERE b.SESSIONID = @sessid
SELECT @ay = AY FROM dbo.Temp_ABR_HDR WHERE SESSIONID = @sessid
-- set the default seq_no --SELECT @seq_no = 1 -- get the max abrid. if no record return the seq_no will be 1 --SELECT @seq_no = convert(integer, max(right(abrid, 4))) --FROM dbo.ABR_HDR --WHERE left(abrid, 7) = @ay + @ddo
-- convert @seq_no to string prefix by 0 --SELECT @abrid = @ay + @ddo + right('0000' + rtrim(convert(char(4),@seq_no)), 4)
Hi, I am migrating a project from Oracle to SQLServer and must use the same DDL. The entities have compound primary keys. In Oracle I ensure the compound key is unique by using a sequence table to generate one of the values for the compound key. Does SQL Server have sequence tabels or does anybody know a way to get it do something similar? Thanks
is it possible to generate a number sequence in a query (without using loop). I want the output to look as ------- ID ------- 1 2 3 4 5 6 7 8 9 .... upto the last number I give in the query
I am not sure about the ANSI/ISO rules on this? We have a SEQUENCE and insert it into a table.
CREATE SEQUENCE Cheque_Seq AS INTEGER START WITH 1 INCREMENT BY 1; CREATE TABLE Cheque_Book (cheque_seq INTEGER*NOT NULL PRIMARY KEY, cheque_amt DECIMAL (15,2) DEFAULT 0.00 NOT NULL);
If I do an insertion with the SEQUENCE in a transaction and then ROLLBACK the transaction, what happens to the sequence value? My thought is that the value should reset to the state it was in before the transaction (basic definition of a ROLLBACK). But it does not in the products I have looked at.
I need to create a function that will return a sequence number. The reason I need this to be a function is that I will use the function to create a view. In Oracle I would do something like this:
create sequence seq;
create or replace function f return number as l_seq number; begin select seq.nextval into l_seq from dual; return l_seq; end; /
create or replace view v as select f from dual;
Since Sql Server does not have a sequence object I have created a sequence table with a identity column and a procedure that inserts a dummy variable and returns the identity id. This works ok but to get my sequence number I must declare a variable, execute the procedure and select the return value. I need the procedure to be a function so that I can use it to create my view. Since I cannot use DML in a function and I cannot call a procedure from a function I am stuck. Do I have to create an extended procedure ? Any help is appreciated
hey all, im having some problem to do this. i have this one table, that has sequence for other table. table : tblpicksequence (this sequence is dynamic eg : Non=1, cons=2, RET=3) picktypepicksequenceitemref RET1x1 Non2x1 Cons3x1 i need to select record from other table depend on tblpicksequence sequence table : tbldetail idRETNonCons 1001 2110 3100 4 001 so base on tblpicksequence, RET=1, NOn=2, Cons=3, i need to list the record from tbldetail :- i.list all RET=1 ii.if (i) not exists list all Non=1 iii.if (ii) not exists list all Cons=3 i expect :- idRETNonConspicksequencepicktype 21101RET 31001RET
Hello to every body I need to know about sequence of execution in a select command. I have a sql command that use a function.some thing like: select id, function(item) from tbl where conditions... I want to know that if my function on item execute before where section or vise versa. I try to explain it more. I want to know that sql engine fetch rows accordin to where clause and then execute my function or execute my funtion and then fetch the rows according to where clause.
If you have a document or some thing that explain about sql engine and sequence of execution please let me know.
I have a table in which there is 5 column ...one with numbers like...1,2,3,4,..20...and one column is with description....and other column with other details...
Now I want to disply my results in sequence followed by 1,2,3,4,5,17,18,15,16,10,11,20 with all other columns...so can anybody suggest me what to do..?
Hello all,I´m currently using a SQL Serve 2K. Would like to do a selectwhich returns the row number - this should not be physically stored inthe database. So for example, I would like to do a query against theCUSTOMER table and receive:* rowID || name1 Evander2 Ron3 Scoth4 JaneI don´t want to store the ID, because if I change the order byclause, the sequence may modifiy, and, for another example, having thesame set of data, I would receive:* rowID || name1 Scoth2 Ron3 Jane4 Evander could someone help me ?best regards,Evandro
Hello,Since SQL Server has no sequence generator, I wrote my own. (I claimno ownership of it as it is closely modeled after earlier discussionson this topic.) I have included the sql statements below.While it works independently on its own, It seems to lock in multi-userenvironments or in nested-transactions. It s funny really: I have mymain transaction, but the sequence generator below forces anothertransaction, which I do not really care for. I cannot remove the extratransaction from the sequence generator because I would like to discardany values it retrieved, regardless of whether the main transactionsucceeded or failed.Any suggestions?--------------------------------------------------------------------------------create table my_sequence (name varchar(10), seq int identity (1, 1))godeclare @next intbegin transactionupdate my_sequence set seq = seq + 1 where name = 'abc';select @next = seq from my_sequence where name = 'abc';commit transaction---------------------------------------------------------
