microsoft.public.sqlserver.programming

Rafael Lenartowicz

3/27/2007 11:33:00 AM

if you need the "tag" being part of your unique ID I'd keep the "tag" in
separate colum and create a identity column. All you need to do after that
is to combine these two during retrieve.
r
combine these two during retrieve.
"Geo" <no@spam.com> wrote in message
news:%23JRbt9FcHHA.1148@TK2MSFTNGP02.phx.gbl...
> Guys, I have to extract old data from one table and insert it into a new
> table. This part is simple but the problem I have is the new table has a
> column named storyID. This doesn't exits in the old table. I thought I
> could do something along the lines of insert into new table (select from
> old table) and generate the storyID within the select statement using
> something along these lines
> DECLARE @CNT INT
> DECLARE @TAG NVARCHAR(1000)
> DECLARE @StoryId NVARCHAR(1000)
> SET @CNT = 70
>
> WHILE(@CNT < 100)
>
>
> begin
> SET @TAG='EV0'
> SET @CNT = @CNT + 1
> SET @StoryId=CAST (@TAG AS NVARCHAR)+CAST (@CNT AS NVARCHAR)
> print @StoryId
>
>
> end
>
> If I print it out I get the results I want but if I try the following
>
> DECLARE @CNT INT
> DECLARE @TAG NVARCHAR(1000)
> DECLARE @StoryId NVARCHAR(1000)
> SET @CNT = 70
>
> WHILE(@CNT < 100)
>
>
> begin
> SET @TAG='EV0'
> SET @CNT = @CNT + 1
> SET @StoryId=CAST (@TAG AS NVARCHAR)+CAST (@CNT AS NVARCHAR)
> --print @StoryId
>
>
> end
>
>
> select
> versionNO,
> StartDate,
> EndDate,
> StoryId=@StoryId,
> EnteredBy,
> modified,
> qanumber,
> eventName,
> details,
> OnHomepage,
> DisseminationId=1,
> comDate,
> eventLoc,
> xCoord,
> yCoord,
> NewsTypeId=2,
> nnccReviewed,
> reviewedBy,
> reviewedDate,
> EnteredBy
>
> from eventsDiary
>
> where status ='scheduled'
>
>
>
>
>
>
> I the same result for all the records i.e. the storyID =EV100, does
> anybody have any thoughts on how I would achieve this using TSQL or am I
> going to have to use an other approach?
> Thanks in advance.
> Geo
>