Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Add Auto Incrementing column to my SELECT statement Expand / Collapse
Author
Message
Posted Monday, April 1, 2013 9:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:18 PM
Points: 123, Visits: 537
Along with the data that I am pulling back from query, I also need to provide an ImportID column with the following requirements:

YYMMDDGP0001, YYMMDDGP0002, YYMMDDGP0003, and so on. The 0001, 0002, and 0003 part could get big into the hundreds of thousands.

I have the YYMMDDGP part down with the following expression:

SELECT CONVERT(VARCHAR(6), GETDATE(), 12) + 'GP' AS [ImportID]

Now I need to get the Auto Incrementing number part of this. I have been researching this trying SELECT INTO a temp table using the Identity Function and declaring different variables all with no luck. If someone could point me into the right direction on this or if you have a snippet of TSQL that will work for me, I would greatly appreciate it.

Thanks Again!
Post #1437499
Posted Monday, April 1, 2013 9:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:23 AM
Points: 12,904, Visits: 31,979
my first guess at what you are asking;
you mentioned hundreds of thousands, but your pattern only allows 99999 values.

note i'm still using an identity and a default value in order to generate the code example you asked for...not using it replace them.

/*
--Results
ID SomeData Createdate CalculatedDescriptor
1 first 2013-04-01 20130401GP00001
2 second 2013-04-01 20130401GP00002
*/
CREATE TABLE Example(
ID int identity(1,1) not null primary key,
SomeData varchar(30),
Createdate date default getdate(),
CalculatedDescriptor AS CONVERT(varchar,Createdate,112) + 'GP' + RIGHT('00000' + convert(varchar,ID),5) )

insert into Example(SomeData) values('first'),('second')

select * from Example



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1437506
Posted Monday, April 1, 2013 10:06 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:41 PM
Points: 541, Visits: 1,045
If you're looking specifically on how to use an identity column in a select into temporary table situation the following code should give you a leg up.

 select IDENTITY (int,1,1) as Autorow, * into #temp
from
(
select 'testrow1' rowdata
union
select 'testrow2' rowdata
union
select 'testrow3' rowdata
)source

select * from #temp

Post #1437512
Posted Tuesday, April 2, 2013 6:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:18 PM
Points: 123, Visits: 537
Thanks to both of you for the reply...

Lowell, yours works perfectly!

Thanks Again
Post #1437868
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse