locked
Inserting files in File Tables: RRS feed

  • Question

  • Hi,

    Can anybody pls let me know if I can insert/copy files in File Tables through sql query in SQL server 2012? If its not possible, then what is the use of Insert command?

    Thanks,

    Santosh

     

    Wednesday, January 18, 2012 8:56 AM

Answers

  • To elaborate,

    first I had to enable use of xp_cmdshell

    then as a test I create a text file e.g. c:\test.txt

    servername is the SQL Server name e.g. sql01

    the instance is the sql instance e.g. MSSQLSERVER

    filestreamdirectoryname is the name from FILESTREAM Directory Name on the options page of the database e.g. fsdn

    filetabledirectory is the name you gave for FILETABLE_DIRECTORY when creating the FileTable e.g. fsd

    so the full command for this example would be

    xp_cmdshell 'copy c:\test.txt \\sql01\MSSQLSERVER\fsdn\fsd'

     

     

    Thursday, January 19, 2012 2:31 AM

All replies

  • Hi Santosh

    I'm not sure that there is a TSQL insert command.

    You could use something like the below.

    xp_cmdshell 'copy x:\file \\sqlserver\instance\filestreamdirectoryname\filetabledirectory'

    Other insert methods are detailed here http://msdn.microsoft.com/en-us/library/gg492083(v=sql.110).aspx#HowToLoadNew

     

    Wednesday, January 18, 2012 7:04 PM
  • I don't have any FileTable set up to play with for the moment, but I think you can insert files this way, although how useful this is can be disputed. I seem to recall that I was able to delete files this way.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, January 18, 2012 10:37 PM
  • Thanks Kevin.

    I beleive TSQL insert command is there for filetable according to:

    http://msdn.microsoft.com/en-us/library/gg509086%28v=sql.110%29.aspx

    But not sure how to use this command.

    Sorry, I could not get how to use the command you have mentioned:

    xp_cmdshell 'copy x:\file \\sqlserver\instance\filestreamdirectoryname\filetabledirectory'

     

    Thanks,

    Santosh

    Thursday, January 19, 2012 2:12 AM
  • To elaborate,

    first I had to enable use of xp_cmdshell

    then as a test I create a text file e.g. c:\test.txt

    servername is the SQL Server name e.g. sql01

    the instance is the sql instance e.g. MSSQLSERVER

    filestreamdirectoryname is the name from FILESTREAM Directory Name on the options page of the database e.g. fsdn

    filetabledirectory is the name you gave for FILETABLE_DIRECTORY when creating the FileTable e.g. fsd

    so the full command for this example would be

    xp_cmdshell 'copy c:\test.txt \\sql01\MSSQLSERVER\fsdn\fsd'

     

     

    Thursday, January 19, 2012 2:31 AM
  • I don't know if this is the correct way to do it but here goes

    My FileTable is called fst

    My file was called mytestfile.txt

     

    insert into fst (file_stream,name)

    select *, 'mytestfile.txt' as name

    from openrowset(bulk 'c:\mytestfile.txt', single_blob) as myfile

     

     

     

     

    Thursday, January 19, 2012 3:57 AM
  • Thank you so much Kevin. Its working fine for me now.
    Thursday, January 19, 2012 4:27 AM