Search code examples
sql-serverazure-sql-databasebcpazure-sql-serverazure-sql

Azure SQL Server BCP -

I have a csv file with 3 columns

LastName
FirstName
EmpID

I was SQLLDR to load the data into Oracle database however I opted to use BCP to load the data into Azure sqlserver after the database migration to Azure SQL Server.

I am using the following command to generate the format

bcp Database.Schema.Persons format nul -c -t, -f Persons.fmt -S tcp:demo.database.windows.net -U User01 -P P@$$word!

It has following format

13.0
3
1       SQLCHAR             0       255     ","      1     LastName               SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       255     ","   2     FirstName              SQL_Latin1_General_CP1_CI_AS
3       SQLINT             0       255     "\r\n"   3     FirstName              SQL_Latin1_General_CP1_CI_AS

However SQLLDR ctl file has the following functions

LPAD(:EmpID, 4, '0')
DATE "YYYY-MM-DD"
TIMESTAMP "YYYY-MM-DD-HH24.MI.SS.FF6"
NVL(:LastName, '')

How do I accommodate in the BCP format file?

Solution

  • How do I accommodate in the BCP format file?

    You don't. BCP into a staging table and INSERT ... SELECT to your final table to apply transformations. Or stage the data in Azure Blob Storage and use INSERT ... .SELECT . . . FROM OPENROWSET to load the data into your target table.