SQL 2012 :: Create A Column Of Numbers That Increment By 1?

Feb 18, 2014

I'm trying to create a column of numbers that increment by one.

I'm not able to use a #temptable in the application I'm using so I cannot use IDENTITY(int,1,1).

I want to add an Id column to this query:

Select distinct sd.name,ic.TABLE_SCHEMA,ic.TABLE_NAME from sys.databases sd
cross join INFORMATION_SCHEMA.COLUMNS ic
where sd.name = 'ODS1stage'
order by TABLE_SCHEMA,TABLE_NAME

How can I accomplish this without creating a temp table? I would just alter the table and insert the numbers but there are 2000 rows.

View 7 Replies

Createing Column Primary Key And Contains Of 3 Characters And 6 Auto Increment Numbers (example: DLL - 123456) (

Dec 28, 2007

i would to make a column contains of 3 characters and 6 auto increment numbers (example: "DLL - 123456")

and made it primary key and which data type i should use. i do not know whether i use after insert trigger in two columns one for three characters and another for code which has identity property >>>so please help me

View 4 Replies View Related

Create Sequential Numbers In A Column

Jul 20, 2005

I have a temp table that's populated with an insert query in as toredprocedure. The temp table has a uniqueID as the primary key.In that table I have a column SortOrder.What I want to do is to create a sequential number in SortOrder butonly for records matching a WHERE statement, for example:(pardon the shorthand...)Insert *.tblPermanent into tblTempIf myField = 1 thenSortOrder = 1(2,3,4,5,.....etc.)elseSortOrder = 0Thankslq

View 1 Replies View Related

Simple Query To Create Column Of Sequential Numbers

May 2, 2006

View 2 Replies View Related

SQL 2012 :: Update A Column With A Sequence Of Numbers Starting From 1?

Oct 7, 2015

If Exists ( Select c.name from sys.columns c where object_id = object_id('HH835HP') and C.name = 'ID_1' )
Begin
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
End;

Obviously... The stuff inside the IF is wrong syntax...I mean

UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;

View 4 Replies View Related

SQL 2012 :: Populate Int Column With A Sequence Of Numbers But Sorted By Another Field

Oct 8, 2015

The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).

While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.

What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.

Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;

View 2 Replies View Related

Creating A Auto Increment Which Contains Numbers && Text

May 25, 2005

Hi,Please can you let me know the best solution for creating a primary key which automatically increments by 1 each time a record is added. My current Primary key is of type "Int" which increments by 1 each time, but I would like my primary key to contain "ABC" before the 1. So each time a record is added I would like to see:-ABC000001 ABC000002ABC000003Etc, EtcI am using SQL Server 2000 and creating an ASP.Net application, will I need to write code in a Stored Procedure to do this?Regards,Brett

View 4 Replies View Related

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers

Feb 1, 2007

I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?

View 1 Replies View Related

SQL Server 2012 :: Create Column Based On A Value In A Flatfile

Nov 10, 2014

I am wanting to creating a column based on a value in a flatfile, and insert that value into the destination table...

I have a flatfile and a oledb destination, I dropped a script component on the dataflow tab, and I am wanting to take an existing field and create a for lack of a better word, "CODE" field based on that value. How would I accomplish this?

Example--- where description = Test1 code = 1, Test2 code = 2, and same thing for Test3

Description code
Test1 1
Test2 2
Test3 3

View 6 Replies View Related

SQL 2012 :: Create A Check Constraint Where Another Table Column Value Is Also Involved

Mar 12, 2015

Table ATable B
AIDBID
Col 1BCol1
Col 2
Col3

I want to put a check constraint on A.col3 that depends on the values of A.Col1, A.Col2 and B.BCol1

How can I achieve this without using function.

View 8 Replies View Related

SQL Server 2012 :: Create A New Column By Dividing 2 Columns In The Query?

Mar 12, 2015

I have the following query that displays 2 values. I want to add a column with the percentage ([Providers With Security]

/ProviderTotal) * 100
SELECT (SELECT COUNT(DISTINCT NPI) FROM HS140_Rpt_Tmp_ForSummary WHERE Market = s.Market) AS ProviderTotal,COUNT(DISTINCT NPI) AS [Providers With Security]
FROM HS140_Rpt_Tmp_ForSummary s
WHERE s.[Security] = 'Yes'
GROUP BY Market

How can I do this?

View 1 Replies View Related

How Do I Create An Auto Increment?

Apr 29, 2006

Hi all

I just got SQL server 2k5 installed and working....im just trying to figure out how to make an auto incremement column wiht an integer..

or has it been replaced with the "unique identifyer"

Abyss

View 4 Replies View Related

Create Primary Key With Increment And Format?

Nov 19, 2005

I have an access table that has a primary key (entitled "ID Number"), no duplicates, the field is an integer.And, importantly, the value is set to "increment".The format is "phd"000 - so it starts out phd001, phd002, and so on...How to do this in an SQL table? Can that format be done? Or is it better not to do it via SQL but in coding instead?

View 1 Replies View Related

SQL 2012 :: Increment Primary Key

Mar 9, 2014

I am developing asp.net application with sql server database

I have table FinancialYear

caseno(unique) finyear
1 2013-2014
2 2013-2014
3 2013-2014
1 2012-2013
2 2012-2013
3 2012-2013

For financial year-2013 to 2014. if i insert record ( select max(caseno) +1).

insert into finanancialYear values(max(caseno) +1,2013-2014)
if i delete record caseno:3 from the table,
select max(caseno)+1 will be 3

Which is already deleted and again that record with caseno 3 will be inserted

For every financial year in 'FinancialYear' table,.caseno will be reset to 1

And if I add new record in finyear 2012-1013

then max(caseno) will be last value of caseno of that financial year 2012-2013

Again insert becomes

insert into finanancialYear values(max(caseno) +1,2012-2013)

I have given the data.

View 5 Replies View Related

SQL 2012 :: Hash Deleted And Auto-increment ID By 2 Not 1?

Sep 15, 2014

When I have a record that I copy and paste (Access 2010 datasheet view) into a new record with one column having a unique constraint , I display message "Cannot insert duplicate code" then if not all values in the record are inserted , I get a #deleted in all columns Refreshing , I find that the id is incremented by 2 instead of one.

That is if the previous record has ID 20 , the #deleted record has ID 22 .

avoid the #deleted which happens if not all columns are filled.

View 9 Replies View Related

SQL Server 2012 :: Increment Day To Timestamp When Time Is After 00:00

Jun 29, 2015

I have a series of time from 08:10 till 00:55 and a single date in a seperate column. What I need to have is associate the date with each time from 08:10 and when it comes after 23:55 the date needs to be incremented to the next day.

something like below

29Jun2015 08:10
29Jun2015 08:15
29Jun2015 08:20
29Jun2015 08:25
29Jun2015 08:30
29Jun2015 08:35
29Jun2015 08:40
.....
.....
....
....
29Jun2015 23:55
30Jun2015 00:00
30Jun2015 00:05
30Jun2015 00:10
30Jun2015 00:15
30Jun2015 00:20

I tried to add the based on the datepart minute however its getting added only to 00:00 and not after that.

View 9 Replies View Related

SQL Server 2012 :: Increment Query Based On Two Tables?

Feb 17, 2014

I have two tables with this info:

TABLE 1

COL1 COL2 COL3
AAA BBB CCC
QQQ WWW EEE
AAA SSS DDD
WWW EEE RRR
BBB BBB BBB

TABLE 2

COL1 COL2 COL3 COL4
b b b 343
a a a 344
c c c 345
d d d 346
e e e 347

I want to insert TABLE 1 into TABLE 2 with a query that will auto increment to COL4 looking like this:

COL1 COL2 COL3 COL4
b b b 343
a a a 344
c c c 345
d d d 346
e e e 347
AAA BBB CCC 348
QQQ WWW EEE 349
AAA SSS DDD 350
WWW EEE RRR 351
BBB BBB BBB 352

I know this can be done easily by just altering the column to have an auto-increment datatype, but I currently cannot do that at this moment.

View 5 Replies View Related

Cursor To Increment A Value In A New Column

Nov 24, 1999

I am trying to update a new column called "sorting", with incremented values
strating at 1 for the first row and 2 for the second and 3 for the thirsd etc......., before doing that I need the table to be sorted using three
columns. I am using a cursor but it is not working I need your help.
Thanks in advance:

CODE:

set @ordering_count=0
declare review_test_cursor cursor scroll keyset for

select oid,decision_date,ranking,sorting,decision_id
from DECISION_FLAGS
order by oid,decision_date,ranking asc

open review_test_cursor
fetch next from review_test_cursor

while (@@fetch_status = 0 )
Begin
update DECISION_FLAGS
set sorting = CAST ((@ordering_count + 1) as VARCHAR)
set @ordering_count = @ordering_count + 1

