exporter la table vers le fichier avec les en-têtes de colonne (noms de colonne) en utilisant l'utilitaire bcp et SQL Server 2008

cette méthode affiche automatiquement les noms des colonnes avec vos données de ligne en utilisant BCP .

le script écrit un fichier pour les en-têtes de colonne (lu de la table INFORMATION_SCHEMA.COLUMNS ) puis ajoute un autre fichier avec les données de la table.

la production finale est combinée en TableData.csv qui a les en-têtes et les données de ligne. Il suffit de remplacer les variables d'environnement en haut pour spécifier le serveur, la base de données et le nom de la Table.

set BCP_EXPORT_SERVER=put_my_server_name_here
set BCP_EXPORT_DB=put_my_db_name_here
set BCP_EXPORT_TABLE=put_my_table_name_here

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='%BCP_EXPORT_TABLE%'; select @colnames;" queryout HeadersOnly.csv -c -T -S%BCP_EXPORT_SERVER%

BCP %BCP_EXPORT_DB%.dbo.%BCP_EXPORT_TABLE% out TableDataWithoutHeaders.csv -c -t, -T -S%BCP_EXPORT_SERVER%

set BCP_EXPORT_SERVER=
set BCP_EXPORT_DB=
set BCP_EXPORT_TABLE=

copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv TableData.csv

del HeadersOnly.csv
del TableDataWithoutHeaders.csv

notez que si vous devez fournir des justificatifs d'identité, remplacez l'option-T par-U my_username -P my_password

cette méthode a l'avantage d'avoir toujours les noms de colonne en phase avec le tableau en utilisant INFORMATION_SCHEMA.COLUMNS . L'inconvénient est qu'il crée des fichiers temporaires. Microsoft devrait vraiment corriger l'utilitaire bcp pour le soutenir.

Cette solution utilise le truc de concaténation de la ligne SQL de ici combiné avec les idées de bcp de ici

une bonne alternative est SqlCmd, car il ne comprend les en-têtes, mais il a l'inconvénient d'ajouter un rembourrage espace autour des données pour la lisibilité humaine. Vous pouvez combiner SqlCmd avec l'utilitaire GnuWin32 sed (stream editing) pour nettoyer les résultats. Voici un exemple qui a fonctionné pour moi, mais je ne peux pas garantir qu'il est à l'épreuve des balles.

d'abord, exportez les données:

sqlcmd -S Server -i C:\Temp\Query.sql -o C:\Temp\Results.txt -s"    "

le -s" " est un caractère d'onglet en double citation. J'ai trouvé que vous devez exécuter cette commande via un fichier batch, sinon L'invite de commande Windows traitera l'onglet comme une commande d'achèvement automatique et substituera un nom de fichier à la place de l'onglet.

Si Requête.sql contient:

SELECT name, object_id, type_desc, create_date
FROM MSDB.sys.views
WHERE name LIKE 'sysmail%'

alors vous verrez quelque chose comme ça dans les résultats.txt

name                                          object_id   type_desc           create_date            
-------------------------------------------   ----------- ------------------- -----------------------
sysmail_allitems                               2001442204 VIEW                2012-07-20 17:38:27.820
sysmail_sentitems                              2017442261 VIEW                2012-07-20 17:38:27.837
sysmail_unsentitems                            2033442318 VIEW                2012-07-20 17:38:27.850
sysmail_faileditems                            2049442375 VIEW                2012-07-20 17:38:27.860
sysmail_mailattachments                        2097442546 VIEW                2012-07-20 17:38:27.933
sysmail_event_log                              2129442660 VIEW                2012-07-20 17:38:28.040

(6 rows affected)

ensuite, analyser le texte en utilisant sed:

sed -r "s/ +\t/\t/g" C:\Temp\Results.txt | sed -r "s/\t +/\t/g" | sed -r "s/(^ +| +$)//g" | sed 2d | sed $d | sed "/^$/d" > C:\Temp\Results_New.txt

noter que la commande 2d signifie supprimer la deuxième ligne, la commande $d signifie supprimer la dernière ligne, et "/^$/d" supprime toutes les lignes vides.

le fichier nettoyé ressemble à ceci (bien que j'ai remplacé les onglets par | pour qu'ils puissent être visualisés ici):

name|object_id|type_desc|create_date
sysmail_allitems|2001442204|VIEW|2012-07-20 17:38:27.820
sysmail_sentitems|2017442261|VIEW|2012-07-20 17:38:27.837
sysmail_unsentitems|2033442318|VIEW|2012-07-20 17:38:27.850
sysmail_faileditems|2049442375|VIEW|2012-07-20 17:38:27.860
sysmail_mailattachments|2097442546|VIEW|2012-07-20 17:38:27.933
sysmail_event_log|2129442660|VIEW|2012-07-20 17:38:28.040

j'ai essayé de comprendre comment faire cela récemment et bien que j'aime la solution la plus populaire au sommet, il ne fonctionnerait tout simplement pas pour moi car j'avais besoin des noms pour être les alias que j'ai entrés dans le script donc j'ai utilisé des fichiers batch (avec l'aide d'un collègue) pour accomplir des noms de table personnalisés.

le fichier batch qui initie le bcp a une ligne en bas du script qui exécute un autre script qui fusionne un fichier template avec les noms d'en-tête et le fichier qui vient d'être exporté avec bcp en utilisant le code ci-dessous. Espérons que cela aide quelqu'un d'autre qui était dans ma situation.

echo Add headers from template file to exported sql files....
Echo School 0031
copy e:\genin\templates\TEMPLATE_Courses.csv + e:\genin"151900920"31\courses0031.csv e:\genin\finished\courses0031.csv /b

j'avais le même problème. J'avais besoin d'exporter l'en-tête de colonne en utilisant L'utilitaire SQL server bcp.De cette façon, j'ai exporté des "en-têtes" de table avec des données dans le même fichier exporté en une seule fois.

DECLARE @table_name  VARCHAR(50) ='mytable'
DECLARE @columnHeader VARCHAR(8000)
SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''+column_name +'''' FROM Nal2013.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@table_name
SELECT @raw_sql = 'bcp "SELECT '+ @columnHeader +' UNION ALL SELECT * FROM mytable" queryout c:\datafile.csv -c -t, -T -S '+ @@servername
EXEC  xp_cmdshell @raw_sql

codage Heureux :)

de ce que je sais, BCP exporte seulement les données - Je ne pense pas qu'il y ait un moyen de le faire exporter la ligne d'en-tête avec les noms de colonne, aussi.

une technique commune vue pour résoudre ceci est d'utiliser une vue sur vos données réelles pour l'exportation, qui fait essentiellement une UNION sur deux déclarations:

• la première déclaration à rendre une rangée avec les en-têtes de colonne
• les données réelles à exporter

et ensuite utiliser bcp sur cette vue, au lieu de votre table de données sous-jacente directement.

Marc

ainsi que la solution de marc_s, vous pouvez également utiliser osql ou sqlcmd

cela comprend les en-têtes et il peut agir comme bcp en utilisant-Q et-O. Cependant, ils ne prennent pas en charge les fichiers de format comme bcp.

Voici une procédure stockée assez simple qui fait le tour aussi bien..

    CREATE PROCEDURE GetBCPTable
    @table_name varchar(200)
AS
BEGIN
    DECLARE @raw_sql nvarchar(3000)

    DECLARE @columnHeader VARCHAR(8000)
    SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''+column_name +'''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name

    DECLARE @ColumnList VARCHAR(8000)
    SELECT @ColumnList = COALESCE(@ColumnList+',' ,'')+ 'CAST('+column_name +' AS VARCHAR)' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name

    SELECT @raw_sql = 'SELECT '+ @columnHeader +' UNION ALL SELECT ' + @ColumnList + ' FROM ' + @table_name 
    --PRINT @raw_SQL
    EXECUTE sp_executesql  @raw_sql
END
GO

les versions de tout le monde font les choses un peu différemment. C'est la version que j'ai développé au fil des ans. Cette version semble en compte tous les problèmes que j'ai rencontrés. Simplement remplir un ensemble de données dans une table, puis passer le nom de la table à cette procédure stockée.

j'appelle cette procédure stockée comme ceci:

EXEC    @return_value = *DB_You_Create_The_SP_In*.[dbo].[Export_CSVFile]
        @DB = N'*YourDB*',
        @TABLE_NAME = N'*YourTable*',
        @Dir = N'*YourOutputDirectory*',
        @File = N'*YourOutputFileName*'

il y a aussi deux autres variables:

• @NullBlanks -- ce sera prendre n'importe quel champ qui n'a pas de valeur et la valeur null. C'est utile parce que dans le vrai sens du CSV spécification chaque point de données doit être accompagné de guillemets. Si vous avoir un grand ensemble de données, ce qui vous fera gagner une bonne quantité d'espace par n'ayant pas "" (deux guillemets) dans ces domaines. Si vous ne le trouvez pas utile, mettez-le à 0.
• @IncludeHeaders -- j'ai une procédure stockée pour la sortie CSV fichiers, donc j'ai ce drapeau dans le cas où je ne veux pas tête.

cela créera la procédure stockée:

CREATE PROCEDURE [dbo].[Export_CSVFile] 
(@DB varchar(128),@TABLE_NAME varchar(128), @Dir varchar(255), @File varchar(250),@NULLBLANKS bit=1,@IncludeHeader bit=1)
AS

DECLARE @CSVHeader varchar(max)=''  --CSV Header
, @CmdExc varchar(max)=''           --EXEC commands
, @SQL varchar(max)=''              --SQL Statements
, @COLUMN_NAME varchar(128)=''      --Column Names
, @DATA_TYPE varchar(15)=''         --Data Types

DECLARE @T table (COLUMN_NAME varchar(128),DATA_TYPE varchar(15))

--BEGIN Ensure Dir variable has a backslash as the final character
IF NOT RIGHT(@Dir,1) = '\' BEGIN SET @Dir=@Dir+'\' END
--END

--BEGIN Drop TEMP Table IF Exists
SET @SQL='IF (EXISTS (SELECT * FROM '+@DB+'.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TEMP_'+@TABLE_NAME+''')) BEGIN EXEC(''DROP TABLE ['+@DB+'].[dbo].[TEMP_'+@TABLE_NAME+']'') END'
EXEC(@SQL)
--END

SET @SQL='SELECT COLUMN_NAME,DATA_TYPE FROM '+@DB+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='''+@TABLE_NAME+''' ORDER BY ORDINAL_POSITION'
INSERT INTO @T
EXEC (@SQL)

SET @SQL=''
WHILE exists(SELECT * FROM @T)
    BEGIN
        SELECT top(1) @DATA_TYPE=DATA_TYPE,@COLUMN_NAME=COLUMN_NAME FROM @T
        IF @DATA_TYPE LIKE '%char%' OR @DATA_TYPE LIKE '%text'
            BEGIN 
            IF @NULLBLANKS = 1
                BEGIN
                    SET @SQL+='CASE PATINDEX(''%[0-9,a-z]%'','+@COLUMN_NAME+') WHEN ''0'' THEN NULL ELSE ''"''+RTRIM(LTRIM('+@COLUMN_NAME+'))+''"'' END AS ['+@COLUMN_NAME+'],' 
                END
            ELSE
                BEGIN
                    SET @SQL+='''"''+RTRIM(LTRIM('+@COLUMN_NAME+'))+''"'' AS ['+@COLUMN_NAME+'],' 
                END
            END
        ELSE
            BEGIN SET @SQL+=@COLUMN_NAME+',' END
            SET @CSVHeader+='"'+@COLUMN_NAME+'",'
            DELETE top(1) @T
    END 

IF LEN(@CSVHeader)>1 BEGIN SET @CSVHeader=RTRIM(LTRIM(LEFT(@CSVHeader,LEN(@CSVHeader)-1))) END

IF LEN(@SQL)>1 BEGIN SET @SQL= 'SELECT '+ LEFT(@SQL,LEN(@SQL)-1) + ' INTO ['+@DB+'].[dbo].[TEMP_'+@TABLE_NAME+'] FROM ['+@DB+'].[dbo].['+@TABLE_NAME+']' END
EXEC(@SQL)

IF @IncludeHeader=0 
    BEGIN
        --BEGIN Create Data file
        SET  @CmdExc ='BCP "'+@DB+'.dbo.TEMP_'+@TABLE_NAME+'" out "'+@Dir+'Data_'+@TABLE_NAME+'.csv" /c /t, -T' 
        EXEC master..xp_cmdshell @CmdExc
        --END
        SET  @CmdExc ='del '+@Dir+@File EXEC master..xp_cmdshell @CmdExc
        SET  @CmdExc ='ren '+@Dir+'Data_'+@TABLE_NAME+'.csv '+@File EXEC master..xp_cmdshell @CmdExc
    END 
else
    BEGIN

        --BEGIN Create Header and main file
        SET  @CmdExc ='echo '+@CSVHeader+'> '+@Dir+@File EXEC master..xp_cmdshell @CmdExc
        --END

        --BEGIN Create Data file
        SET  @CmdExc ='BCP "'+@DB+'.dbo.TEMP_'+@TABLE_NAME+'" out "'+@Dir+'Data_'+@TABLE_NAME+'.csv" /c /t, -T' 
        EXEC master..xp_cmdshell @CmdExc
        --END

        --BEGIN Merge Data File With Header File
        SET @CmdExc = 'TYPE '+@Dir+'Data_'+@TABLE_NAME+'.csv >> '+@Dir+@File EXEC master..xp_cmdshell @CmdExc
        --END

        --BEGIN Delete Data File
        SET @CmdExc = 'DEL /q '+@Dir+'Data_'+@TABLE_NAME+'.csv' EXEC master..xp_cmdshell @CmdExc
        --END
    END
--BEGIN Drop TEMP Table IF Exists
SET @SQL='IF (EXISTS (SELECT * FROM '+@DB+'.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TEMP_'+@TABLE_NAME+''')) BEGIN EXEC(''DROP TABLE ['+@DB+'].[dbo].[TEMP_'+@TABLE_NAME+']'') END'
EXEC(@SQL)

la dernière version de sqlcmd ajoute l'option -w pour supprimer l'espace supplémentaire après la valeur de champ; cependant, elle ne met pas de guillemets autour des chaînes, ce qui peut être un problème sur CSV lors de l'importation d'une valeur de champ qui contient une virgule.

j'ai réussi à atteindre ceci avec le code ci-dessous. Mettez le code ci-dessous dans la nouvelle fenêtre de requête SQL Server et essayez

CREATE TABLE tempDBTableDetails ( TableName VARCHAR(500), [RowCount] VARCHAR(500), TotalSpaceKB VARCHAR(500),
                UsedSpaceKB VARCHAR(500), UnusedSpaceKB VARCHAR(500) )

    -- STEP 1 :: 
    DECLARE @cmd VARCHAR(4000)

INSERT INTO tempDBTableDetails
SELECT 'TableName', 'RowCount', 'TotalSpaceKB', 'UsedSpaceKB', 'UnusedSpaceKB'
INSERT INTO tempDBTableDetails
SELECT  
    S.name +'.'+ T.name as TableName, 
    Convert(varchar,Cast(SUM(P.rows) as Money),1) as [RowCount],
    Convert(varchar,Cast(SUM(a.total_pages) * 8 as Money),1) AS TotalSpaceKB, 
    Convert(varchar,Cast(SUM(a.used_pages) * 8 as Money),1) AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables T
INNER JOIN sys.partitions P ON P.OBJECT_ID = T.OBJECT_ID
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
INNER JOIN sys.allocation_units A ON p.partition_id = a.container_id
WHERE T.is_ms_shipped = 0 AND P.index_id IN (1,0)
GROUP BY S.name, T.name
ORDER BY SUM(P.rows) DESC

-- SELECT * FROM [FIINFRA-DB-SIT].dbo.tempDBTableDetails ORDER BY LEN([RowCount]) DESC

SET @cmd = 'bcp "SELECT * FROM [FIINFRA-DB-SIT].dbo.tempDBTableDetails ORDER BY LEN([RowCount]) DESC" queryout "D:\Milind\export.xls" -U sa -P dbowner -c'
    Exec xp_cmdshell @cmd 

--DECLARE @HeaderCmd VARCHAR(4000)
--SET @HeaderCmd = 'SELECT ''TableName'', ''RowCount'', ''TotalSpaceKB'', ''UsedSpaceKB'', ''UnusedSpaceKB'''
exec master..xp_cmdshell 'BCP "SELECT ''TableName'', ''RowCount'', ''TotalSpaceKB'', ''UsedSpaceKB'', ''UnusedSpaceKB''" queryout "d:\milind\header.xls" -U sa -P dbowner -c'
exec master..xp_cmdshell 'copy /b "d:\Milind\header.xls"+"d:\Milind\export.xls" "d:/Milind/result.xls"'

exec master..xp_cmdshell 'del "d:\Milind\header.xls"'
exec master..xp_cmdshell 'del "d:\Milind\export.xls"'

DROP TABLE tempDBTableDetails 

vous devriez être en mesure de résoudre ce problème avec une vue cte et un fichier batch contenant le code bcp. D'abord créer la vue. Puisque, son relativement simple en avant, je n'ai pas créé une table temporaire, normalement je fais

