Home » SQL & PL/SQL » SQL & PL/SQL » identity column
identity column [message #237159] Sat, 12 May 2007 14:48 Go to next message
venu_kmnn
Messages: 8
Registered: August 2005
Location: hyderabad
Junior Member

Hi everybody.

iam going to crate new table with three field i.e

create table emp(no number,name varchar2(30),sal number)

Now I want to insert data into emp table with out mention no column it will insert automatically(with out using sequence)

eg:
insert into emp(name,sal)values('venu',20000)
insert into emp(name,sal)values('Sridhar',25000)


the o/p of table is.


no name sal
----- --------- ----------
1 venu 20000
2 Sridhar 25000


regards
venu k
Re: identity column [message #237160 is a reply to message #237159] Sat, 12 May 2007 15:07 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Without a sequence? Why? What's wrong with sequences?

Is it yet another "how to make my life complicated" question?
Re: identity column [message #237213 is a reply to message #237159] Sun, 13 May 2007 07:43 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
IMHO - that "thats the way to do it with MS SQLServer" question.

Michael
Re: identity column [message #237228 is a reply to message #237213] Sun, 13 May 2007 11:05 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Reported message

Reported By: venu_kmnn On: Sun, 13 May 2007 17:52 In: SQL & PL/SQL SQL & PL/SQL Newbies identity column

Reason Hello Thanks for reply I have 100 tables every table having identity column In this case 100 sequences are require or any other way Regards venu k




Please, do not report your own messages; those reports are visible only to the administration staff.



No, you don't need 100 sequences. One sequence can serve all your tables.
Re: identity column [message #237229 is a reply to message #237228] Sun, 13 May 2007 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the other way is worst: 100 triggers!

Regards
Michel
Re: identity column [message #237430 is a reply to message #237159] Mon, 14 May 2007 08:23 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Whats so hard about 100 triggers. With a simple select, a script could be generated that will create the triggers and sequences in about 5 minutes

spool c:\temp\build.sql
select 'create sequence '||table_name||'_S;'||chr(10)||
'create trigger '||table_name||'_T.....
FROM USER_TABLES;

SPOOL OFF

@C:\TEMP\BUILD.SQL


I won't write the entire code, but I hope you understand the technique.
Previous Topic: Alter column
Next Topic: Deadlock by deletion
Goto Forum:
  


Current Time: Fri Dec 09 09:56:47 CST 2016

Total time taken to generate the page: 0.06841 seconds