How do I assign nos for column

I have existing data in a table and would like to
assign auto number to a column.
How can I do?
Thank you for your help in advance!
-KimTo add an identity column to your existing table you can issue the following
command:
alter table yourtable add newcol int identity
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Kim" <anonymous@.discussions.microsoft.com> wrote in message
news:00af01c49045$a6f16e80$a401280a@.phx.gbl...
> I have existing data in a table and would like to
> assign auto number to a column.
> How can I do?
> Thank you for your help in advance!
> -Kim|||Greg,
I don't want to add a column, I already have a column,
I want to assign sequence numbers to this column.
Sequence numbers will depend on another field, if
the field value = 'A' it will have one sequence,
if the field value = 'B' then it will have another
sequence, so on...
here is the example -
Col1 Col2 Col3 ...
T123 Test rec1 A
F001 Test Rec2 B
S0001 Test Rec3 A
P001 Test Rec4 A
it should have the following values ...
Col1 Col2 Col3 ...
A00001 Test rec1 A
B00001 Test Rec2 B
A00002 Test Rec3 A
A00003 Test Rec4 A
Hope this helps!
Thank you,
-Kim

>--Original Message--
>To add an identity column to your existing table you can
issue the following
>command:
>alter table yourtable add newcol int identity
>--
>----
--
>----
--
>-
>Need SQL Server Examples check out my website
>http://www.geocities.com/sqlserverexamples
>
>"Kim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00af01c49045$a6f16e80$a401280a@.phx.gbl...
>
>.
>|||Kim wrote:
> Greg,
> I don't want to add a column, I already have a column,
> I want to assign sequence numbers to this column.
> Sequence numbers will depend on another field, if
> the field value = 'A' it will have one sequence,
> if the field value = 'B' then it will have another
> sequence, so on...
> here is the example -
> Col1 Col2 Col3 ...
> T123 Test rec1 A
> F001 Test Rec2 B
> S0001 Test Rec3 A
> P001 Test Rec4 A
> it should have the following values ...
>
> Col1 Col2 Col3 ...
> A00001 Test rec1 A
> B00001 Test Rec2 B
> A00002 Test Rec3 A
> A00003 Test Rec4 A
> Hope this helps!
> Thank you,
> -Kim
If you know the number of possible col3 values beforehand, you can write
a T-SQL script to move through the table, grab each row, one at a time,
check the col3 value, increment the corresponding counter value in the
script, and update the row using the counter value.
Pseudo-Code Here:
Start all counters at 0
Loop through a cursor on the table (do this off-hours)
Get col3 value
If col3 = 'A' then
CounterA = CounterA + 1
NewCol1 = col3 + Right('0000' + CAST(CounterA as varchar(5)), 5)
If col3 = 'B' Then
CounterB = CounterB + 1
etc.
Update Table
Set Col1 = NewCol1
Where PKVal = WhateverThePKValueIs
David G.|||David,
I will implement your suggestion - thanks.
In the meantime wanted to find out from you how
can I create sequences for each of the series for future
use? Like for 'A' ... 'A000001' onwards,
for 'B' ... 'B000001' onwards.
'cause after I update my database with these numbers
I would like it to autogenerate while creating records
for each of the series.
Thank you for your help!
-Kim

>--Original Message--
>Kim wrote:
>
>If you know the number of possible col3 values
beforehand, you can write
>a T-SQL script to move through the table, grab each row,
one at a time,
>check the col3 value, increment the corresponding
counter value in the
>script, and update the row using the counter value.
>Pseudo-Code Here:
>Start all counters at 0
>Loop through a cursor on the table (do this off-hours)
> Get col3 value
> If col3 = 'A' then
> CounterA = CounterA + 1
> NewCol1 = col3 + Right('0000' + CAST(CounterA as
varchar(5)), 5)
> If col3 = 'B' Then
> CounterB = CounterB + 1
> etc.
> Update Table
> Set Col1 = NewCol1
> Where PKVal = WhateverThePKValueIs
>--
>David G.
>.
>|||Kim wrote:[vbcol=seagreen]
> David,
> I will implement your suggestion - thanks.
> In the meantime wanted to find out from you how
> can I create sequences for each of the series for future
> use? Like for 'A' ... 'A000001' onwards,
> for 'B' ... 'B000001' onwards.
> 'cause after I update my database with these numbers
> I would like it to autogenerate while creating records
> for each of the series.
> Thank you for your help!
> -Kim
>
You can create a trigger on the table or a before trigger if the value
is not part of the PK. You'll have to keep track of the underlying key
values using another table.
I'm not a big fan of these types of intelligent keys because maintance
and implementation are much more difficult than using an indentity
columns. Have you considered using an identity column with the col3 as
the compound PK. That way, you have to do nothing to get the values in
there. You can then create a computed column on the table to return
values to your application in the correct format.
David G.