CREATE VIEW [dbo].[vwxMySAMPLE_EXTRACT_COLUMNS]
  AS
    WITH MYBCP_CTE (COLUMN_NM, ORD_POS, TXT)
     AS
      ( SELECT  COLUMN_NAME
             , ORDINAL_POSITION
             , CAST(COLUMN_NAME AS VARCHAR(MAX))
        FROM [INFORMATION_SCHEMA].[COLUMNS]   
        WHERE TABLE_NAME = 'xMySAMPLE_EXTRACT_NEW'
        AND ORDINAL_POSITION = 1

        UNION ALL

        SELECT  V.COLUMN_NAME
              , V.ORDINAL_POSITION
              , CAST(C.TXT + '|' + V.COLUMN_NAME AS VARCHAR(MAX))
        FROM [INFORMATION_SCHEMA].[COLUMNS]  V INNER JOIN MYBCP_CTE C
        ON V.ORDINAL_POSITION = C.ORD_POS+1
        AND V.ORDINAL_POSITION > 1
        WHERE TABLE_NAME = 'xMySAMPLE_EXTRACT_NEW'
      )

      SELECT CC.TXT
      FROM MYBCP_CTE CC INNER JOIN ( SELECT MAX(ORD_POS) AS MX_CNT
                                     FROM MYBCP_CTE C
                                    ) SC
      ON CC.ORD_POS = SC.MX_CNT

maintenant, créez le fichier batch. J'ai créé ça dans mon répertoire temporaire mais je suis paresseux.

  cd\ 
  CD "C:\Program Files\Microsoft SQL Server0\Tools\Binn"

  set buildhour=%time: =0%
  set buildDate=%DATE:~4,10%
  set backupfiledate=%buildDate:~6,4%%buildDate:~0,2%%buildDate:~3,2%%time:~0,2%%time:~3,2%%time:~6,2%

  echo %backupfiledate%
  pause

Le code ci-dessus crée une date à ajouter à la fin de votre fichier.. Ensuite, la première déclaration de bcp avec le point de vue à la cte récursive de concaténer tout ensemble.

  bcp "SELECT *  FROM [dbo].[vwxMYSAMPLE_EXTRACT_COLUMNS] OPTION (MAXRECURSION 300)" queryout C:\Temp\Col_NM%backupfiledate%.txt -c -t"|" -S MYSERVERTOLOGINTO -T -q
  bcp "SELECT *  FROM [myDBName].[dbo].[vwxMYSAMPLE_EXTRACT_NEW] " queryout C:\Temp16_PHYSDATA_ALL%backupfiledate%.txt -c -t"|" -S MYSERVERTOLOGINTO -T -q

fusionnez-les en utilisant la commande

  copy C:\Temp\Col_NM%backupfiledate%.txt  + C:\Temp16_PHYSDATA_ALL%backupfiledate%.txt  C:\Temp16_PHYSDATA_ALL%backupfiledate%.csv

Tous ensemble

Veuillez trouver ci-dessous une autre façon de faire la même chose. Cette procédure prend également en paramètre un nom de schéma dans le cas où vous en avez besoin pour accéder à votre table.

CREATE PROCEDURE Export_Data_NBA
@TableName nchar(50),
@TableSchema nvarchar(50) = ''

AS
DECLARE @TableToBeExported as nvarchar(50);

DECLARE @OUTPUT TABLE (col1 nvarchar(max));
DECLARE @colnamestable VARCHAR(max);
select @colnamestable = COALESCE(@colnamestable, '') +COLUMN_NAME+ ','
from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
order BY ORDINAL_POSITION

SELECT @colnamestable = LEFT(@colnamestable,DATALENGTH(@colnamestable)-1)

INSERT INTO @OUTPUT
select @colnamestable

DECLARE @selectstatement VARCHAR(max);
select @selectstatement = COALESCE(@selectstatement, '') + 'Convert(nvarchar(100),'+COLUMN_NAME+')+'',''+'
from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
order BY ORDINAL_POSITION

SELECT @selectstatement = LEFT(@selectstatement,DATALENGTH(@selectstatement)-1)

DECLARE @sqlstatment as nvarchar(max);

SET @TableToBeExported = @TableSchema+'.'+@TableToBeExported

SELECT @sqlstatment = N'Select '+@selectstatement+N' from '+@TableToBeExported

INSERT INTO @OUTPUT
exec sp_executesql @stmt = @sqlstatment

SELECT * from @OUTPUT