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.







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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Update Column With Sequence Number Every Time
hello friends

i want to update one column from my table regularly on sequence number i.e. 0,1,2,3,4,5

i created procedure but it is not working as per my output

declare @IndexIDGen int
declare @ID int
set @ID = 0
update temp_test set indexid = NULL
declare IndexIDGen cursor for select indexid from temp_test
open IndexIDGen
FETCH Next from IndexIDGen into @IndexIDGen
while @@fetch_status = 0
begin
update temp_test set indexid = @ID where indexid is null

set @ID = @ID + 1
print @id
fetch next from IndexIDGen into @IndexIDGen
end

close IndexIDGen
deallocate IndexIDGen


where i am going in wrong direction ??????

T.I.A

View Replies !   View Related
How To Create A Sequence Generator Number In SSIS
Hi,

I got 5000 rows in source and when i am sending the data to destination it has to create a sequence generator number for each row.

Can any one help me which transformation do i need to take for doing this in SSIS.

 

 

 

 

 

 

 

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
Incrementing An Invoice Number
Hi there
 
I need a bit of generic advice about a project I am currently working on which is slightly out of my comfort zone. I€™m using SQL 2005 and VB.Net to develop an application that produces invoices for a number of different countries. The issue is that the data is arriving from an AS400 system so the tables I am working from are a little strange! This results in a fair degree of work tweaking the data into a format that is useful for my application.
 
To simplify the explanation each invoice in its basic form consists of the invoice data and the line items (so there is nothing new here). What is unusual is that the invoice data has an invoice number but then an additional invoice number specific to the country it is destined for. The initial invoice number arrives with the data from the AS400 but the secondary number is generated according to a number of business rules.
 
I have written an update query in a stored procedure which updates the invoice data with a number of values from a secondary table and a number of calculated values. There are several functions, one in particular which looks up the new invoice number from a secondary table based on specific criteria (country of destination being one of them) and I had intended to write an update query, called from within this function which then increments the invoice number but I have since found out that you cant do an update/delete from a function?
 
I can€™t add an update query to increment the value after the initial update query as this results in the stored procedure running through and adding all the invoice numbers (which are the same) and then the number being incremented at the end.
 
I assume I can€™t call an update query from within the initial update query so that the increment takes place after each loop of the initial update (if I can what would the syntax be?).
 
I have tried writing a trigger which completes the increment when the invoice data is updated. I haven€™t used triggers before but once again it appears as if the first update query is running through and completing all the updates and then the trigger fires incrementing the number. Or have I just got this wrong?
 
I guess what I need is a trigger which fires after each row of the table is updated which was how I assumed it should work!
 
Ironically I could write this in VB.Net with ease but for speed we need it within the database.
 
What is the best way to increment my invoice number?
 
I€™m sorry of this is general in nature but im really looking for some advice as to the best approach to deal with this issue.
 
Martyn Fewtrell

View Replies !   View Related
Generating Sequenced Line Numbers For Each Invoice Number
Can you help me with SQL issue I€™m stuck on? 
I wish to take source data that looks like this:





Invoic_num

Line_num

 

 

 


6658

0

 

 

 


6658

2

 

 

 


6658

8

 

 

 


7721

2

 

 

 


7721

3

 

 

 
 
And rebuild the line numbers like this:





Invoic_num

Line_num

 

 

 


6658

1

 

 

 


6658

2

 

 

 


6658

3

 

 

 


7721

1

 

 

 


7721

2

 

 

 
 
This seems completely impossible to me. So I was thinking that maybe a second procedure using update could be run against the table after the initial build.

View Replies !   View Related
OPENROWSET (INSERT) Insert Error: Column Name Or Number Of Supplied Values Does Not Match Table Definition.
Is there a way to avoid entering column names in the excel template for me to create an excel file froma  dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
 Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'  
IF @File_Name = ''      Select @fn = 'C:Test1.xls'     ELSE      Select @fn = 'C:' + @File_Name + '.xls'        -- FileCopy command string formation     SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn     
-- FielCopy command execution through Shell Command     EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT        -- Mentioning the OLEDB Rpovider and excel destination filename     set @provider = 'Microsoft.Jet.OLEDB.4.0'     set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn   
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet1$]'')      '+ @sql1 + '')         exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet2$]'')      '+ @sql2 + ' ')   
 
 

