|
I'm not a database expert, but nobody else answered your question so I'll give it a shot. It sounds like you're using Oracle or something because you said auto increment instead of index. But, in SQL Server, you can write an insert statement in your stored procedure like this:
insert into [table]
(
column1,
column2,
column3
)
values
(
@column1,
@column2,
@column3,
)
return SCOPE_IDENTITY()
That will return the value in the identity column.
In Oracle, you do it two ways. The first way is to have a sequence and get the next value of the sequence first using:
SELECT table_seq.NEXTVAL AS ID from DUAL
Then when you do your insert, use that ID in your insert.
The other way is to have a sequence and a trigger. Do your insert and then select the max value for that column out of the table and return it. The first way is probably better.
Any of you db gurus out there, please correct me if I'm wrong.
Logifusion[^]
|
|
|
|
|
Thanks Dustin, that was fast. I am actually using SQL Server 2000 and I am sorry I forgot to mention.
|
|
|
|
|
with SQL server its the same but you do
:
RETURN @@IDENTITY
Jon
|
|
|
|
|
Hi,
What happens when we try to update a data in a dataset which is already deleted in sql server?
Thanks in advance
|
|
|
|
|
i want to check column of type image in my database to see if image column in the selected row has image in it or it null?
ma_refay
|
|
|
|
|
|
Hello all,
using SQL server 2000 does anyone know a sql statment to get a list of trigger names that are associated with a table?
|
|
|
|
|
just to clarify im looking for a way to list the defiend triggers of a particular table.. I have found examples for oracle and MySQL5.0 but nothing for SQL Server 200.
Oracle = select trigger_name from user_triggers;
MySQL = SELECT TRIGGER_NAME,ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_OBJECT_TABLE='TEST_TABLE';
|
|
|
|
|
ahh dont worry...
ive found it.. it: exec sp_helptrigger table_name
|
|
|
|
|
ahh.... actually im still having trouble.. while that works im still unable to use that select statement programatically... Does anyone know how i can do this?
|
|
|
|
|
ahh dont worry... ive cracked it!!!
select name as 'Trigger', object_name(parent_obj) as 'Table' from sysobjects where xtype = 'TR' and object_name(parent_obj) = 'MY_TABLE_NAME'
where 'MY_TABLE_NAME' is the name of the table
|
|
|
|
|
hi, i want to find the solution for the following sql query..
there is a table T(x,y,z)
i want to insert 10 records into table T using a sql query.
sam
|
|
|
|
|
Nigam Samir wrote: i want to insert 10 records into table T using a sql query.
You can use loops for this purpose either in Stored procedures or in front end application.
_____________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
insert into T select top 10 'xval','yval','zval' from sysobjects
This will put 10 records (all the same) into the "T" table. I just used sysobjects becuase it is pretty much guarenteed to have at least 10 records in it.
If you want different values in the fields or records, you could use the random function.
There might be betters ways to do this though.
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
i work in hr sqlserver database
in employees table i have password column so every one have permission to open database can see all the passwords i wnat toedit the column password so who open employyes table see symblos in password column not the real password
ma_refay
|
|
|
|
|
|
Hi!
Check my blog at http://www.cubido.at/Blog/tabid/176/EntryID/34/Default.aspx. There I show how you can encrypt and decrypt the password column with SQL2005.
If you do not have SQL2005 but 2000 you do not have these smart encryption functions built into the DB. You would have to implement encryption yourself (e. g. using .NET's encryption functions). In fact for a password it would be enough to calculate a (salted) hash (see http://en.wikipedia.org/wiki/Salt_%28cryptography%29 for details) -> no encryption needed.
A completly unsecure solution that just displays the password in binary instead of cleartext is to change the data type of the password column to varbinary. A SELECT will then just show hex values. Again, this is NOT encryption. Everyone how is able to write SQL can write cast(password as varchar) and sees the password. However, sometimes it is enough to just changing the display format of the password to binary.
Regards,
Rainer.
Rainer Stropek
Visit my blog at http://www.cubido.at/rainers
|
|
|
|
|
|
You're right, this is also a storage format. However, as far as I understood the question the application is Microsoft's enterprise manager or management studio -> you do not have the possiblitiy to change the display format without changing the storage format. If the original question did not regard to pre-built standard software but to individually developed software I aggree to you.
Regards,
Rainer.
Rainer Stropek
Visit my blog at http://www.cubido.at/rainers
|
|
|
|
|
how to build and execute a query in user defined functions?
Swaroop.
|
|
|
|
|
|
Hi!
It is not supported to build and execute a dynamic query in user defined functions. UDFs have to be deterministic -> no exec( @MyVar ) allowed.
Rainer.
Rainer Stropek
Visit my blog at http://www.cubido.at/rainers
|
|
|
|
|
i have sqlserver data base and i want to store sounds in the database and reterive these sound how?!!!!!!!!!!!!!!
ma_refay
|
|
|
|
|
|
hai
I want to protect my database from anyone other than i. How I can set a password to my single Database. This is in Intranet. I have sql server username and Password(ie Sa and some pwd).
Pls help me
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