fetch next from review_test_cursor
end

close review_test_cursor
deallocate review_test_cursor

View 2 Replies View Related

Auto Increment On Non PK Column

May 2, 2008

How can I put an Auto increment on a non PK field?
for ordering contents items?

View 9 Replies View Related

Column That Is Not Set To Auto Increment

Oct 16, 2014

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...

View 1 Replies View Related

Create Random Numbers Within A Range?

Dec 27, 2013

s it possible to create random numbers within a range like : .28 to .57 ?

UPDATE Nop_ProductVariant
SET percent = RAND() .28 to .57

So the percent field would look like :

ProdID percent
1001 .38
1002 .29
1003 .40
etc . .

all rows having a different value

View 12 Replies View Related

How To Use Cursor To Create Re-occuring Numbers?

Jun 9, 2007

SELECT pid, lname, visit_date, quantity
FROM customer
ORDER BY pid

pid lname visit_date quantity
-------------------------------------------
23 wang 07/23/2006 100
23 wang 07/30/2006 140
23 wang 08/05/2006 130
23 wang 08/15/2006 135
23 wang 08/22/2006 110
34 linden 06/23/2006 99
34 linden 07/06/2006 110
34 linden 07/15/2006 120
34 linden 08/26/2006 99

How do I use cursor (or is there a better way) to create an additional column "index"? "Index" starts at 1 for every unique pid

index pid lname visit_date quantity
----------------------------------------------------------
1 23 wang 07/23/2006 100
2 23 wang 07/30/2006 140
3 23 wang 08/05/2006 130
4 23 wang 08/15/2006 135
5 23 wang 08/22/2006 110
1 34 linden 06/23/2006 99
2 34 linden 07/06/2006 110
3 34 linden 07/15/2006 120
4 34 linden 08/26/2006 99

Please let me know if I can explain the my question better. Thanks in advance

View 4 Replies View Related

SQL Server 2012 :: Increment Date Based On Time Value Automatically

Jul 1, 2015

I have a client data which has the candidate id, a start date which will have only the date in varchar format and a time value associated to that date in varchar format in a seperate column.

To give a brief explanation about the data a candidate will come to study center at any time point in a day for example 10:00 AM on 20-10-2014. The start date will be 20-10-2014 and from 10:00 AM he will have test based on the time point. So if the time point is 2 HR, then at 12:00 PM he will have a test. If the time point is 8 HR, it will get added with 10:00 AM and based on this he will have the test at 06:00 PM. When the timepoint reaches 00:00 the start date needs to be the next date instead of 20-10-2014.

The start date needs to be appended with the time value such that when it crosses over the time 00:00, the start date needs to get increased by 1 ie the next day. I have added the start date to the time by using the code below

CAST(STARTDATE as datetime) + CAST(CAST(STUFF([TIME],3,0,':') as time(0)) as datetime) as [EXPECTEDDATETIME]

By this above code i have created the expected datetime however

I will not be able to hardcode the value due to dynamic data. I tried to apply >= and < to the time value something like

case when MyTime >= '00:00' and MyTime < '08:10' the Dateadd(day, 1, date)

This works perfect but my concern is that I cannot put the value 08:10 because it will not a constant value for all rows.

I have provided a screenshot of my data and a expected date column for reference.

Candidate StartDateStartTimeExpected DateTime Timepoint
1 20141020 1000 2014-10-20 10:00:00 0 HR
1 201410201200 2014-10-20 12:00:00 02 HR
1 201410201400 2014-10-20 14:00:00 04 HR
1 201410201800 2014-10-20 18:00:00 08 HR
1 201410200000 2014-10-21 00:00:00 12 HR
1 201410201200 2014-10-21 12:00:00 24 HR
2 20141020 1100 2014-10-20 11:00:00 0 HR

[Code] ....

I have also attached the data for reference.

View 9 Replies View Related

Identity Column-- Seed/Increment Value

Oct 1, 1998

I have created a table that generates a sequential id and a
stored procedure that will return that id. The trouble is
no matter what I set the Seed or Increment values to, the
id will always start with #1 and increment by 1.

My table is BILLING_TIME_ID
Identity field BT_GEN_ID
(SEED 200, INCREMENT 1)

The sp is as follows:
CREATE PROCEDURE BT_NEXT_ID
AS
INSERT dbo.BILLING_TIME_ID DEFAULT VALUES
select count (*) from dbo.BILLING_TIME_ID
GO
I have double checked that Identity_Insert is set to off for
this table. (does this default to off unless it is set to on?) Since
there is only 1 field in the table, I don`t have any indexes set.

Thanks for your help!
Toni

View 1 Replies View Related

Union ALL + With Auto Increment Column??

Aug 22, 2006

Hi there,

With a Union all Query is there a way to have it also generate
an Auto Increment (number) column that is appended to the Union all results

?

View 2 Replies View Related

T-SQL (SS2K8) :: Increment A Column By 5 Starting At 5

Jun 4, 2015

I am trying to increment a column that I am creating in a select by 5's, example 5, 10, 20, 25.... first row starting at 5,

Something like

WITH CTE as
(
SELECT cast(5 as int) as myColumn from table
)

SELECT cte.myColumn + 5 from CTE

View 3 Replies View Related

DTS Auto Increment Column And Excel

Feb 8, 2008

i've posted in the wrong forum, so im posting here

hi, im having problems to import data from my excel to a sql table.

in the excel file i have exact the same fields that i have in the table excepts the primary key which is an auto increment. When i try to import data, an error that i can't insert nulls into my auto increment column.

I put enable identity insert in the edit options, but still doesnt work.

can anyone help?

thanks in advance

View 1 Replies View Related

Can I Increment The Column (c1+2) ---by Creating The Trigger

Mar 10, 2006

hi,i am a beginner to ms sql server2000i have a tablecreate table ddd (a int, b int)by table structure is a bnow when i enter a value in b column suppose '2' in column bbext time when i insert a value in the column a i have to get the valuein b as 3 is thi spossible with triggersinsert into gdg values (1,2)a b1 2insert into gdg (a) values(2)a b2 3----------------> i have to get this 3 automaticallyis there any method to get thispls help mesatish

View 1 Replies View Related

Concat With Auto-increment Column

Aug 29, 2007

I have a column with consist of customer number.e.g.
c001
c002
c003

How do I add the character "c" to the auto-incremental number everything I add?

View 7 Replies View Related

Alter Column To Have Auto-increment (identity)

Oct 27, 2006

trying to change a column that is just a INT NOT NULL column, to have an auto-increment.

Code:

ALTER TABLE [ImpExpTables].[dbo].[ImpExp_eBayLocalNeedsDeleted]
ALTER COLUMN ID int IDENTITY(1,1) NOT NULL

just gives error at IDENTITY

View 7 Replies View Related

Change Increment Value For Existing Identity Column

Jul 20, 2004

Hi,
How to Change Increment Value for existing Identity Column (MS SQL2000) ?

I know how to change the seed :
DBCC CHECKIDENT (activity, RESEED,4233596)

but I need the future id generated with step 2
4233596
4233598
4233600
I would like to do it using T-sql because I will need to do it every day after syncronising with another SQL server .

Thanks,
Natalia

View 1 Replies View Related

Change The Seed && Increment Value Of An Identity Column.

Sep 5, 2007

Dear Sir,

the following code is copied from the SQL Server Help Example.

CREATE TABLE MyCustomers2 (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, CompanyName NvarChar (50))

INSERT INTO MyCustomers2 (CompanyName) VALUES ('A. Datum Corporation')

ALTER TABLE MyCustomers2 ALTER COLUMN CustId IDENTITY (200, 2)

It gives the following error.

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'IDENTITY'

Can U please guide me abt the error.

with regards,
wilfi

View 5 Replies View Related

Transact SQL :: Key Increment Even On Failed Insert Into On One Column

May 28, 2015

The reason why cust_id started at #4 and not #1 is because I failed to insert property three times in a row for having "Tatoine" instead of "WI" or a state less than 5chars nchar(5) correct? Then when I did a valid statement, the row was created at the starting number of four. I imagine this prevents users from having duplicate cust_ids. This however is also where rollback and similar commands could be handy correct or is there something more obvious I'm missing on a failed "insert into" to not increment the cust_id. The three rows 1,2 and 3 do not exist I believe and are not null. Having null values would of contradicted the table where two columns "not null" are a requirement.

CREATE TABLE customersnew
(
cust_idINTNOT NULL IDENTITY(1,1),
cust_nameNCHAR(50)NOT NULL,
cust_addressNCHAR(50)NULL ,
cust_cityNCHAR(50)NULL ,
cust_stateNCHAR(5)NULL ,

[code]...

View 2 Replies View Related