how do I assign a string to a parameter Im passing to a select statement?

Hello,

I'm needing to pass a variable length number of values to a select statement so I can populate a result list with items related to all the checkboxlist items that were selected by the user. for example, the user checks products x, y and z, then hits submit, and then they see a list of all the tests they need to run for each product.

I found a UDF that parses a comma delimited string and puts the values into a table. I learned how to do this here:

http://codebetter.com/blogs/darrell.norton/archive/2003/07/01/361.aspx

I have a checkboxlist that I'm generating the string from, so the string could look like this: "1,3,4,5,7" etc.

I added the function mentioned in the URL above to my database, and if I understand right, I should be able to pass the table it creates into the select statement like so:

WHERE (OrderStatus IN ((select value from dbo.fn_Split(@.StatusList,','))) OR @.StatusList IS NULL)

but now I don't know how to assign the string value to the parameter, say to '@.solution_id'.

my current select statement which was generated by Visual Studio 2005 looks like this:

SELECT [test], [owner], [date] FROM [test_table] WHERE ([solution_ID] = @.solution_ID)

...but this only pulls results for the first item checked in the checkboxlist.

Does anyone know how this is done? I'm sure it's simple, but I'm new to ASP .NET so any help would be greatly appreciated.

hi

First make sure you have createddbo.fn_Split .

