Adding prefilled column to a Database...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding prefilled column to a Database...

Author

Topic

UF-Supra
Starting Member

11 Posts

Posted - 2006-07-14 : 11:33:37

Ok here's the deal guys (I'm not even sure this is possible),

There is a database with information already loaded in it, I need to be able to step through each row, so I had an idea to add a "Counter" column. Basically just add a column named "Counter" with each field in the column corresponding to the row its in.

Counter
1
2
3
4
5

and so on.
Now here's my problem, I know how to add blank columns (with each field = to <NULL>), however I have no clue how to accomplish what my goal is above.

Can anyone help me????

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-14 : 11:40:14

Add an Identity column as shown below:

Alter Table SomeTable
Add SeqNo Int Identity(1,1)
Go

Harsh Athalye
India.
"Nothing is Impossible"

UF-Supra
Starting Member

11 Posts

Posted - 2006-07-14 : 12:14:52

HA! Thank you so much, works perfectly!!

And to think I spent all day yesterday trying to find that snipet of code...

UF-Supra
Starting Member

11 Posts

Posted - 2006-07-14 : 15:14:36

Ok... another question.

I'm writing a program in VB that uses the above column addition. Now I was wondering if there is a way, after adding the column, that you can ask what the highest value in the column is???

Thanks in advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-14 : 15:16:48

If you want to capture the value that was just inserted, then you use SCOPE_IDENTITY() function.

INSERT INTO Table1 ...

SELECT @id = SCOPE_IDENTITY()

You can also use @@IDENTITY, but SCOPE_IDENTITY() is safer to use.

Tara Kizer

UF-Supra
Starting Member

11 Posts

Posted - 2006-07-14 : 15:22:53

uh huh... maybe I should of said that I'm learning SQL on the fly (been working with it for the past couple weeks)

so... could you explain what exactly what you just typed does??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-14 : 15:31:48

You perform an INSERT statement. Then to retrieve the value that SQL Server assigned to the identity column, you can use SCOPE_IDENTITY() function right after the insert. Put the value into a variable so that it is saved. Then use the variable wherever you need this value.

Tara Kizer

UF-Supra
Starting Member

11 Posts

Posted - 2006-07-14 : 15:42:47

Ok so if I'm understanding this correctly,

the SCOPE_IDENTITY() function captures the last value entered into the table (due to being right after the INSERT function). And the way you wrote it, it stores that vaule into a SQL variable "@id". Now how can I get that value passed into VB code (where I need the value for certain processes)?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-14 : 15:51:23

I can't help you with the VB code. If you were to use stored procedures in your code, then I could assist. You'll probably want to ask VB questions in a VB forum.

Tara Kizer

UF-Supra
Starting Member

11 Posts

Posted - 2006-07-14 : 15:56:37

HA! I got it

Thanks again for all the help! If you're curious this is the VB side of it (pretty obvious actually):


Dim big As Int16
DatabaseCommand.Connection = sqlConn
            DatabaseCommand.CommandText = "SELECT "column name" FROM "table name" WHERE "column name" = (SELECT MAX("column name") FROM "table name") ;"
            Dim rdrMyReader As SqlDataReader
            rdrMyReader = DatabaseCommand.ExecuteReader
            rdrMyReader.Read()
            big = rdrMyReader("column name")

Thanks again!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-14 : 15:58:31

That does not guarantee that you will get the last value inserted. You can only do this with SCOPE_IDENTITY() function.

Tara Kizer

UF-Supra
Starting Member

11 Posts

Posted - 2006-07-14 : 16:05:01

With this:

Alter Table SomeTable
Add SeqNo Int Identity(1,1)
Go

The last value insterted will always be the largest integer in the column. (Since the column was previously completley blank, and then filled by the above code).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-14 : 16:34:24

When you MAX instead of SCOPE_IDENTITY() function, you aren't guaranteed to get the last value inserted. This is because someone else could have inserted a value into the table in between the insert and selecting the max. Therefore, you would receive this other max value instead of the one that you intended. Even if there will only ever be one person on your system, you should not code like this. SCOPE_IDENTITY() function is how you guarantee you receive the correct value.

Tara Kizer

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-17 : 04:09:29

quote:
Originally posted by tkizer

When you MAX instead of SCOPE_IDENTITY() function, you aren't guaranteed to get the last value inserted. This is because someone else could have inserted a value into the table in between the insert and selecting the max. Therefore, you would receive this other max value instead of the one that you intended. Even if there will only ever be one person on your system, you should not code like this. SCOPE_IDENTITY() function is how you guarantee you receive the correct value.

Tara Kizer

But Tara, I am afraid that either Scope_Identity() or @@identity will not work in UF-Supra's case.
Since he is just adding an identity column, not doing any inserts,
both @@Identity and SCOPE_IDENTITY() will return NULL. Instead what he need is Ident_Current() function as follows:

Select Ident_Current('tablename')

UF-Supra, I believe you can replace Select query in your front-end code with the above query (replacing tablename ofcourse !!)

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources