Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

CoqBlog

.NET is good :-)
{ Blog de coq }

Actualités

SQL Server : Insérer des données binaire (fichiers) en base, sans outil particulier

J'avais vu cette astuce chez Greg, et j'ai eu l'occasion de m'en servir dernièrement, pour des tests avec Full Text Search pour lesquels je voulais tout simplement insérer des PDFs en base.

Il est possible, du moins avec SQL Server 2005 et sup, d'effectuer l'insertion du contenu d'un fichier en base directement en TSQL, via OPENROWSET(BULK…).
Un exemple donné, pour un import de données en colonne varbinary(max), dans la documentation d'OPENROWSET est le suivant :

USE AdventureWorks
GO
CREATE TABLE myTable(FileName nvarchar(60), 
  FileType nvarchar(60), Document varbinary(max))
GO 

INSERT INTO myTable(FileName, FileType, Document) 
   SELECT 'Text1.txt' AS FileName, 
      '.txt' AS FileType, 
      * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document
GO
Excellent, j'aime ça !

 

Maintenant, moi je veux le faire pour x documents, et sans me fatiguer plus.
En bon adepte du moindre effort, nous allons donc faire comme d'habitude appel à notre ami PowerShell qui va cette fois ci nous servir à générer le script SQL qui va bien pour les x fichiers.

Pour l'exemple, nous allons prendre la table suivante :

CREATE TABLE [dbo].[Files](
    [FileID] [bigint] IDENTITY(1,1) NOT NULL, 
    [FileName] [nvarchar](256) NOT NULL, 
    [FileExtension] [nvarchar](256) NOT NULL, 
    [FileContent] [varbinary](max) NOT NULL, 
    CONSTRAINT [PK_Files] PRIMARY KEY CLUSTERED 
    ( 
        [FileID] ASC 
    ) 
) 

Nous ne voulons donc pas seulement insérer les données binaires du fichier, mais aussi stocker son nom et son extension (qui servira pour la spécification TYPE COLUMN à la création de l'index FTS).

Nous avons donc juste besoin d'une instruction comme celle-ci pour chaque fichier :

INSERT INTO [dbo].[Files] ([FileContent], [FileName], [FileExtension]) 
SELECT BulkColumn, N'MonDoc.pdf', N'.pdf' 
FROM OPENROWSET(BULK N'D:\Temp\FTSSampleFiles\PDFs\MonDoc.pdf', 
SINGLE_BLOB) AS [File] GO
 

Voici le script PowerShell en charge du parcours récursif du répertoire source et de la génération du script SQL : 

$dir='D:\Temp\FTSSampleFiles' 
$outFile = "$dir\files.sql"
$printFrequency=100

$scriptTemplate=@"
INSERT INTO [dbo].[Files] ([FileContent], [FileName], [FileExtension]) 
SELECT BulkColumn, N'{1}', N'{2}' 
FROM OPENROWSET(BULK N'{0}', SINGLE_BLOB) AS [File] 
GO 
"@

function Process-File 
{ 
    BEGIN 
    { 
        Write-Output 'SET NOCOUNT ON' 
        Write-Output 'GO' 
        $count=0
    } 
    PROCESS 
    { 
        Write-Output ($scriptTemplate -f $_.FullName, $_.Name, $_.Extension) 
        $count++
        
        if ( $count%$printFrequency -eq 0 )
        {
            Write-Output "PRINT N'$count'"
            Write-Output 'GO'
        }
    } 
    END 
    { 
        Write-Output 'SET NOCOUNT OFF' 
        Write-Output 'GO' 
    } 
} 

dir -Recurse $dir | where { !$_.PSIsContainer } | Process-File | 
Out-File $outFile
 

Ce script est un peu long à cause de la fonction filtre, comme je voulais ajouter quelques éléments comme les PRINT, en version courte ça donnerait :

$dir='D:\Temp\FTSSampleFiles' 
$outFile = "$dir\files.sql"

$scriptTemplate=@"
INSERT INTO [dbo].[Files] ([FileContent], [FileName], [FileExtension]) 
SELECT BulkColumn, N'{1}', N'{2}' 
FROM OPENROWSET(BULK N'{0}', SINGLE_BLOB) AS [File] 
GO 
"@

dir -Recurse $dir | where { !$_.PSIsContainer } | 
%{ Write-Output ($scriptTemplate -f $_.FullName, $_.Name, $_.Extension) } | 
Out-File $outFile

 

Plus qu'à executer le script de génération, et executer le script SQL en lui même, avec sqlcmd par exemple :

PS C:\
PS> D:\Temp\FTSSampleFiles\SqlScriptGen.ps1
PS C:\
PS> sqlcmd -d 'TestDB' -i 'D:\Temp\FTSSampleFiles\files.sql'
100
200
300
400
500
600
700
800
900
1000
1100
1200
1300
1400
PS C:\
PS>

Cool non ?

Naturellement, rien ne vous empêche de modifier tout celà, par exemple pour envoyer directement les commandes au serveur sans passer par l'étape script SQL etc etc, suivant vos besoins/le volume.
Le script SQL en lui même peut sans doute être lui aussi amélioré.

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
Posted: samedi 1 décembre 2007 23:20 par coq

Commentaires

Pas de commentaires

Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Merci par Blog de Jérémy Jeanson le 10-01-2019, 20:47

- Office 365: Script PowerShell pour auditer l’usage des Office Groups de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 11:02

- Office 365: Script PowerShell pour auditer l’usage de Microsoft Teams de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 10:39

- Office 365: Script PowerShell pour auditer l’usage de OneDrive for Business de votre tenant par Blog Technique de Romelard Fabrice le 04-25-2019, 15:13

- Office 365: Script PowerShell pour auditer l’usage de SharePoint Online de votre tenant par Blog Technique de Romelard Fabrice le 02-27-2019, 13:39

- Office 365: Script PowerShell pour auditer l’usage d’Exchange Online de votre tenant par Blog Technique de Romelard Fabrice le 02-25-2019, 15:07

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01