topic Re: Reference file name when using COPY INTO? in Data Engineering

Reference file name when using COPY INTO?

cnjrules — Thu, 27 Apr 2023 21:25:24 GMT

When using the COPY INTO statement is it possible to reference the current file name in the select staement? A generic example is shown below, hoping I can log the file name in the target table.

COPY INTO my_table

FROM (SELECT key, index, textData, 'constant_value', ?FILE_NAME?

FROM 'abfss://container@storageAccount.dfs.core.windows.net/base/path')

FILEFORMAT = CSV

PATTERN = 'folder1/file_[a-g].csv'

FORMAT_OPTIONS('header' = 'true')

Re: Reference file name when using COPY INTO?

Anonymous — Fri, 28 Apr 2023 17:36:47 GMT

@Michael L

Yes, it is possible to reference the current file name in the SELECT statement when using the COPY INTO statement. The placeholder for the current file name is @1. You can use this placeholder in your SELECT statement to reference the current file name. Here is an example of how to use it:

COPY INTO my_table
FROM (
  SELECT key, index, textData, 'constant_value', @1 AS file_name
  FROM 'abfss://container@storageAccount.dfs.core.windows.net/base/path'
)
FILEFORMAT = CSV
PATTERN = 'folder1/file_[a-g].csv'
FORMAT_OPTIONS('header' = 'true')

In this example, the @1 placeholder is used to reference the current file name and it is aliased as file_name in the SELECT statement. This value will be inserted into the my_table target table as a column.

Re: Reference file name when using COPY INTO?

cnjrules — Fri, 28 Apr 2023 22:35:38 GMT

Thanks for the response. I tried that but received a PARSE_SYNTAX_ERROR on the @1 reference. Any chance you know of any documentation on that placeholder?

Re: Reference file name when using COPY INTO?

cnjrules — Mon, 08 May 2023 19:45:52 GMT

Found the info I was looking for on the page below:

https://docs.databricks.com/ingestion/file-metadata-column.html

Re: Reference file name when using COPY INTO?

dalcuovidiu — Sat, 23 Aug 2025 16:02:53 GMT

In sql, with a subselect works just fine

%sql

COPY INTO tabele_copy_into_test from (

SELECT

user_id,

email,

_metadata.file_name AS source_file

from "/Volumes/dbacademy_ecommerce/v01/raw/users-historical/")

FILEFORMAT = PARQUET

COPY_OPTIONS ('mergeSchema' = 'true');