Hi There,
I want to create a column that usto numbers upto a specified value, and the resets. I've tried using the identity column and then using DBCC CHECKIDENT, but this doesnt doesn't have the desired affect. Here's an example of what I'm after.
AutoRow
1
2
3
4 -------- reset
1
2
3
4 --------reset
Any help would be great,
many thanks
Stuart
Hi All,
I need to opening up a browser page everyday at 6am. (What I am trying to do is to send an email with some formatted output everyday at 6am)
Is there anyway to achive this via SQL 05 Express?
Thanks,
Mal.
Hi!
Let's say users of my app can build some reports based on a current database schema and my custom-database adapter will generate some SQL to get the right data. User can save his report template ( basically data needed to create SQL ) in some storage. Will it be better solution to generate stored procedure for such report and save stored proc to a database and their name with raport data so next time user needs this report it will use stored proc.
Jarod
Hi everyone,
I am having a table with which i generate a report. Now how to using a SQL query i can generate a auto incrementing no.
Say i am executing this query
SELECT NAME,AGE,ADDRESS FROM MEMBER which gives
NAME AGE ADDRESS
HOLYMAC 13 MALACCA
HOLYCOW 25 USA
HOLYGOD 55 LONDON
Now how can i make it come out like this
SNO NAME AGE ADDRESS
1HOLYMAC 13 MALACCA
2HOLYCOW 25 USA
3HOLYGOD 55 LONDON
See the first column is a auto incrementing number 1,2,3.
How can I write a SQL QUERY that outputs a auto incrementing number.
Thank you
Have nice day
Hi guys,
I am new to programming and sqlserver 2000. i use sql2000 as my database server...
I have a problem on auto-generating medicine codes. and i hope someone would help me with my script.
as you noticed i declared two variables (@id and @code). I am having a problem in generating @code... i would just like to create an autogenerated medicines_code from another field from another table (hosp_counter.counter_id)
here is how my medicines_code should look like ('MEDS34') "where 34 is from a counter_id in hosp_counter table...
but it only creates a medicines_code = 'M' (below is the result)
34(this is from counter_id)M(this is the autogenerated code)AUTOGENERATE11111010110012110010000000018/29/2007 11:29:46 AM1/1/19001/1/1900
here is my script in autogenerating medicine code...
set xact_abort on
begin transaction
declare @Code as varchar
select @Code =
(select 'MEDS' + CAST
(Counter_ID + 1 as varchar (50))
from Hosp_Counter where table_description = 'Medicines')
declare @ID as integer
select @ID = Counter_ID + 1
from Hosp_Counter
where table_description = 'Medicines'
select @ID
INSERT INTO Hosp_Medicines
(medicines_id,
medicines_code,
medicines_description,
medicines_specification,
medicines_genericname,
medicines_drugclass,
PhicCategory,
medpreparation,
medicines_prohibited,
medicines_trade,
medicines_NoneTrade,
medicines_taxable,
medicines_PhicCovered,
medicines_AmountCovered,
medicines_Discountable,
medicines_SmallUnit,
medicines_BigUnit,
medicines_AverageCostSmall,
medicines_AverageCostBig,
medicines_WeightedAverageCost,
medicines_weight,
medicines_conversion,
medicines_LowStockLevel,
medicines_OverStockLevel,
medicines_LowSalesReference,
AllowStat,
status,
CreateBy,
UpdateBy,
DeleteBy,
CreateDate,
UpdateDate,
DeleteDate)
VALUES
(@ID,
@code,
'AUTOGENERATE2',
'',
1,
1,
1,
1,
0,
1,
0,
1,
1,
0,
0,
1,
2,
1,
100,
100,
0,
0,
0,
0,
0,
0,
1,
'',
'',
'',
'8/29/2007 11:31:25 AM',
'',
'')
select @ID
Update Hosp_Counter set Counter_ID = @ID
where Table_Description = 'Medicines'
commit transaction
any help is very much appreciated!
'confused'
Joel
I am using sybase aSE12.5. I have a table with only one column withdatatype char(2). when i query this table to select all the records, ishould get these records in the ascending order and they should be numbered, i.e, the o/p should look something like thiscolumn_name------ --------1 AB2 AC3 ADand so on.I cannot add an extra column and i need this to be done in a single query.--Message posted via http://www.sqlmonster.com
View 2 Replies View RelatedIs it possible to have a DateTime field in a row automatically generated when a row is created?
ie:
INSERT INTO [TableName] ([DateTimeStamp], [UserId], [Title], [Explanation]) VALUES (DateTime.Now(), @UserId, @Title, @Explanation)
or something like that?
Karikalan writes "We need alpha numeric auto increment code in sql server 2000.
(for eg.: ico1001, ico1002, ico1003,......)
Can any one send the code in MS sql server 2000? plz ..................
bcoz i am beginner in sqlserver 2000"
Hi guys,
In the query below, I need to insert a new Column that shows the last Document date.
In example,
Document Date Last Date
2006/01/15 2007/11/30
2007/03/25 2007/11/30
2007/11/30 2007/11/30
I need to show the last (max) date for each row.
Thanks in advance for any help,
Aldo.
Code Block
SELECT
Stock.VALUEDATE AS 'Document Date'
FROM
Stock As Stock
INNER JOIN ACCOUNTS AS Accounts
LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE
LEFT OUTER JOIN EXTRANOTES AS ExtraNotes01 ON Accounts.ACCOUNTKEY = ExtraNotes01.KEF AND ExtraNotes01.NOTEID IN ('17')
LEFT OUTER JOIN EXTRANOTES AS ExtraNotes02 ON Accounts.ACCOUNTKEY = ExtraNotes02.KEF AND ExtraNotes02.NOTEID IN ('18') ON Stock.ACCOUNTKEY = Accounts.ACCOUNTKEY
INNER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID
WHERE
Accounts.SORTGROUP Between '0' AND '379' AND
Accounts.SORTGROUP Not Between '100' AND '150'
AND Stock.DOCUMENTID In ('1', '4', '5', '11', '78', '95')
AND Stock.STATUS Not In ('0', '2')
AND Accounts.SORTGROUP Between '0' AND '379'
AND Accounts.ACCOUNTKEY Like '313%'
Hi all,please see if some one can suggest a better approach for this.i need to move 5 million rows from one table to another.LoanID is the only clumn in Source tableSource table structure=========-->==============================loanID1122234=====================================target or detination table should look like this=========================================loanID Sqeuence_number_with_in_LoanID1 11 22 12 22 33 14 1=========================================this is what i have tried so far1)there are about 4 & 1/2 million out of 5 million which will appearonly once (this is one tine load) in source table.so if i do this-----------sleect loanIDfrom SourceTablegroup by loanIDhaving count(*)=1and then i will hard code Sqeuence_number_with_in_LoanID to 1 .becausei know they will only come once.this is set based operation so this isfast.2)now,problem is with remainder of the 1/2 million rows.i had to use cursor for this which is very-very slow.other option i was thinking of is to pull it in the front end(.net)and do it there.i am hoping that will be faster.Database i am working on is sql server 2000.if some could suggest me some better approach of handling thisremainder1/2 milion rows.thanks in advance-sidd
View 4 Replies View RelatedHi,
i'm importing data from a txt file to Sql server table through a DTS package. the table is created dynamically.
i want three new cloumns added in the front and one of the columns should have a sequence number generation.
Please advice
I want to fin out the number of records that are not numeric on my database, how can I fin that using an SQL query?
View 2 Replies View RelatedHello All,
Can someone pls help me with how to check for a numeric value in a varchar column?
For example I have a column called client_id , it has values "AB" , "CD" , "18", "19" . I need to delete those client_id where the values are 18 and 19. How would I do that?
Thanks in advance!!
I am trying to insert some values into a table where the column is of the data type "numeric". The insert works fine.Update does not work.
Update BUT_BREAKDOWN_PCT SET BDP_EFFORT_BREAKDOWN_PCT=0.15 WHERE BDP_BREAKDOWN_ID =1 AND BDP_PHASE_ID = 3 AND BDP_START_EFF_DT = '12/31/2004'
BDP_EFFORT_BREAKDOWN_PCT is a numeric column with a size 5 (4,3)
When I do the updatedirectly from QA, it works fine.
I was googling it and read a KB article saying it's a problem with Service Pack of SQL Server 2000. If it is, then the query should not work even from QA....isn't it?
Anyone had this problem before? Please help.
Using the following:
select hl, substring(hl,1,patindex('%/%',hl)) as test
from appointment
returns
hl test
A PCM/RODRIGUEZ A PCM/
Y OPTOMETRY/VISUAL FIELD TESTSY OPTOMETRY/
W DENTAL/DUNDON W DENTAL/
Y LAB
Y PCM/NEMES F/U Y PCM/
W NUTRITION/FIRM-A (ROOM E116)W NUTRITION/
W FIRM-A/SILVER/ABBOUD IIW FIRM-A/
I want to be able to remove the first 2 digits and the / to just have the clinic only remaining. Note that Y LAB is not listed in the test column..why? Any help is greatly apprecitated. Thank you....
Hi All,
I have table called Product with seven columns all of nvarchar type. There are 150,000 records in this table. Also there's no unique identifier column in this table. I want to put a column lets say Column1 which start from 1 to 150000. The Column1 should be a unique column.
How can achive this in SQL Server 2005?
Looking for a quick help.
Thanks a million,
Zee
Hi there --I'm writing a SQL query that works something like this:SELECT a, b, a/b AS col_name FROM TABLEDividing a by b can result in a value above or below 1, e.g. 1.234 or 0.123.If the value is below 1, I don't want the preceding zero, e.g. .123.I've been using a STR() function to format it to three decimal points, e.g.:STR(a/b, 5, 3) AS col_name.... but for values below 1 it'll look like 0.123.Any suggestions for how I can make this look like .123?Thanks in advance.
View 5 Replies View RelatedI would like to know how can i use derived column to covert the scrip that use to transformation column in DTS 2000(i've migrate my dts to ssis) i didn't know what function can check the numeric in ssis.
My scrip in transformation in dts 2000 as follow.
Function Main()
if isnumeric(DTSSource("Col012")) then
DTSDestination("SH") = int( DTSSource("Col012"))
else
DTSDestination("SH") = DTSSource("Col012")
end if
Main = DTSTransformStat_OK
End Function
Any suggestion please let's me know.
Thanks.
Best Regards,
Kus
I have a table named galleryindex which contains rows of photo gallery names along with an associated id (gsid).
example:
gsid (assigned ID), gnumpics (# of photos), gname (gallery name)
=================
43, 133, our summer visit
493, 53, camping photos
When someone uploads more photos I do the following to recalculate the NEW number of photos for the gallery.
SELECT gnumpics FROM galleryindex where gsid= 43
(select current # of photos from location 43 - our summer visit)
in ASP classic I then do this:
sb = objrs("gnumpics")
sb = sb + totupl
SQL = "UPDATE galleryindex SET gnumpics =" & sb & " where gsid= '" & fnum & "'"
to update the gallery with the new amount of photos which is obtained by pulling the old value into SB, and adding "totupl" (total pics uploaded) to it and then updating the values.In SQL speak it would be like this if 20 photos were just uploaded:
SELECT gnumpics FROM galleryindex where gsid= 43
(results in 133 photos)
UPDATE galleryindex SET gnumpics =153 where gsid= 43
(write back 133 + 20)
Basically I'm trying to update the current column gnumpics by a numeric value and I know there's an easier way then pulling the current, adding to it and then writing it back to SQL.
I am trying to run a query that checks a column to see if its numeric. If it is, it should cast as a float, if its not numeric it should return 0.
select case when (isnumeric(Ref)=1) THEN select (cast(Ref,float) )
else (0) as outputfrom #table
but its returning an error.
I have written this query to return only numeric characters from a string.
select *
from TableA
where isNumeric(Column_A) = 1
But I have discovered that the following
SELECT IsNumeric('-')
SELECT IsNumeric('£')
SELECT IsNumeric('$')
SELECT IsNumeric('+')
all return a value of 1. I do not want these in my result set.
If I use Column_A NOT LIKE '%[a-z]%' and Column_A <> '' I get characters such as "", ----- etc in my result set.
I would like a simple way of only returning numbers in my resultset.
Thanks for looking at this.
Hi,
I have a little bit of a problem I cannot seem to figure it out. Is it possible to write a Select statement that contains a WHERE column_name > desired_numeric_value
The tricky part it that the column is of CHAR type and can contain numeric grades ranging from 0-100 or the letter I for Incomplete.
My SQL was working perfect when this column contained only numbers as soon as a record with I was added I get the following error:
Character to numeric conversion error
This report will be used to find students who have failing grades. Thanks for any help!
I would like to determine the largest numeric datatype scale of a column.
Decimal Largest Scale Solution found here.
Sample Data:
2222.0002430
2222.0000245
2222.0023455
We have an address table with a house_num field which is a nvarchar.
Most of house numbers are numbers like 1234, 0989
But some of them have a letter behind it like 678 B, 8909 F, even some like this 123/B
We would like to remove any non-numeric letter for this column.
Is there a way to do it?
Hi there,
I have setup merge replication which successfully synchronizes with a group of desktop users using SQL Compact Edition.
However now I have setup Article Filters and when I attempt to regenerate the snapshot I get the following error:
Invalid column name 'rowguid'.
Failed to generate merge replication stored procedures for article 'AssignedCriteria'.
When I look at publication properties at the Articles page.. All my tables have the rowguid uniqueidentifier successfully added to tables and selected as a compulsory published column, apart from the table above "AssignedCriteria".. Even when I attempt to select this column in the article properties page and press ok, when I come back it is deselected again. ( The Rowguid column is however physically added to the table)
I have scripted the publication SQL and then totally reinstalled from scratch, including the database but for some reason it doesn't like this table. I remove the article filters, but still this "rowguid" is never "selected" in article properties.
We are using Uniqueidentifiers in other columns as well for historical reasons, but this doesn't appear to be a problem in other tables..
DDL For this problematic table is as follows
CREATE TABLE [dbo].[AssignedCriteria](
[AssignedCriteria] [uniqueidentifier] NOT NULL,
[CriteriaName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TargetScore] [numeric](5, 0) NULL,
[HRPlan] [uniqueidentifier] NULL,
[ActualScore] [numeric](18, 0) NULL,
[Criteria] [uniqueidentifier] NULL,
[Employee] [uniqueidentifier] NULL,
[IsActive] [bit] NULL,
[addDate] [datetime] NULL,
[totalscore] [numeric](5, 0) NULL,
[isCalc] [bit] NULL,
[Weight] [decimal](18, 2) NULL,
[ProfileDetail] [uniqueidentifier] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_7FF25DF903B6415FBFF24AC954BC88E4] DEFAULT (newsequentialid()),
CONSTRAINT [PK_AssignedCriteria] PRIMARY KEY CLUSTERED
(
[AssignedCriteria] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Thanks.
hi
I am using sql Server as database what value should I send for the autonumber field
Thanks
How can I put an Auto increment on a non PK field?
for ordering contents items?
I have a table called dbo.test. This table is built with two columns, namely; col_id and col_name.. Now, I want col_id to be auto-populated when a new col_name is added. But the col_id - should be of formulae xyyyy
where x = 2
and yyyy = will be incremental
Therefore, the first 3 records will be like ;
col_id Col_name
20001 A
20002 B
20003 C
......
......
and so on
Now, is there a way SQL can auto-populate teh col_id whenever a col_name is added?
I have a column that is not set to auto increment "IDX and Im inserting 800 part numbers but i want the IDX column to start at IDX 400 and increment 1 time per part number that is inserted. how can i accomplished that task.
EXAMPLE:
IDX PART#
400 abcde
401 fghi
402 jklm
etc. and so forth until the last part# will have IDX 1200...
Hello,
I have a column that is not auto-incremented. I would like to make it auto-incremented. For the moment, the only solution I found is to delete my column and to add a new auto-incremented column. The problem is I cannot delete it without deleting dependencies on it... So is there a solution to make an existing column auto-incremented or is it necessary to a create a new column ?
Thank you in advance for your help.
Hello, I have a table (publication) that has a primary key pubID which is an identity specification. This ID already has relationships to other tables.
I am having problems trying to make one of the other columns in the same table to auto generate. This column (bibNumber) is not related to any other table. All i want to do is when i create a new record in the table, auto generate a number for this column bibNumber.
How can i do this?
I am using sql server express 2005
Regards
Here is my objective:
As part of auditing of our application, I want to write the values of all column in one row out to an audit table both before and after an update to see what the changes were.
I know I can SELECT * FROM tableName FOR XML AUTO to generate a single XML 'column', but I cannot figure out how to capture that output and store it.
According to MS, you cannot use FOR XML in a subselect, or fill a TEXT variable with the results of such (which, IMHO really diminishes the usefulness).
I want to stream out the results of one entire row and store that in ONE column of an audit table. XML seems like the easiest way if not for this limitation.
A better way?
Thanks.
Hi,
I have an auto incrementing int column setup which serves as my unique primary key. Just wondering what happens when the auto increment reaches the limit? Will it recycle numbers from the begining (who's rows have obviously been deleted by this stage)?