Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Appending Auto incrementing id to another field 1

Status
Not open for further replies.

jgurgen

Programmer
Mar 9, 2006
192
US
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?
 
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:
Code:
SELECT FileName + CAST(Id as varchar(20)) AS RealFileName
       FROM MyTable


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP
 
because im not inserting the id its being done on the insert so i dont know what number its going to give
 
Two passes:
Code:
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:
Code:
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:
Code:
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
 
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
 
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
 
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...

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top