View Replies !   View Related
Error Number For Duplicate Column Value During An Insert For A Unique Column ?
I have a table in which a non-primary key column has a unique index on it.
If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
 

View Replies !   View Related
To Create Invoice Report Which Control Is Suitable
I need to create an invoice report, each page represents a single invoice. like a form report.

 

Which control is good, i am new to reports. i have used table control for few of our reports still learning.

 

Thank you very much for your help.

View Replies !   View Related
How To Create A View With An Auto Number Column?
I have a View created from 2 tables.  How do I add an autoindex (0,1,2,3,..) to a new column?

View Replies !   View Related
How To Create A New Column And Insert Values Into The New Column
Can anyone assist me with a script that adds a new column to a table then inserts new values into the new column based on the Table below. i have included an explanation of what the script should do.
 
Column from
Parts Table           Column from
                                MiniParts           New Column in
       (Table 1 )              (Table 2 )       MiniParts (Table2)





PartsNum

MiniPartsCL

NewMiniPartsCL

 


 


1

K

DK

 


1

K

K

 


1

Q

Q

 


0

L

L

 


0

L

LC

 


0

D

G

 


0

S

S

 
 
I have 2 tables in a database. Table 1 is Parts and Table 2 is MiniParts. I need a script that adds a new column in the MiniParts table.   and then populate  the  new column (NewMinipartsCL) based on Values that exist in  the PartsNum column in the Parts Table, and MiniPartsCL column in the MiniParts columns.

The new column is NewMiniPartsCL. The table above shows the values that the new column (NewMiniPartsCL) should contain.
 
For Example
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "DK" ( as shown in the table above).
 
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "K" ( as shown in the table above). etc..

View Replies !   View Related
Insert Error: Column Name Or Number Of Supplied Values
In SQL 2005 I created table as

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[logMsg](
[logMsgID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[msg] [nvarchar](256) COLLATE Latin1_General_CI_AS NOT NULL,
[AppId] [int] NULL,
CONSTRAINT [PK_logMsg] PRIMARY KEY CLUSTERED
(
[logMsgID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

and trying to insert values with

INSERT INTO [ProxyDB].[dbo].[logMsg]
([msg]
,[AppId])
VALUES
('Text Test',1)

Getting error message:

Msg 213, Level 16, State 1, Procedure TrgInslogMsg, Line 14
Insert Error: Column name or number of supplied values does not match table definition.


Urgent help is required

View Replies !   View Related
Row Sequence Number
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

View Replies !   View Related
Sequence Number Help
I have the following stored procedure:

CREATE PROCEDURE dbo.ABR_HDR_INSERT

@id int output,
@status int,
@mode int,
@sessid varchar(100)

AS

declare @ay char(4)
declare @ddo char(4)
declare @abrid varchar(50)
declare @seq_no int

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)


Insert into dbo.ABR_HDR (ABRID, HDR_MODE, HDR_DDO, HDR_AY, HDR_REQUESTOR, HDR_DT, HDR_SUBJECT, HDR_DESCRIPTION, HDR_STATUS)

SELECT

@abrid,
@mode,
DDO,
AY,
REQUESTOR,
DT,
SUBJECT,
DESCRIPTION,
@status

FROM dbo.Temp_ABR_HDR


SELECT @id = @@identity

return @id
GO

ABRID gets inserted as a <NULL> value. I can't figure out why? If I comment out the following then ABRID will insert without the sequence number:

CREATE PROCEDURE dbo.ABR_HDR_INSERT

@id int output,
@status int,
@mode int,
@sessid varchar(100)

AS

declare @ay char(4)
declare @ddo char(4)
declare @abrid varchar(50)
declare @seq_no int

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 @abrid = @ay + UPPER(@ddo)

Insert into dbo.ABR_HDR (ABRID, HDR_MODE, HDR_DDO, HDR_AY, HDR_REQUESTOR, HDR_DT, HDR_SUBJECT, HDR_DESCRIPTION, HDR_STATUS)

SELECT

@abrid,
@mode,
DDO,
AY,
REQUESTOR,
DT,
SUBJECT,
DESCRIPTION,
@status

FROM dbo.Temp_ABR_HDR


SELECT @id = @@identity

return @id
GO

So, the code that sets the sequence number is what is causing the <NULL> value.

Any help is appreciated.
Thanks,
-D-

View Replies !   View Related
Sequence Number
suppose I have the following table grouped by memid

memiddx
3455
3322
3232
433
43434

I want to attach sequence number for each unique value of dx per memid as
below

memiddxSEQ
34551
33222
32323
4331
434342

I am using a cursor right now and it takes a lot of time if my table is large.
Is there a more efficient way of doing this.

Thanks much.

View Replies !   View Related
Number Sequence
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

View Replies !   View Related
Sequence Number
In SQL 6.5 object dependencies window, what does the sequence number means?

Thanks,

Sam

View Replies !   View Related
Log Sequence Number
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

View Replies !   View Related
Create Trigger To Check Values At Insert/update
I have never used triggers before and I have tried to solve one problem. If I have the column "currency" in a table and want to make sure that the entered value i valid in relation to another table that contains valid currency formats, I did it like this:

---------------------------------
CREATE TRIGGER [trigger_checkCurrency] ON [dbo].[Client]
FOR INSERT, UPDATE
AS
declare @currency as char(50)
declare @country as char(50)

declare cur cursor for SELECT currency, country
FROMinserted

OPEN cur
fetch cur into @currency, @country
WHILE @@FETCH_STATUS = 0
BEGIN
if not exists(select * from listinfoid where listname = 'currency' and listid = @currency)
begin
set @currency = (cast(@currency as varchar (3)) + ' is not a valid currency')
CLOSE cur
DEALLOCATE cur
RAISERROR (@currency,16,-1) with log
return
end
if not exists(select * from listinfoid where listname = 'country' and listid = @country)
begin
set @country = (cast(@country as varchar (3)) + ' is not a valid contry')
CLOSE cur
DEALLOCATE cur
RAISERROR (@country,16,-1) with log
return
end
else
fetch cur into @currency, @country

END
CLOSE cur
DEALLOCATE cur
update Client set currency = UPPER(currency), country = UPPER(country)
---------------------------------



I use a cursor to handle multiple rows in an update query.
(SQL2000-server)

Is there an easier och better way to do this?
I´m a bit unsure of this code.

Thanx!

/Erik

View Replies !   View Related
How Create Trigger Stop Update Delete And Insert
How to create trigger to stop the delete , updation and insert in the table of database ....

How can i stopped .......................I want to apply on whole table of database

Pls help me out.

Yaman

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
Sequence Number Generation
Does anyone know an efficient method for generating a sequence number in the following form?

Starting with 2 columns
1 2
----
A X
A Y
B X
B Y
B Z
C X

I want to then generate a third column as follows:
1 2 3
-------
A X 1
A Y 2
B X 1
B Y 2
B Z 3
C X 1



The purpose being so that I can easily identify the previous row within a Column1 group. So given column1=A and column2=Y I know that the previous row is Column3 - 1 where column1 = A. Therefore I will be able to join to the previous result of any row within any group quickly for future calculations.

Any ideas? Thanks.

View Replies !   View Related
Generating Sequence Number....
Hi,
I got stuck with a trivial issue. I have a table named T1 having column as Col1 (INT). I have another table named T2 having columns Col1(Int), COl2(Int). Following are the reacords in my table T1.


Col1
----
1
1
1
1
2
2
3
3
3
3
3
4
5
6
6
7

And I want to migrate the data from T1 to T2 where the Col1 data of T1 will get migrated to Col1 of T1 and after the migration is done the T2 should have the data like this.........

Col1 Col2
---- -----
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 2
3 3
3 4
3 5
4 1
5 1
6 1
6 2
7 1


Thanks In Advance,
Rahul Jha

View Replies !   View Related
Generating Sequence Number....
Hello folks,
I have a table where the records are like followings.

ID Value
---------------
1 aa
1 aa
1 aa
1 bb
1 bb
1 bb
1 bb
1 cc
2 pp
2 dd
2 dd
3 qq
4 aa



I need to include one column "SeqId" which will be having value based on the 1st 2 columns (ID + Value). I am struglling with this since last few days. Can I request you guys to help me out in this reagard.

ID Value SeqId
-----------------------------
1 aa 1
1 aa 2
1 aa 3
1 bb 1
1 bb 2
1 bb 3
1 bb 4
1 cc 1
2 pp 1
2 dd 1
2 dd 2
3 qq 1
4 aa 1




Thanks,
Rahul Jha

View Replies !   View Related
Generating Sequence Number
Hello,

I need to know how to generating a sequence number, for example, from 300,000 to 900,000 without skipping any number due to failure. For example, if user 1 request a number then he/she will get 300000 in a transaction. User 2 will get 300001. How ever user 1's transaction fails, then the next request should get 300000. Is it possible to do this in SQL2K? If so, how do I create a table that and stored procedure that can do this.

Thank you so much.

NK

View Replies !   View Related
Generate Sequence Number In Sql???
Hi,
I have written the following StoredProcedure





Code:

create Procedure spCreateQuestion(
@QuestionName varchar(30)
)

as

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..

View Replies !   View Related
Getting Sequence Number In 2000
I have a table like this

create table
#invoice (invoice_no varchar(5), invoice_amount int)

INSERT INTO #invoice
SELECT 'A', 100 UNION
SELECT 'A', 200 UNION
SELECT 'A', 300 UNION
SELECT 'B', 400 UNION
SELECT 'B', 500 UNION
SELECT 'B', 600 UNION
SELECT 'B', 700


Now I want a output like this

INVOICE_NO AMOUNT SEQUENCE_NO
A 100 1
A 200 2
A 300 3
B 400 1
B 500 2
B 600 3
B 700 4

The sequence_no should set back to 1 on change of invoice_no
these are items in a an invoice. they just want a sequence_no

I can do this using cursors, but if anyone can help with WHILE LOOP
would be great

THANKS






-----------------------------------------------------------------------------------------------
Ashley Rhodes

View Replies !   View Related
Getting Sequence Number Generations
I am newbie to SQL Server 2005.

Can any experts please give me code for how to generate automatic sequence numbers in sql server 2005?

I want to use this generated sequence number as a primary key to update the records.

Thanks in advance,
Vani

View Replies !   View Related
Sequence Number Generation
Hi,
I have an error table that is to be updated by more than one package.
There is a sequence number generated in the error table. It is generated by using the max value of the previous data present in the table.
When more than one package runs parallely, conflict occurs in generating the sequence number.
How can this be handled?

View Replies !   View Related
Sequence Number For Records
Hi...
 
I have Sql statement more like this
 
SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeA a
UNION
SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeB a
 
 
                   rec_num      employeeID    employeeName      employeeDepartment


    1                 777           Mike                              HR
    2                 888           Susy                              HR
    1                 111           Smith                            TECH
    2                 222           John                              TECH
    3                 333           Lenny                            TECH  
 
 

How do i get sequence number for all of this records. The rec_num reset for every statement. I want the records numbering for second statement continue from first statement so that it can be like this :
 
                   rec_num      employeeID    employeeName      employeeDepartment


    1                 777           Mike                              HR
    2                 888           Susy                              HR
    3                 111           Smith                            TECH
    4                 222           John                              TECH
    5                 333           Lenny                            TECH  

View Replies !   View Related
Add Sequence Number To Data
 

Hi all, I need some your help.
I want to add sequence number to data flow below:

Cust_Name | Month
=======================
Chonnathan | 5
Chonnathan | 4
Chonnathan | 7
Derec | 3
Derec | 9

and the result i need is:

SEQ_nbr | Cust_Name | Month
================================
1 | Chonnathan | 4
2 | Chonnathan | 5
3 | Chonnathan | 7
1 | Derec | 3
2 | Derec | 9

How can I do it in Integration Service to show like the above?

Thank you for your respones,
Chonnathan

View Replies !   View Related
Auto Number Sequence
How can I create a number sequence starting at a certain number and continue on for the number of records I have.

For example I have 3000 records in my table and a field named I created called RecordId which I'd like to start at number 1 and goto 3000 (or maybe even start at 9000 and goto 12000 or however many records there are).  

In my pseudo SQL code Im guessing it would be something like...

select * from Incident

update Incident
       set RecordId( i=9000; i<=Number of Records in Table; i++)

 

Whats the easiest way to do this?

View Replies !   View Related
SQL Server Sequence Number
Hi,
 
Does any one have information about how 'large' the SQL Server Sequence Number generator can go,
like 1 to 1 billion, or to 12 zeros, etc?
 
Thanks.
 
--John
 

View Replies !   View Related
How To Create Trigger For Multi Insert Employees Update Tb Employee Once
how to do this
i have table of employee ,evry employee have a unique ID "empid"
empid     VAL_OK
--------------------------
111            0
222            0
333            0
 
now insert multiple insert   to my work_table  shifts  for all month for evry employee
like this
(this is work_table)
empid      date                 val
--------------------------------------------------
111        01/02/2008         1
111        02/02/2008         2
...............
111        29/02/2008         5
--next employee
222        01/02/2008         1
222       02/02/2008          4
...............
222        29/02/2008         6
--next employee
333
--next employee
444
--next employee
555
-------------------------------------------------------------
 

now i need for evry OK insert (for all month)  each employee
go to the TB_Employee
and update each employee once !!
from VAL_OK=0       to  VAL_OK=1
like this
 
empid     VAL_OK
--------------------------
111            1
222            1
333            1
----------------------
like this i know who is the employee have shift for all month and who NOT !
 
i think it like this



Code Snippet
Create trigger for_insert on tb_work
For insert
begin
if @@rowcount = 1
Update tb_employee
Set
val_ok= 1
 
else
/* when @@rowcount is greater than 1,
use a group by clause */
Update tb_employee
set
val_ok= 1
select empid from tb_work
group by tb_work.empid

End
 
 


 

 
TNX

View Replies !   View Related
Creating Alphanumberic Sequence Number
Database in:SqlServer builtin interface with VS05
Using:ASP.NET VISUAL STUDIO 2005
I want to create a unique ID that identifies a row.
Id:STU+(SeqNumber) for Student and
Id:EMP+(SeqNumber) for Employee
[Question]:But how can i create sequence number and store it for use like that of ORACLE

Current Idea:Based on registration info if type is student generate STU+seq num and insert to table along with username and other details and if employee like wise.
So a procedure will receive this from user and append that with seqnumber and stores that in the table

View Replies !   View Related
Chalenge Returning The First Available Number In A Sequence
 Hi all,Ive been trying to figure this out and has proven to be quite difficult for me. Lets say i do a select on all client numbers from a clients table i would want the first available number returned.Given the client table below my query would return 4 because its the lowest number availeble. Thanks. client tableclnum12367 

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
Sequence Number Generating With A Twist
:eek:

I'm having a brain not functioning day - well who am I kidding - more like a year :rolleyes:

I need some help with some sequence numbering and cannot even get my head around the logic I want to use, let alone the actual code.

I have a dataset with 3 fields:

Area
ID
RefNo

This table contains a list of employee ID's by Area and each employee has a RefNo (counter) in each area.

The data comes from 2 different sources and is combined in this table. Some employees had no RefNo already assigned to them so I have entered their RefNo as 10000 in order to ensure they are sorted at the bottom of the list.

The ID's that have RefNo's have to keep the one they have. Therefore, I need to create RefNo's for the ones that currently have RefNo 10000.

These numbers I create have to follow on from the highest RefNo for the Area.

For example:

Area ID RefNo
A Z 1
A Y 2
A X 3
A W 10000
A V 10000
B N 1
B O 10000
B P 10000

So, for Area A, ID's W and V would have to be assigned RefNo 4 and 5, and for Area B, ID's O and P would have to be assigned RefNo 2 and 3.


Hope this makes sense to all.

BTW, am using SQL 2000 at the moment.

Thanks in advance for any help!

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
Generate Sequence Number Basing On Each Id
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

Thanks in advance

View Replies !   View Related
Update Table - Insert New Column
This should be easy for someone, but I just can't seem to find a sample to do this.....I have created a table...CREATE TABLE dbo.test   ( oId int NOT NULL UNIQUE,          test1 varchar(50) NOT NULL PRIMARY KEY                            )Now, I need to go back and simply add another column to the table such as       test2 varchar(50)Not sure if the insert is the way to go and been playing around with various statements but with no luck.Suggestions?Thanks all,Zath

View Replies !   View Related
Create User Only With Permissions, To Select, Insert, Update, Delete, And Exec Sps
Hello, I recently view a webcast of sql injection, and at this moment I created a user, and give dbo to this user, and this same user, is the one I have in the connection string of my web application, I want to create a user to prevent sql injection attacks, I mean that user wont be able to drop or create objects, only select views, tables, exec insert,update, deletes and exec stored procedures.

Is any  easy way to do this?

A database role and then assing that role to the user?

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
How To Extract A Sequence Number From The Group By Count?
Hi,

I have a situation where I have to write a SQL to generate a sequence number from the count of the group by.

Example:

I have src data :

---------------------------------------------------------------------------------------------------------------

SUBSCRID         col1     col2     col 3     col4

1001                  val1        val2      val3    val4

1002                 vala         valb      valc     valc

1001                 ................

1002                 ..........

1001                  ...........

1003                 ................

---------------------------------------------------------------------------------------------------------------

So my result should be :

---------------------------------------------------------------------------------------------------------------

SUBSCRID      SUBSCRCNT       col1          col2       col3       col4   

1001                1                        val1        val2       val3       val4

1001              2                       ...................

1001              3                       .....................

1002             1                       vala          valb       valc       valc

1002              2                     ................

1003              1                    .................

---------------------------------------------------------------------------------------------------------------

Any help on how to write a query will be appreciated?

Thanks,

Venkat

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 same Invoice No.  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
View, Insert, Update An Image Column
How to view, insert and update an Image column
Using Visual Basic 6.0

View Replies !   View Related
Insert / Update Trigger Based On A Column's Value
I am new to triggers and surely could use some help.

I can create a trigger to insert related records based on the main tables ID and insert that value into other related tables fine... but...

How do I create a trigger that can insert a record into one table for a columns given value and then  insert a record into another table for another given value?

For instance:
New row...

Table1, Column1 (PK) has a value of 101
Table1, Column2 has a value of 'Blue'.

// When a new row is created in Table1 and Column2 has a value of 'Blue'...
I want to insert a new row into Table2 -  with Table1 Column1's value.

// Now if Table1, Column2 has a value of 'Red' when the new row was created...
I want to insert a new row into Table3 - with Table1 Column1's value. Not Table2

This has to be inserted into one or the other tables based on column2's value, not both.
Then I want to populate the other related tables (Table4, Table5) with the regular insert statements based on Table1 Column1's value.

This (the conditional part above) has to work with an update to Table1 also.
So if someone came back to that record and changed Column2's value from 'Blue' to 'Red', it would have to delete the appropriate record in Table2 and then insert the new row into Table3 and visa-versa.

Can I do this with one trigger?

Thanks

View Replies !   View Related
CREATE TABLE For BULK INSERT: How To Set The Decimal Number Right In &&"Col&&" ?
Hi all,
 
I executed the following sql code in my SQL Server Management Studio Express (SSMSE):
-- myCSV1.sql --

USE MyDatabase

GO

CREATE TABLE myCSVtable

(

Col1 int,

Col2 nvarchar(25),

Col3 nvarchar(25),

Col4 decimal (9.3),

)

BULK INSERT myCSVbulk

FROM 'c:myfile.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

);

GO
=====================================
I got the following error message:
 

Msg 102, Level 15, State 1, Line 6

Incorrect syntax near '9.3'.
 
How can I set the statement "Col4 decimal (9.3)" right?  Please help and advise.
 
Thanks in advance,
Scott Chang

View Replies !   View Related
Insert Or Update Into A Image Column Causes Data To Double In Size
We are experiencing problems inserting or updating image fields fromone table to another in SQL Server.When we do this what ever size of file we insert is doubled in sizewhen it is inserted into the destination table.This happens in insert and update queries, and if we use DTS.Any help would be greatly appreciated

View Replies !   View Related
How Can I Create A Sequence ?
Hi,

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.

Thanks in advance.

Marie

View Replies !   View Related

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