SELECT [test], [owner], [date]FROM [test_table]WHERE ([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)

I am not sure "OR @.solution_IDISNULL" should be added,you have to decide it according to your logic.

You are required to pass @.solution_ID to the statement(1,3,4,6 etc) then you can get corresponding test.

Hope this helps.

|||

Thanks for your response. If I'm following you, I do understand that I need to pass @.solution_ID to the select statement like you showed. I have a string of values that I created from iterating through CheckBoxList to find selected boxes. My question is, how do I assign the value of this string to @.solution_ID?

Regards,

Daniel

|||

Assume you have checkboxlist Check1, using following code to get @.solution_ID :

for (int i = 0; i < Check1.Items.Count; i++)
{
if (Check1.Items[i].Selected)
{
// List the selected items
solution_ID = solution_ID + Check1.Items[i].Text;
solution_ID = solution_ID +",";
}
}

Then connect with DB:

SqlCommand sqlcmd = new SqlCommand("SELECT [test], [owner], [date]FROM [test_table]WHERE
([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)", sqlconn);
sqlcmd.Parameters.AddWithValue("@.solution_ID",solution_ID);
sqlconn.Open();
SqlDataReader sdr = sqlcmd.ExecuteReader();

.............

You 'd bette put bold sql script into a stored procedure.

hope this helps.

How do I apply a LDF log file to a restored database?

I only have the ldf file and the bak file.

My bak file was current to 3/2/2007

My ldf file was current to 3/25/2007

I do not have the mdf file.

How to I apply the transaction from the ldf file to my resotred database?

Hello David,

According to my knowledge it not is possible to apply a .ldf file but if you are using some external tool like Logexplorer then you can atleast view the transactions in that .ldf file.

|||If this is the case it would seem to negate the purpose of having a log file?|||

The purposes of the log file are:

To facilitate rollback/rollforward when a database is brought online after an unusual shutdown|||

you said you have a .bak file and a log file. My gut feeling is that , this bak file is a full backup of your database. In that case you can restore the database from this back file as on the bak file is created.

YOu have mentioned that you have a ldf file. is it physical file or is it a bakup file. confirm this

run the following command to know what all are the contents inside the bak file

RESTORE FILELISTONLY FROM DISK = 'D:\your bak filename.bak'

if it is a full backup use restore command to restore it. Read about Restore command in BOL

Madhu

|||

No David ,

It would not negate the use of a log file. if you had a .mdf file then you could attach the log file with the .mdf file. .trn files are the log back up file not the .ldf files.

How do I apply a LDF log file to a restored database?

I only have the ldf file and the bak file.

My bak file was current to 3/2/2007

My ldf file was current to 3/25/2007

I do not have the mdf file.

How to I apply the transaction from the ldf file to my resotred database?

Hello David,

According to my knowledge it not is possible to apply a .ldf file but if you are using some external tool like Logexplorer then you can atleast view the transactions in that .ldf file.

|||If this is the case it would seem to negate the purpose of having a log file?|||

The purposes of the log file are:

To facilitate rollback/rollforward when a database is brought online after an unusual shutdown|||

you said you have a .bak file and a log file. My gut feeling is that , this bak file is a full backup of your database. In that case you can restore the database from this back file as on the bak file is created.

YOu have mentioned that you have a ldf file. is it physical file or is it a bakup file. confirm this

run the following command to know what all are the contents inside the bak file

RESTORE FILELISTONLY FROM DISK = 'D:\your bak filename.bak'

if it is a full backup use restore command to restore it. Read about Restore command in BOL

Madhu

|||

No David ,

It would not negate the use of a log file. if you had a .mdf file then you could attach the log file with the .mdf file. .trn files are the log back up file not the .ldf files.

How do I append string to end of sql query results?

I need to append text to the end of my sql query results.

For instance, my query returns a list of user names in
firstname.lastname. I need to add @.yahoo.com to each record. So the
end result should be firstname.lastname@.yahoo.com. What should by
select statement look like?

Any help?"diesel" <dieselpb03@.yahoo.com> skrev i en meddelelse
news:854ece22.0407131219.6f38c10a@.posting.google.c om...
> I need to append text to the end of my sql query results.
> For instance, my query returns a list of user names in
> firstname.lastname. I need to add @.yahoo.com to each record. So the
> end result should be firstname.lastname@.yahoo.com. What should by
> select statement look like?
> Any help?

SELECT Firstname + '.' + Lastname + '@.yahoo.com' AS Name
FROM tblPerson

easypeasy

--
Med venlig hilsen

Tom F Jensen
FFSoft
www.ffsoft.dk|||Maybe:

SELECT name + '@.yahoo.com'
FROM YourTable

--
David Portas
SQL Server MVP
--

How do I append a row to SQL results?

I have a problem with a dropdowncontrol. It is databound, but I need to add "select..." to be the first item in the dropdown. Here is the SQL:

SELECT * FROM [PB_Subtopics] Where BriefID=" + DropDownList1.SelectedValue

So the problem I am having is I can't just make an item in the dropdownlist called "select..." and then use appenddatabounditems="true". I'm using ajax and it keeps appending stuff over an over without resetting. So I think I'm going to have to do this within the sql.

So maybe that was more information than you needed to know. Anyone know how to make the first row of my SQL results be "select..." or whatnot with a value of 0.

You need to do this kind of thing at the front end.

|||

You can do this with a UNION. Something like this:

"SELECT 0, 'Select...'
UNION
SELECT * FROM [PB_Subtopics] Where BriefID=" + DropDownList1.SelectedValue

The number of fields in the first SELECT has to match the second, so adjust that as needed. And I've put in line breaks here to make it clearer what is happening, but it is all one string in what I assume is VB.NET. Adjust as needed for your code.

Also, you might need to use UNION ALL instead of just UNION, depending on your data.

Put the 'Select...' in the field location where the data used to display the item in the dropdown list.

Make sense?

Don

|||

bbaxter:

I have a problem with a dropdowncontrol. It is databound, but I need to add "select..." to be the first item in the dropdown. Here is the SQL:

SELECT * FROM [PB_Subtopics] Where BriefID=" + DropDownList1.SelectedValue

So the problem I am having is I can't just make an item in the dropdownlist called "select..." and then use appenddatabounditems="true". I'm using ajax and it keeps appending stuff over an over without resetting. So I think I'm going to have to do this within the sql.

So maybe that was more information than you needed to know. Anyone know how to make the first row of my SQL results be "select..." or whatnot with a value of 0.

Try this:

1-- Get The Result Set2SELECT Col1, Col2, Col33From MyTable45UNION-- to append67SELECT'MyRowValueForCol1','MyRowValueForCol2','MyRowValueForCol3'-- columns value of the row you want to append to the result set8FROM MyRowTable-- if you are geeting this row from table OR you can ignor this line if the values are hard coded9

Good luck.

|||

You can have the resultset modified using one of the above techniques, that should work.

You can also turn on append databound items and turn off viewstate (Viewstate will prevent the multiple additions as well as cut down the size of your page and the response you get back).

Or you can just add this type of code:

Sub ddlMyDropdown_databound(sender as object,e as object) handles ddlMydropdown.databound

ctype(sender,dropdown).items.insert(0,new listitem("Select...",""))

end sub

sql

how do I alter the column with index on it

hi,
I have a sqlserver 2000 db. I want to alter two columns
from one of the tables to not allow nulls.
but these 2 columns (one if char, and the other is
smalldatetime), one have clustered index on it, and the
other one is part of a combined index of two columns.
is the only way for the column alteration is to drop the
index first, and then re-create it later?
many thanks!
JJ
Hi,
Altering the data type of one column which has participated in an index is
not supported. If the modified column participates in an index, the only
type changes that are allowed are increasing the length of a
variable-length type (for example, VARCHAR(10) to VARCHAR(20)), changing
nullability of the column, or both. I believe you have to drop the original
index and rebuild a new index.
Thanks
Hari
MCDBA
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:2131001c45a37$30e96600$a501280a@.phx.gbl...
> hi,
> I have a sqlserver 2000 db. I want to alter two columns
> from one of the tables to not allow nulls.
> but these 2 columns (one if char, and the other is
> smalldatetime), one have clustered index on it, and the
> other one is part of a combined index of two columns.
> is the only way for the column alteration is to drop the
> index first, and then re-create it later?
> many thanks!
> JJ
|||You can user table designer shipped with SQL server client tools or
Visuastudio to change the table.
Lishil, VSDATA Team
--
>Content-Class: urn:content-classes:message
>From: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Sender: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Subject: how do I alter the column with index on it
>Date: Thu, 24 Jun 2004 15:04:16 -0700
>Lines: 15
>Message-ID: <2131001c45a37$30e96600$a501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcRaNzDpwp7EieNJRQCcEtOkif2wyg==
>Newsgroups:
microsoft.public.sqlserver.programming,microsoft.p ublic.sqlserver.tools
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.tools:24659
microsoft.public.sqlserver.programming:453163
>NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>hi,
>I have a sqlserver 2000 db. I want to alter two columns
>from one of the tables to not allow nulls.
>but these 2 columns (one if char, and the other is
>smalldatetime), one have clustered index on it, and the
>other one is part of a combined index of two columns.
>is the only way for the column alteration is to drop the
>index first, and then re-create it later?
>many thanks!
>JJ
>
|||thanks Lishil. I know the tool to alter it, I just want
to know whether I have to drop the index first before I
alter the column. please see my privious email for detail.
thanks.
JJ
>--Original Message--
>You can user table designer shipped with SQL server
client tools or
>Visuastudio to change the table.
>Lishil, VSDATA Team
>
>--
>microsoft.public.sqlserver.programming,microsoft. public.sq
lserver.tools[vbcol=seagreen]
microsoft.public.sqlserver.tools:24659
>microsoft.public.sqlserver.programming:453163
>.
>
|||thanks Hari. I fear so too. oh well...
thanks.
JJ
>--Original Message--
>Hi,
>Altering the data type of one column which has
participated in an index is
>not supported. If the modified column participates in an
index, the only
>type changes that are allowed are increasing the length
of a
>variable-length type (for example, VARCHAR(10) to VARCHAR
(20)), changing
>nullability of the column, or both. I believe you have to
drop the original
>index and rebuild a new index.
>
>--
>Thanks
>Hari
>MCDBA
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2131001c45a37$30e96600$a501280a@.phx.gbl...
>
>.
>
|||Ok, you already get answer from hari_prasad_k@.hotmail.com.
You do need to drop index in you case.
Lishi Liu, VSData team
--
>Content-Class: urn:content-classes:message
>From: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Sender: "JJ Wang" <anonymous@.discussions.microsoft.com>
>References: <2131001c45a37$30e96600$a501280a@.phx.gbl>
<$AXF2GuXEHA.2352@.cpmsftngxa06.phx.gbl>
>Subject: RE: how do I alter the column with index on it
>Date: Thu, 8 Jul 2004 18:24:14 -0700
>Lines: 61
>Message-ID: <2962a01c46553$72445bb0$a501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcRlU3JEKPTFyjEUT129z6+ojcBcmg==
>Newsgroups:
microsoft.public.sqlserver.tools,microsoft.public. sqlserver.programming
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:456466
microsoft.public.sqlserver.tools:24467
>NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>thanks Lishil. I know the tool to alter it, I just want
>to know whether I have to drop the index first before I
>alter the column. please see my privious email for detail.
>thanks.
>JJ
>client tools or
>lserver.tools
>microsoft.public.sqlserver.tools:24659
>