Как запустить файл .sql с помощью bcp в SQL Server

use -s "|" с sqlcmd. Это должно позволить вам вывести данные с полями, разделенными вкладкой.

-s для разделителя столбцов.

См. утилиту sqlcmd

Hafsa Mushtaq.

Лучший вариант - PowerShell - вы можете использовать эту удивительную функцию dbatools для импорта ваших данных https://github.com/sqlcollaborative/dbatools/blob/development/functions/Import- DbaCsvToSql.ps1 и мой скрипт для экспорта https://github.com/ktaranov/sqlserver-kit/blob/master/PowerShell/Export-SQLTableToCSV.ps1

Или вы можете использовать динамический sql для генерации всех операторов а затем выполнить его. Все хранимые процедуры ниже необходимы для решения некоторых ограничений bcp и BULK INSERT в SQL Server - bcp не может создавать файлы с заголовком, а BULK INSERT не может импортировать в таблицы с столбцом IDENTITY.

Чтобы выгрузить все таблицы в csv используя утилиту bcp и мои хранимые процедуры https://github.com/sqlcollaborative/dbatools/blob/development/functions/Import-DbaCsvToSql.ps1 и usp_PrintString:

DECLARE @tsqlCommand NVARCHAR(MAX) = '';
DECLARE @path NVARCHAR(900) = 'd:\111\NIIGAZ\333\';
DECLARE @databaseName  SYSNAME = 'NIIGAZ';
DECLARE @exludeColumns NVARCHAR(MAX) = '';  -- '[CreatedDate],[ModifiedDate],[UserID]';
DECLARE @codePage NVARCHAR(10) ='C65001';  -- '1251'
DECLARE @crlf NVARCHAR(10) = CHAR(13);
DECLARE @debug BIT = 1;

IF @debug = 0 SET NOCOUNT ON;


SELECT @tsqlCommand = @tsqlCommand + 'EXECUTE dbo.usp_bcpTableUnload @databaseName   = ''' + @databaseName + ''','              + @crlf +
                                     '                               @path           = ''' + @path + ''','                      + @crlf + 
                                     '                               @schemaName     = ''' + SCHEMA_NAME(t.[schema_id]) + ''',' + @crlf +
                                     '                               @tableName      = ''' + t.[name]        + ''','            + @crlf +
                                     '                               @excludeColumns = ''' + @exludeColumns  + ''','            + @crlf +
                                     '                               @codePage       = ''' + @codePage       + ''','            + @crlf +
                                     '                               @formatFile     = ''xml'';'                                + @crlf
FROM [sys].[tables] t
INNER JOIN [sys].[dm_db_partition_stats] ps
     ON ps.[object_id] = t.[object_id]
WHERE [index_id] < 2  -- AND SCHEMA_NAME(t.[schema_id]) IN ()
GROUP BY t.[name]
        ,t.[schema_id]
HAVING SUM(ps.[row_count]) > 1
--ORDER BY 1, 2 ASC
OPTION (RECOMPILE);

IF @debug = 1
BEGIN
    EXEC dbo.usp_PrintString @str = @tsqlCommand;
END;

IF @debug = 0
BEGIN
EXEC sp_executesql @tsqlCommand;
END

И для массового вставки этих файлов в базу данных вы можете использовать этот код и хранимую процедуру usp_PrintString :

DECLARE @debug BIT = 0;
DECLARE @tsqlCommand NVARCHAR(max) = '';
DECLARE @path NVARCHAR(1000) = '$(varPath)txt\';  -- 'd:\NII\SQL\txt\';
DECLARE @ExcludeColumns NVARCHAR(1000) = '';  -- 'CreatedDate,ModifiedDate,UserID';
DECLARE @firstRow INTEGER = 2;
DECLARE @fieldTerminator NVARCHAR(10) = '''|''';
DECLARE @crlf NVARCHAR(10) = CHAR(13);

WITH fk_tables AS (
SELECT  s1.name AS from_schema
      , o1.name AS from_table
      , s2.name AS to_schema
      , o2.name AS to_table
FROM sys.foreign_keys fk
INNER JOIN sys.objects o1 ON fk.parent_object_id = o1.object_id
INNER JOIN sys.schemas s1 ON o1.schema_id = s1.schema_id
INNER JOIN sys.objects o2 ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.schemas s2 ON o2.schema_id = s2.schema_id
/*For the purposes of finding dependency hierarchy
    we're not worried about self-referencing tables*/
WHERE NOT (s1.name = s2.name
       AND o1.name = o2.name
  )
)
, ordered_tables AS (
SELECT   s.name AS schemaName
       , t.name AS tableName
       , 0 AS table_level
FROM sys.tables AS t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN fk_tables fk ON s.name = fk.from_schema
 AND t.name = fk.from_table
WHERE fk.from_schema IS NULL
UNION ALL
SELECT fk.from_schema
 ,fk.from_table
 ,ot.table_level + 1
FROM fk_tables fk
INNER JOIN ordered_tables ot ON fk.to_schema = ot.schemaName
 AND fk.to_table = ot.tableName
)
,final AS (
SELECT DISTINCT ot.schemaName
 ,ot.tableName
 ,ot.table_level
FROM ordered_tables ot
INNER JOIN (
 SELECT schemaName
  ,tableName
  ,MAX(table_level) maxLevel
 FROM ordered_tables
 GROUP BY schemaName
  ,tableName
 ) mx ON ot.schemaName = mx.schemaName
 AND ot.tableName = mx.tableName
 AND mx.maxLevel = ot.table_level
)
SELECT @tsqlCommand = @tsqlCommand +
      'EXECUTE     dbo.usp_BulkUpload @path            = ''' + @path + ''', '                  + @crlf +
      '                               @fileName        = ''' + QUOTENAME(final.schemaName) + '.' + QUOTENAME(final.tableName) + ''', ' + @crlf +
      '                               @fileExtension   = ''txt'', '                            + @crlf +
      '                               @databaseName    = ''NIIGAZ'', '                         + @crlf +
      '                               @schemaName      = ''' + final.schemaName + ''', '       + @crlf +
      '                               @tableName       = ''' + final.tableName + ''', '        + @crlf +
      '                               @FIRSTROW        = ' + CAST(@firstRow AS NVARCHAR) + ',' + @crlf +
      '                               @CODEPAGE        = ' + N'65001' + ' ,'                        + @crlf +
      '                               @useIdentity     = ' + CASE WHEN OBJECTPROPERTY(OBJECT_ID(schemaName + '.' + tableName), 'TableHasIdentity') = 1
                                                                  THEN '1' ELSE '0'
                                                             END + ' ,'                        + @crlf +
      '                               @FIELDTERMINATOR = ' + @fieldTerminator + ',' + @crlf +
      '                               @ROWTERMINATOR   = ''' + CASE WHEN final.tableName IN ('PowerPlant', 'Turbine', 'EnergoBusinessArticle') THEN N'0x0a'''
                                                                ELSE '\n'''
                                                           END + ' ,'            + @crlf +
      '                               @excludeColumns  = ''' + @ExcludeColumns + ''';'         + @crlf
  FROM final
 WHERE (
           final.schemaName NOT IN ('dbo')
           AND final.tableName NOT IN (
                 'ExcludeYourTable'
           )
           AND final.tableName NOT LIKE '%Temp'
      )
      OR final.tableName IN ('IncludeYourTable')
      OR final.tableName LIKE 'AspNet%'
      OR final.tableName LIKE 'Application%'
ORDER BY table_level;

IF @debug = 1 EXECUTE dbo.usp_PrintString @str = @tsqlCommand;
IF @debug = 0 EXECUTE sp_executesql @tsqlCommand;