Appending Auto incrementing id to another field 1

[jgurgen]

I have a table that contains ArchiveID (PK, AutoIncrementing) and FileName. When inserting an item into the database i would like to insert the filename originally spreadsheet.xls as spreadsheet141.xls where the 141 is the archiveid that is given on insert. is there a way to append this before the insert? Something like FileName+ArchiveID+Extension as FileName type thing?

 

[bborissov]

If you have FileName and Id why you need computed column? You always could get your desired filename afterward by just concatenation filename and the id:

SELECT FileName + CAST(Id as varchar(20)) AS RealFileName
       FROM MyTable

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[jgurgen]

because im not inserting the id its being done on the insert so i dont know what number its going to give

 

[bborissov]

Two passes:

DECLARE @id int
INSERT INTO YourTable (FileName) VALUES ('spreadsheet')
SET @id = SCOPE_IDENTITY()
UPDATE YourTable SET FileName + CAST(@id as varchar(10))+'.xls' WHERE ID = @Id

You could make this as a store procedure:

CREATE PROCEDURE MyInsert(
@FullFileName varchar(200) = ''
)
AS 
 BEGIN
      IF ISNULL(@FileName,'') = ''
         RETURN
 
      DECLARE @FileName varchar(200)
      DECLARE @fileEXT varchar(200)
      DECLARE @id int
      SET @fileEXT = RIGHT(@FullFileName,4)
      SET @FileName = REPLACE(@FullFileName, @fileEXT '')
      INSERT INTO YourTable (FileName) VALUES (@FileName)
      SET @id = SCOPE_IDENTITY()
      UPDATE YourTable
             SET FileName + 
                 CAST(@id as varchar(10))+
                 @fileEXT
      WHERE ID = @Id

Use it:

EXEC  MyInsert([spreadsheet.xls])

That code assumes that you always will pass filename WITH extension.
not tested



Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[r937]

the easiest way to handle this is with a computed column

just declare the concatenation in the CREATE TABLE statement

there is no need for an actual extra physical column

r937.com | rudy.ca

 

[jgurgen]

i went with the long way on doing this, inserting, selecting the id then updating. Its not the way i wanted to but it works for now and im going to look into bborisov's method in the future to clean it up.

Thanks for the help

 

[r937]

have a look at a computed column

there is no need for an actual extra physical column

r937.com | rudy.ca

 

[gmmastros]

jgurgen,

I encourage you to follow r937's advice. It's simpler and faster.

With a computed column, there is no physical storage required. The value is computed each time it is retrieved. In every other way, it acts like a real column.

The syntax for adding a computed column is...

Alter Table YourTable
Add   ArchiveFile As ParseName(FileName, 2) + Convert(VarChar(20), ArchiveId) + '.' + ParseName(FileName, 1)

To test....

Select * from YourTable

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom