A Little Question About Sequences

Jan 3, 2008

hello !

there is this little dumb thing i don't manage to understand about sequences
when i create a table i put the id as the primary key and write something like this




Code Block[Id] [int] IDENTITY(1,1) NOT NULL,


so each time the sequence is incremented by,

but if i delete a line from a table and then insert a new one
instead of inserting the id 3 for example it inserts id = 4

how can i change this definition sothat if i do




Code Block
delete from table
where id=3






and then i want to to



Code Block

insert into table values ...





it gives a this new line




Code Block
id columnA
3 A






and not



Code Block
id columnA
4 A







thanks in advance for the help




View 6 Replies


ADVERTISEMENT

Sequences

Aug 25, 2006

Jamie writes "When Inserting a row on a table where the Primary key is a sequence number. Once inserted is there a key word to find out the Sequence number of the newley Created Row."

View 2 Replies View Related

Sequences

Jan 3, 2008

hi

i'm creating a sql hoilday database and i need to write a sequence but i've been told its wrong but i dont know why, can anyone help?
CREATE SEQUENCE SEQ_HOILDAY_SITES
INSERT INTO Details_of_sites_visited(Code_of_the_sites, Sites_name)
VALUES(SEQ_HOILDAY_SITES.NEXTVAL,'124','Yosemite National Park');

thanks

jessica

View 8 Replies View Related

Sequences W/ Sql Server 2k5

May 31, 2005

Im doing a quickie Access Project for a Fileroom orginization DB.  Generally, Records are addressed by a 7 character unique ID, with all sorts of rules, assigned by another system.  However we need to track Items that for various reasons do not exist in that system. For this, we enter a code that consists of 2 characters of invalid data, followed by a simple incrementing number.  In Oracle, i would do this with a Sequence.  How do I go about this in Sql Server?basicly, I want to write a trigger that says something like: pseudo code wrote:before insert:   if :new.ItemType is Type3 Then-- File local to our file room      :new.FileID = "IV" + getNextLocalNumber().ToString()   end ifNote that the fileID is not a Key in the normal DB Sense.  It is a neumonicly unique identifier w/ some other properties so that people can easily look things up.      

View 5 Replies View Related

Sequences Followed By Triggers

Apr 4, 2008



Hi SqlGurus,


I have created table T1 as

created table t1 (no int not null primary key,name varchar(30))


i have created a sequence for this table


when i give the following insert statement


insert into t1 (name) values ('xyz')


the next sequence val should be inserted in column no


this is done generally in oracle with before trigger


how about here .Can we do this ?



View 3 Replies View Related

Escape Sequences In Stored Procedures

Nov 8, 2007

All,

What is the escape sequence in a stored procedure?

Here is what I'm trying to achieve:

ALTER PROCEDURE Test
(
@Func VarChar(1000)
)

AS
DECLARE @SQL VarChar(8000)
SELECT @SQL = 'SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE ' + @Func

Now, my goal is to add single quote (') before @Func and another one after that. For eg, if @Func is "Test", I want my query to be
SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE 'Test'

and NOT
SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE Test

Any help?

View 8 Replies View Related

Find Missing Sequences By Category

May 22, 2008



I have to identify missing records from the example below.








Category
BatchNo
TransactionNo

CAT1
1
1

CAT1
1
2

CAT1
2
3

CAT1
2
4

CAT1
2
5

CAT1
3
6

CAT1
3
7

CAT1
3
8

CAT1
5
12

CAT1
5
13

CAT1
5
14

CAT1
5
15

CAT1
7
18

CAT2
1
1

CAT2
1
2

CAT2
3
6

CAT2
3
7

CAT2
3
8

CAT2
3
9

CAT2
4
10

CAT2
4
11

CAT2
4
12

CAT2
6
14


I need a script that will identify missing records as below







Category
BatchNo

CAT1
4

CAT1
6

CAT2
2

CAT2
5

I do not need to know that CAT1 8 and CAT2 7 are not there as they potentially have not been inserted yet.

I idealy want a nice clean SQL statement and do not particually want to insert new table's or triggers although views i Can deal with to an extent.

Considerations
up to 50,000 records added per day!!!
Only need script to run once a day and I have insert dates to help me.
Only 12 Categorys
Batch numbers always start at 1 for different categorys

View 13 Replies View Related

Transact SQL :: Use Sequences And Triggers For Identity

Oct 16, 2013

I would like to use sequences and triggers to update table identity field with int value from sequence via before insert trigger. I'm searching on google for a few days and there are no same or identical article about this subject.

Is there any sample how to create table with column Id, Name, Comment and sequence (for generate int numbers for Int field in table) and trigger which will fired before insert and check is inserted Id is NULL and update this field from sequence or nothing if id is set up.

View 15 Replies View Related

Custom Auto Generated Sequences With Server?

Aug 31, 2014

can i create Custom Auto-Generated Sequences with SQL Server like DD-0001,DD-0002,DD-0003...... to DD-000........

View 1 Replies View Related

PredictCaseLikelihood Returns Low Probability For Sequences That Are Very Frequent

Jun 28, 2007

I am working on a text mining application wherein I need to detect unusual/anomalous sentences in text. Certain sentences, that I know occur very frequently, are given a likelihood of 0.2 by PredictCaseLikelihood. Other sentences that are just as frequent get a much higher likelihood (>0.9). I am using the NORMALIZED option. The only significant difference between these sentences is their length. The one with the lower likelihood has only 2 words in it, whereas the one with the higher likelihood has more than 10 words. The problem is that the shorter sentences end up being interpreted as anomalous, when in fact they are'nt. Any suggestions?

View 2 Replies View Related

Destination File With Multiple Record Types And Sequences - Mainframe-like

Jul 20, 2007

Howdy all,



I've seen several posts about reading and writing files that have different record types with varying column metadata. My particular file has 11 record types plus several header types and looks something like:



<Header1>

<Header2>

<Detail01-#1>

<Subdetail02>

<Subdetail03>

...

<Detail01-#2>

<Subdetail02>

<Subdetail03>

...

...



Since i need to get different detail and subdetail records, i can't really use the technique of 3 dest file connection managers found in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=87269&SiteID=1



I've tried using an exec sql to get the main detail records and then a forech ADO en umerator that would get the subdetails, but it all seems so kludgy. I'm starting to think that I should just write the bulk of the file creation code in a c# app instead of trying to smush this into SSIS. Opinions? Am I missing some trick in SSIS?



TIA,



-Peter

View 7 Replies View Related







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