%3CLINGO-SUB%20id%3D%22lingo-sub-1298242%22%20slang%3D%22en-US%22%3EHow%20to%20use%20COPY%20Into%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1298242%22%20slang%3D%22en-US%22%3E%3CP%3ECOPY%20Into%20provides%20a%20new%20method%20of%20loading%20data%20from%20storage%20directly%20into%20a%20target%20object%20with%20lesser%20permissions%20required%20and%20no%20additional%20objects%20or%20credentials%20to%20be%20created%20in%20order%20to%20load%20the%20data.%3C%2FP%3E%0A%3CP%3EFor%20full%20details%20on%20Parameters%20and%20Syntax%20review%20our%20documentation%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Fcopy-into-transact-sql%3Fview%3Dazure-sqldw-latest%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EPermissions%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EThe%20User%20execution%20the%20Copy%20Command%20must%20have%20the%20following%20Permissions%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EADMINISTER%20DATABASE%20BULK%20OPERATIONS%3C%2FLI%3E%0A%3CLI%3EINSERT%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ERequirements%20and%20Limitations%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWith%20COPY%20Into%20we%20DO%20NOT%20Have%20to%20create%20any%20Database%20Scoped%20Credentials%20like%20we%20must%20for%20Polybase%2C%20the%20credential%20will%20be%20defined%20in%20the%20COPY%20Into%20Statement.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEach%20File%20Type%20has%20varying%20support%20for%20credentials%20Per%20storage%20type%20so%20when%20deciding%20on%20a%20credential%20ensure%20that%20the%20storage%20and%20file%20type%20supports%20this%20method%20of%20authentication%20to%20storage.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20width%3D%22635%22%3E%0A%3CTHEAD%3E%0A%3CTR%3E%0A%3CTD%20colspan%3D%224%22%3E%3CP%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%3E%3CP%3E%3CSTRONG%3ECSV%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E%3CSTRONG%3EParquet%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3E%3CSTRONG%3EORC%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTHEAD%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3E%3CSTRONG%3EAzure%20blob%20storage%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ESAS%2FMSI%2FSERVICE%20PRINCIPAL%2FKEY%2FAAD%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ESAS%2FKEY%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ESAS%2FKEY%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3E%3CSTRONG%3EAzure%20Data%20Lake%20Gen2%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ESAS%2FMSI%2FSERVICE%20PRINCIPAL%2FKEY%2FAAD%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ESAS%2FMSI%2FSERVICE%20PRINCIPAL%2FKEY%2FAAD%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ESAS%2FMSI%2FSERVICE%20PRINCIPAL%2FKEY%2FAAD%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CEM%3EImportant%20note%3A%20%3C%2FEM%3E%3C%2FSTRONG%3E%3CEM%3EWhen%20the%20source%20file%20is%20Parquet%20or%20Orc%20an%20External%20File%20format%20is%20required%2C%20which%20is%20defined%20via%20Create%20External%20File%20Format.%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EStorage%20Level%20Permissions%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EAs%20per%20the%20table%20above%20we%20support%20certain%20credentials%20per%20file%20type%20per%20storage%20account%20type.%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ERBAC%20Storage%20Blob%20Data%20Contributor%2FReader%2FOwner%3A%20%3C%2FSTRONG%3EAAD%20%2F%20MSI%20%2F%20Service%20Principal%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ERead%20and%20List%20Permissions%3C%2FSTRONG%3E%3A%20Shared%20Access%20Signature%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EBest%20Practices%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EAvoid%20Specifying%20Wildcards%20that%20would%20expand%20over%20large%20number%20of%20files%20rather%20list%20multiple%20file%20locations%20instead%3C%2FLI%3E%0A%3CLI%3EUse%20%3CSTRONG%3Edfs%3C%2FSTRONG%3E%20endpoint%20for%20ADLS%20Gen2%20for%20better%20performance%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EExamples%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EFor%20all%20the%20examples%20below%20there%20are%202%20FROM%20Entries%2C%20one%20for%20Storage%20Account%20Gen2%20and%20the%20other%20for%20Azure%20Blob%20Storage.%20A%20data%20source%20will%20be%20either%20or%2C%20remove%20the%20entry%20which%20is%20not%20required.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EAuthenticating%20with%20Managed%20Service%20Identity%20(MSI)%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIDENTITY%3A%20A%20constant%20with%20a%20value%20of%20%E2%80%98Managed%20Identity%E2%80%99%3C%2FP%3E%0A%3CP%3EThe%20MSI%20Authentication%20type%20requires%20you%20to%20create%20the%20MSI%20via%20powershell%20for%20the%20logical%20SQL%20Server%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20order%20to%20so%20run%20the%20following%20Powershell%20commands%2C%20you%20can%20run%20this%20from%20the%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fazure%2Finstall-az-ps%3Fview%3Dazps-2.4.0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAzure%20Module%3C%2FA%3E%26nbsp%3Bif%20you%20have%20it%20installed%20or%20run%20the%20command%20from%20the%20Azure%20Portal%20using%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fcloud-shell%2Fquickstart-powershell%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3ECloudshell%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EConnect-AzAccount%3C%2FP%3E%0A%3CP%3ESelect-AzSubscription%20-SubscriptionId%20%3CYOUR-SUBSCRIPTIONID%3E%3C%2FYOUR-SUBSCRIPTIONID%3E%3C%2FP%3E%0A%3CP%3ESet-AzSqlServer%20-ResourceGroupName%20%3CYOUR-DATABASE-SERVER-RESOURCEGROUP%3E%20-ServerName%20%3CYOUR-DATABASE-SERVERNAME%3E%20-AssignIdentity%3C%2FYOUR-DATABASE-SERVERNAME%3E%3C%2FYOUR-DATABASE-SERVER-RESOURCEGROUP%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B--%20MSI%3C%2FP%3E%0A%3CP%3ECOPY%20INTO%20%3CTARGET%20table%3D%22%22%3E%3C%2FTARGET%3E%3C%2FP%3E%0A%3CP%3EFROM%20'https%3A%2F%2F%3CSTORAGEACCOUNT%3E.dfs.core.windows.net%2Fcontainer%2Ffile'%20--%20Storage%20Account%20Gen2%3C%2FSTORAGEACCOUNT%3E%3C%2FP%3E%0A%3CP%3EFROM%20'https%3A%2F%2F%3CSTORAGEACCOUNT%3E.blob.core.windows.net%2Fcontainer%2Ffile'%20--%20Azure%20Blob%20Storage%3C%2FSTORAGEACCOUNT%3E%3C%2FP%3E%0A%3CP%3EWITH%20(%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FILE_TYPE%20%3D%20'CSV'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CREDENTIAL%3D%20(IDENTITY%20%3D%20'Managed%20Identity')%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FIELDQUOTE%20%3D%20'%22'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FIELDTERMINATOR%3D'%3B'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ROWTERMINATOR%3D'%5Cn'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20DATEFORMAT%20%3D%20'ymd'%2C%3C%2FP%3E%0A%3CP%3EMAXERRORS%20%3D%2010%3C%2FP%3E%0A%3CP%3E)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EAuthenticating%20with%20Service%20Principals%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EIDENTITY%3A%20%26lt%3B%3CEM%3ECLIENTID%3C%2FEM%3E%26gt%3B%3CEM%3E%40%3COAUTH_2.0_V1_TOKEN_ENDPOINT%3E%3C%2FOAUTH_2.0_V1_TOKEN_ENDPOINT%3E%3C%2FEM%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESECRET%3A%20AAD%20Application%20Service%20Principal%20key%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExample%20%3A%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECREDENTIAL%20%3D%20(%20IDENTITY%20%3D%20'abcdefgh-a123-4567-I9jk-l1m12345678n%40%3CA%20href%3D%22https%3A%2F%2Flogin.microsoftonline.com%2F12a345bc-67d8-91ef-23gh-4i5jk678lm91%2Foauth2%2Ftoken%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Flogin.microsoftonline.com%2F12a345bc-67d8-91ef-23gh-4i5jk678lm91%2Foauth2%2Ftoken%3C%2FA%3E'%2C%20SECRET%20%3D%20'maopiqweh%2B-alskd!)%40ASlkd09k1'%20)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B--%20Service%20Principal%3C%2FP%3E%0A%3CP%3ECOPY%20INTO%20%3CTARGET%20table%3D%22%22%3E%3C%2FTARGET%3E%3C%2FP%3E%0A%3CP%3EFROM%20'https%3A%2F%2F%3CSTORAGEACCOUNT%3E.dfs.core.windows.net%2Fcontainer%2Ffile'%20--%20Storage%20Account%20Gen2%3C%2FSTORAGEACCOUNT%3E%3C%2FP%3E%0A%3CP%3EFROM%20'https%3A%2F%2F%3CSTORAGEACCOUNT%3E.blob.core.windows.net%2Fcontainer%2Ffile'%20--%20Azure%20Blob%20Storage%3C%2FSTORAGEACCOUNT%3E%3C%2FP%3E%0A%3CP%3EWITH%20(%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FILE_TYPE%20%3D%20'CSV'%2C%3C%2FP%3E%0A%3CP%3ECREDENTIAL%3D(IDENTITY%3D%20'%3CCLIENT_ID%3E%40%3COAUTH_2.0_TOKEN_ENDPOINT%3E'%2CSECRET%3D'%3CKEY%3E')%2C%26nbsp%3B%26nbsp%3B%3C%2FKEY%3E%3C%2FOAUTH_2.0_TOKEN_ENDPOINT%3E%3C%2FCLIENT_ID%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FIELDQUOTE%20%3D%20'%22'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FIELDTERMINATOR%3D'%3B'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ROWTERMINATOR%3D'%5Cn'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20DATEFORMAT%20%3D%20'ymd'%2C%3C%2FP%3E%0A%3CP%3EMAXERRORS%20%3D%2010%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EAuthentication%20with%20AAD%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWhen%20using%20AAD%20Authentication%20you%20have%20to%20be%20connected%20to%20the%20DW%20as%20the%20AAD%20User%2C%20a%20Service%20Principal%20Does%20NOT%20Qualify%20as%20an%20AAD%20User%20and%20if%20you%20want%20to%20make%20use%20of%20the%20Service%20Principal%20follow%20the%20guidelines%20above.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B--%20AAD%3C%2FP%3E%0A%3CP%3ECOPY%20INTO%20%3CTARGET%20table%3D%22%22%3E%3C%2FTARGET%3E%3C%2FP%3E%0A%3CP%3EFROM%20'https%3A%2F%2F%3CSTORAGEACCOUNT%3E.dfs.core.windows.net%2Fcontainer%2Ffile'%20--%20Storage%20Account%20Gen2%3C%2FSTORAGEACCOUNT%3E%3C%2FP%3E%0A%3CP%3EFROM%20'https%3A%2F%2F%3CSTORAGEACCOUNT%3E.blob.core.windows.net%2Fcontainer%2Ffile'%20--%20Azure%20Blob%20Storage%3C%2FSTORAGEACCOUNT%3E%3C%2FP%3E%0A%3CP%3EWITH%20(%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FILE_TYPE%20%3D%20'CSV'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FIELDQUOTE%20%3D%20'%22'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FIELDTERMINATOR%3D'%3B'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ROWTERMINATOR%3D'%5Cn'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20DATEFORMAT%20%3D%20'ymd'%2C%3C%2FP%3E%0A%3CP%3EMAXERRORS%20%3D%2010%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EAuthenticating%20with%20Storage%20account%20key%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIDENTITY%3A%20A%20constant%20with%20a%20value%20of%20%E2%80%98Storage%20Account%20Key%E2%80%99%3C%2FP%3E%0A%3CP%3ESECRET%3A%20Storage%20account%20key%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E--%20Storage%20Account%20Key%3C%2FP%3E%0A%3CP%3ECOPY%20INTO%20%3CTARGET%20table%3D%22%22%3E%3C%2FTARGET%3E%3C%2FP%3E%0A%3CP%3EFROM%20'https%3A%2F%2F%3CSTORAGEACCOUNT%3E.dfs.core.windows.net%2Fcontainer%2Ffile'%20--%20Storage%20Account%20Gen2%3C%2FSTORAGEACCOUNT%3E%3C%2FP%3E%0A%3CP%3EFROM%20'https%3A%2F%2F%3CSTORAGEACCOUNT%3E.blob.core.windows.net%2Fcontainer%2Ffile'%20--%20Azure%20Blob%20Storage%3C%2FSTORAGEACCOUNT%3E%3C%2FP%3E%0A%3CP%3EWITH%20(%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FILE_TYPE%20%3D%20'CSV'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CREDENTIAL%3D%20(IDENTITY%3D'Storage%20Account%20Key'%2C%20Secret%3D'%3CYOUR%20account%3D%22%22%20key%3D%22%22%3E'%3C%2FYOUR%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FIELDQUOTE%20%3D%20'%22'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FIELDTERMINATOR%3D'%3B'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ROWTERMINATOR%3D'%5Cn'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20DATEFORMAT%20%3D%20'ymd'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20MAXERRORS%20%3D%2010%3C%2FP%3E%0A%3CP%3E)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EORC%20File%20with%20AAD%20Authentication%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EThe%20Following%20example%20is%20to%20provide%20you%20with%20the%20required%20steps%20to%20read%20data%20from%20ORC%20files%2C%20if%20you%20are%20using%20Parquet%20then%20a%20File%20Format%20for%20Parquet%20should%20be%20created%20as%20well.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECREATE%20EXTERNAL%20FILE%20FORMAT%20%3CFILE%20format%3D%22%22%20name%3D%22%22%3E%3C%2FFILE%3E%3C%2FP%3E%0A%3CP%3EWITH%20(%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FORMAT_TYPE%20%3D%20ORC%2C%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20DATA_COMPRESSION%20%3D%20'org.apache.hadoop.io.compress.SnappyCodec'%26nbsp%3B%3C%2FP%3E%0A%3CP%3E)%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECOPY%20INTO%20%3CTARGET%20table%3D%22%22%3E%3C%2FTARGET%3E%3C%2FP%3E%0A%3CP%3EFROM%20'https%3A%2F%2F%3CSTORAGEACCOUNT%3E.dfs.core.windows.net%2Fcontainer%2Ffile'%20--%20Storage%20Account%20Gen2%3C%2FSTORAGEACCOUNT%3E%3C%2FP%3E%0A%3CP%3EFROM%20'https%3A%2F%2F%3CSTORAGEACCOUNT%3E.blob.core.windows.net%2Fcontainer%2Ffile'%20--%20Azure%20Blob%20Storage%3C%2FSTORAGEACCOUNT%3E%3C%2FP%3E%0A%3CP%3EWITH%20(%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FILE_TYPE%20%3D%20'ORC'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FORMAT%20%3D%20%3CFILE%20format%3D%22%22%20name%3D%22%22%3E%3C%2FFILE%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FIELDQUOTE%20%3D%20'%22'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FIELDTERMINATOR%3D'%3B'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ROWTERMINATOR%3D'%5Cn'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20DATEFORMAT%20%3D%20'ymd'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20MAXERRORS%20%3D%2010%3C%2FP%3E%0A%3CP%3E)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EConclusion%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EIn%20the%20article%20we%20covered%20how%20to%20execute%20COPY%20Into%20from%20a%20CSV%20File%20and%20what%20the%20basic%20requirements%20are%20to%20use%20the%20feature.%20I%20strongly%20advise%20reading%20the%20COPY%20Into%20documentation%20to%20confirm%20all%20the%20available%20options%20and%20functionality%20for%20the%20feature.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1298242%22%20slang%3D%22en-US%22%3E%3CP%3EWith%20the%20release%20of%20COPY%20Into%20we've%20provided%20simplified%20method%20of%20loading%20data%20into%20an%20object%2C%20in%20this%20article%20we%20will%20provide%20you%20examples%20for%20various%20authentication%20types.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1298242%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESynapse%20SQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20Support%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

COPY Into provides a new method of loading data from storage directly into a target object with lesser permissions required and no additional objects or credentials to be created in order to load the data.

For full details on Parameters and Syntax review our documentation here

 

Permissions

The User execution the Copy Command must have the following Permissions

  • ADMINISTER DATABASE BULK OPERATIONS
  • INSERT

 

Requirements and Limitations

With COPY Into we DO NOT Have to create any Database Scoped Credentials like we must for Polybase, the credential will be defined in the COPY Into Statement.

 

Each File Type has varying support for credentials Per storage type so when deciding on a credential ensure that the storage and file type supports this method of authentication to storage.

 

 

 

CSV

Parquet

ORC

Azure blob storage

SAS/MSI/SERVICE PRINCIPAL/KEY/AAD

SAS/KEY

SAS/KEY

Azure Data Lake Gen2

SAS/MSI/SERVICE PRINCIPAL/KEY/AAD

SAS/MSI/SERVICE PRINCIPAL/KEY/AAD

SAS/MSI/SERVICE PRINCIPAL/KEY/AAD

 

Important note: When the source file is Parquet or Orc an External File format is required, which is defined via Create External File Format.

 

Storage Level Permissions

As per the table above we support certain credentials per file type per storage account type.

RBAC Storage Blob Data Contributor/Reader/Owner: AAD / MSI / Service Principal

Read and List Permissions: Shared Access Signature

 

Best Practices

  • Avoid Specifying Wildcards that would expand over large number of files rather list multiple file locations instead
  • Use dfs endpoint for ADLS Gen2 for better performance

 

Examples:

For all the examples below there are 2 FROM Entries, one for Storage Account Gen2 and the other for Azure Blob Storage. A data source will be either or, remove the entry which is not required.

 

Authenticating with Managed Service Identity (MSI)

 

IDENTITY: A constant with a value of ‘Managed Identity’

The MSI Authentication type requires you to create the MSI via powershell for the logical SQL Server

 

In order to so run the following Powershell commands, you can run this from the Azure Module if you have it installed or run the command from the Azure Portal using Cloudshell.

 

Connect-AzAccount

Select-AzSubscription -SubscriptionId <your-subscriptionId>

Set-AzSqlServer -ResourceGroupName <your-database-server-resourceGroup> -ServerName <your-database-servername> -AssignIdentity

 

 

 -- MSI

COPY INTO <TARGET TABLE>

FROM 'https://<Storageaccount>.dfs.core.windows.net/container/file' -- Storage Account Gen2

FROM 'https://<Storageaccount>.blob.core.windows.net/container/file' -- Azure Blob Storage

WITH (

    FILE_TYPE = 'CSV',

    CREDENTIAL= (IDENTITY = 'Managed Identity'),

    FIELDQUOTE = '"',

    FIELDTERMINATOR=';',

    ROWTERMINATOR='\n',

    DATEFORMAT = 'ymd',

MAXERRORS = 10

)

 

 

Authenticating with Service Principals

IDENTITY: <CLIENTID>@<OAuth_2.0_V1_Token_EndPoint> 

SECRET: AAD Application Service Principal key

 

Example : 

CREDENTIAL = ( IDENTITY = 'abcdefgh-a123-4567-I9jk-l1m12345678n@https://login.microsoftonline.com/12a345bc-67d8-91ef-23gh-4i5jk678lm91/oauth2/token', SECRET = 'maopiqweh+-alskd!)@ASlkd09k1' )

 

 -- Service Principal

COPY INTO <TARGET TABLE>

FROM 'https://<Storageaccount>.dfs.core.windows.net/container/file' -- Storage Account Gen2

FROM 'https://<Storageaccount>.blob.core.windows.net/container/file' -- Azure Blob Storage

WITH (

    FILE_TYPE = 'CSV',

CREDENTIAL=(IDENTITY= '<client_id>@<OAuth_2.0_Token_EndPoint>',SECRET='<key>'),  

    FIELDQUOTE = '"',

    FIELDTERMINATOR=';',

    ROWTERMINATOR='\n',

    DATEFORMAT = 'ymd',

MAXERRORS = 10

 

)

 

Authentication with AAD

When using AAD Authentication you have to be connected to the DW as the AAD User, a Service Principal Does NOT Qualify as an AAD User and if you want to make use of the Service Principal follow the guidelines above.

 

 -- AAD

COPY INTO <TARGET TABLE>

FROM 'https://<Storageaccount>.dfs.core.windows.net/container/file' -- Storage Account Gen2

FROM 'https://<Storageaccount>.blob.core.windows.net/container/file' -- Azure Blob Storage

WITH (

    FILE_TYPE = 'CSV',

    FIELDQUOTE = '"',

    FIELDTERMINATOR=';',

    ROWTERMINATOR='\n',

    DATEFORMAT = 'ymd',

MAXERRORS = 10

 

)

 

Authenticating with Storage account key

 

IDENTITY: A constant with a value of ‘Storage Account Key’

SECRET: Storage account key

 

-- Storage Account Key

COPY INTO <TARGET TABLE>

FROM 'https://<Storageaccount>.dfs.core.windows.net/container/file' -- Storage Account Gen2

FROM 'https://<Storageaccount>.blob.core.windows.net/container/file' -- Azure Blob Storage

WITH (

    FILE_TYPE = 'CSV',

     CREDENTIAL= (IDENTITY='Storage Account Key', Secret='<Your Account Key>'

    FIELDQUOTE = '"',

    FIELDTERMINATOR=';',

    ROWTERMINATOR='\n',

    DATEFORMAT = 'ymd',

    MAXERRORS = 10

)

 

ORC File with AAD Authentication

The Following example is to provide you with the required steps to read data from ORC files, if you are using Parquet then a File Format for Parquet should be created as well.

 

CREATE EXTERNAL FILE FORMAT <File Format Name>

WITH ( 

    FORMAT_TYPE = ORC, 

    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec' 

);

 

COPY INTO <TARGET TABLE>

FROM 'https://<Storageaccount>.dfs.core.windows.net/container/file' -- Storage Account Gen2

FROM 'https://<Storageaccount>.blob.core.windows.net/container/file' -- Azure Blob Storage

WITH (

    FILE_TYPE = 'ORC',

    FORMAT = <File Format Name>

    FIELDQUOTE = '"',

    FIELDTERMINATOR=';',

    ROWTERMINATOR='\n',

    DATEFORMAT = 'ymd',

    MAXERRORS = 10

)

 

Conclusion:

In the article we covered how to execute COPY Into from a CSV File and what the basic requirements are to use the feature. I strongly advise reading the COPY Into documentation to confirm all the available options and functionality for the feature.