April 1, 2013 at 9:34 am
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!
April 1, 2013 at 9:45 am
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
IDSomeDataCreatedateCalculatedDescriptor
1first2013-04-0120130401GP00001
2second2013-04-0120130401GP00002
*/
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
April 1, 2013 at 10:06 am
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
April 2, 2013 at 6:52 am
Thanks to both of you for the reply...
Lowell, yours works perfectly!
Thanks Again
January 13, 2015 at 4:16 am
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...
January 13, 2015 at 4:35 am
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
January 13, 2015 at 7:14 am
SAMDEV (1/13/2015)
Hello ErinI 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.
January 15, 2015 at 11:43 pm
Post deleted. I thought I was in the SQL 2012 forum...
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply