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 @ 7:20 AM
Points: 148, Visits: 623
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: Yesterday @ 11:22 PM
Points: 13,706, Visits: 35,052
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: Wednesday, August 12, 2015 11:17 AM
Points: 550, Visits: 1,076
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 @ 7:20 AM
Points: 148, Visits: 623
Thanks to both of you for the reply...

Lowell, yours works perfectly!

Thanks Again
Post #1437868
Posted Tuesday, January 13, 2015 4:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 5, 2015 7:10 AM
Points: 24, Visits: 48
Hello Erin

I would like to use same technique while inserting records into existing table. Is it possible by any way?
As per my knowledge IDENTITY function can be used only with SELECT...INTO query which inserts into new table only.
I want to insert into an existing table...
Post #1650691
Posted Tuesday, January 13, 2015 4:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 24, 2015 9:31 AM
Points: 2,926, Visits: 5,410
You can generate number on fly without using identity:

SELECT CONVERT(VARCHAR(6), GETDATE(), 12) + 'GP'  + 
RIGHT('00000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR), 5) AS [ImportID]
, *
FROM sys.columns



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1650697
Posted Tuesday, January 13, 2015 7:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 6,336, Visits: 7,325
SAMDEV (1/13/2015)
Hello Erin

I would like to use same technique while inserting records into existing table. Is it possible by any way?
As per my knowledge IDENTITY function can be used only with SELECT...INTO query which inserts into new table only.
I want to insert into an existing table...


The use of ROW_NUMBER() or the use of a Tally Table.


Brandie Tarvin, MCITP Database Administrator

LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1650761
Posted Thursday, January 15, 2015 11:43 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 5:48 PM
Points: 3,971, Visits: 6,355
Post deleted. I thought I was in the SQL 2012 forum...


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1651780
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse