Does uploading a 1MB file into SQL 2005 locks the table?

It should not block other inserts.  Now if your upload is more complicated & does some comparison to what's already there, the could block.

Depending on the criteria your SELECT uses, it could block while you insert the data.

So for example, if I'm uploading an email attachment and another user logs into the email system and trys to download an attachment from the same table by using this statement:

Select * from Attachment (nolock) where AttachmentId = @Id

Does the statement above execute without waiting for the upload to complete? Thanks