-- ===============

-- bcp 批量导出数据

-- ===============

select concat('bcp AdventureWorksDW2019.'

, a.name,'.' , b.name

, ' out ', a.name, '.', b.name, '.txt -c -t"|" -T -S. -C65001') as c

from sys.schemas a

     inner join sys.objects b on a.schema_id = b.schema_id

        where (b.name like 'dim%' or b.name like 'fact%') and b.type='u'

        order by a.name, b.name

-- 示例

bcp AdventureWorksDW2019.dbo.DimAccount out dbo.DimAccount.txt -c -t"|" -T -S. -C65001

CREATE SCHEMA raw

select concat('SELECT top 0 * INTO raw.', a.name,'_', b.name

            , ' FROM ', a.name , '.', b.name

            , '; TRUNCATE table raw.',  a.name,'_', b.name )

from sys.schemas a

     inner join sys.objects b on a.schema_id = b.schema_id

    where (b.name like 'dim%' or b.name like 'fact%')

           and b.type='u' and a.name='dbo'

    order by a.name, b.name

-- 示例

SELECT top 0 * INTO raw.dbo_DimAccount FROM dbo.DimAccount; TRUNCATE table raw.dbo_DimAccount

SELECT top 0 * INTO raw.dbo_DimCurrency FROM dbo.DimCurrency; TRUNCATE table raw.dbo_DimCurrency

NOTES: 这样做， 可用减少手动一个个建表的工作量。确保raw表是空表，无数据。

-- ===============

-- 从外部文件加载数据

-- ===============

declare @root nvarchar(400) = 'src-data/adventureworks2019dw/bcp/'

SELECT CONCAT('TRUNCATE TABLE raw.', a.name,'_', b.name ,';BULK INSERT raw.',   a.name,'_', b.name, ' FROM ''' , @root,  a.name,'.', b.name,'.txt'' WITH (DATA_SOURCE = ''azblobadvtwksblob'', FIRSTROW =1,FIELDTERMINATOR = ''|'' );')

from sys.schemas a

     inner join sys.objects b on a.schema_id = b.schema_id

    where (b.name like 'dim%' or b.name like 'fact%') and b.type='u' and a.name='dbo'

-- 示例

TRUNCATE TABLE raw.dbo_DimAccount;

BULK INSERT raw.dbo_DimAccount

FROM 'src-data/adventureworks2019dw/bcp/dbo.DimAccount.txt'

WITH (DATA_SOURCE = 'azblobadvtwksblob', FIRSTROW =1,FIELDTERMINATOR = '|' );

TRUNCATE TABLE raw.dbo_DimCurrency;

BULK INSERT raw.dbo_DimCurrency

FROM 'src-data/adventureworks2019dw/bcp/dbo.DimCurrency.txt'

WITH (DATA_SOURCE = 'azblobadvtwksblob', FIRSTROW =1,FIELDTERMINATOR = '|' );

SELECT concat('ALTER TABLE ',

          SCHEMA_NAME(a.schema_id), '.', object_name(a.parent_object_id)

       , ' drop constraint ' , a.name ,';')

FROM SYS.foreign_keys A

WHERE TYPE='F'

and   (object_name(a.parent_object_id) like 'dim%' or object_name(a.parent_object_id) like 'fact%')  and schema_name(a.schema_id)='dbo'

order by SCHEMA_NAME(a.schema_id), object_name(a.parent_object_id)

select concat('drop view ', schema_name(a.schema_id) , '.', a.name )

from sys.views a

    where (A.name like 'dim%' or A.name like 'fact%')  and schema_name(a.schema_id)='dbo'

-- ===============

-- 从raw导入数据

-- ===============

; with c1 as

(

SELECT

   isIdentity = sum(case when b.is_identity = 1 then 1 else 0 end) ,

   schemaName = SCHEMA_NAME(a.schema_id),

   tableName = a.name,

   columnNames  = STRING_AGG( '['+ b.name + ']', ',')

FROM SYS.objects a

      left join sys.columns b on a.object_id = b.object_id 

WHERE a.type='u'

     and   (A.name like 'dim%' or A.name like 'fact%')  and schema_name(a.schema_id)='dbo'

      and b.is_computed = 0

group by a.schema_id, a.name

)

SELECT

 pk_identity_prefix = case when (isIdentity >0  ) then concat( ' SET IDENTITY_INSERT ' , schemaName, '.',tableName, ' On;') else '' end

    ,

   load_script=concat('TRUNCATE TABLE ', schemaName, '.',tableName

   , '; INSERT INTO ' ,schemaName, '.',tableName , '(',columnNames,')'

   ,  ' SELECT ',  columnNames

          ,' FROM raw.',schemaName, '_',tableName,' ;')

   ,

 pk_identity_prefix = case when (isIdentity >0  ) then concat( ' SET IDENTITY_INSERT ' , schemaName, '.',tableName, ' OFF;') else '' end

FROM c1

order by  schemaName,  tableName

--打开IDENTITY_INSERT

SET IDENTITY_INSERT dbo.DimAccount On;

--清空

TRUNCATE TABLE dbo.DimAccount;

--插入

INSERT INTO dbo.DimAccount([AccountKey],[ParentAccountKey],[AccountCodeAlternateKey],[ParentAccountCodeAlternateKey],[AccountDescription],[AccountType],[Operator],[CustomMembers],[ValueType],[CustomMemberOptions])

SELECT [AccountKey],[ParentAccountKey],[AccountCodeAlternateKey],[ParentAccountCodeAlternateKey],[AccountDescription],[AccountType],[Operator],[CustomMembers],[ValueType],[CustomMemberOptions] FROM raw.dbo_DimAccount ;

--还原

SET IDENTITY_INSERT dbo.DimAccount OFF;

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe

usage: bcp {dbtable | query} {in | out | queryout | format} datafile

  [-m maxerrors]            [-f formatfile]          [-e errfile]

  [-F firstrow]             [-L lastrow]             [-b batchsize]

  [-n native type]          [-c character type]      [-w wide character type]

  [-N keep non-text native] [-V file format version] [-q quoted identifier]

  [-C code page specifier]  [-t field terminator]    [-r row terminator]

  [-i inputfile]            [-o outfile]             [-a packetsize]

  [-S server name]          [-U username]            [-P password]

  [-T trusted connection]   [-v version]             [-R regional enable]

  [-k keep null values]     [-E keep identity values][-G Azure Active Directory Authentication]

  [-h "load hints"]         [-x generate xml format file]

  [-d database name]        [-K application intent]  [-l login timeout]