0

I have a stored-procedure which will save a file into a filetable table: 

DECLARE @table1 TABLE (id NVARCHAR(50))
INSERT INTO FileTable1(file_stream,name,path_locator) OUTPUT inserted.stream_id INTO @table1 VALUES(@File,@FName,@SubDirectoryPath)

The problem is, SOME TIMES stored procedure raises insert duplicate key error and i don't know why. ERROR MESSAGE:

Violation of UNIQUE KEY constraint ''UQ__FileTabl__A236CBB318510CF4''. Cannot insert duplicate key in object ''dbo.FileTable1''. The duplicate key value is...

-----------------EDIT---------------------
I know what Duplicate key means, But i'm wondering that: should stream-id automatically inserted by sql server according to the following table structure? 

CREATE TABLE [dbo].[FileTable1] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [FSDataGroup]
WITH
(
  FILETABLE_DIRECTORY = N'FileTable1', FILETABLE_COLLATE_FILENAME = Arabic_CI_AS
)
Rzassar
  • 2,117
  • 1
  • 33
  • 55
  • 2
    I'm not sure what is confusing about that error message. It's literally telling you what the problem is. – DavidG Feb 12 '19 at 14:03
  • If you are wondering about the "some times" then those times you are trying to insert a key that's already there and not the other times. Also it might happen if you try to insert repeated keys on the same insert (but you are using VALUES with 1 row in this case). This also depends on the rows already inserted on your table (if you have another process that deletes, truncate or updates the key values). – EzLo Feb 12 '19 at 14:16
  • @EzLo please check edited section. – Rzassar Feb 12 '19 at 14:42
  • If this is a filetable then you are inserting a file on a path that already exists. This has to coincide with the parent_path_locator and the path_locator. – EzLo Feb 12 '19 at 14:58
  • @EzLo That's right. I need to delete already existing file first. Why don't you put it as an answer with some examples, so i can mark it? – Rzassar Feb 13 '19 at 06:54

1 Answers1

0

Can you please check your UNIQUE KEY constraint on the table columns. It seems you are trying to insert same value to unique key column.

do you have identity in your column stream_id.

Dinesh Gopal
  • 26
  • 1