0

I have to provide a text file for an application that uses SQL loader to import data. Therefore I require both a ctl file and a ldr file. I am just wondering if there is an easy way to generate a ctl file as shown below:

LOAD DATA
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE DATE "dd/mm/yyyy",
SAL,
COMM,
DEPTNO
)

In the the example above I have a ctl file that loads data into a table called emp. The text contained within the brackets are the columns that correlate with the columns in the ldr file. I would like to be able to generate this output into a text file from SQL Server, specifically the 'LOAD DATA' and 'APPEND' statements. Is there an efficient way to do this?

5
  • SQL Loader is an Oracle tool. Can you clarify how this relates to SQL Server? Commented Nov 29, 2023 at 19:01
  • @DavidBrowne-Microsoft I have to create an extract of data stored in SQL Server for an external financial application that will load that data using SQL Loader from Oracle. I have to create the and ctl and ldl files and put it in a directory where the application will pick it up. Commented Nov 29, 2023 at 19:18
  • Among many options, you can use the BCP tool to extract data from SQL Server to a file. learn.microsoft.com/en-us/sql/tools/… Commented Nov 29, 2023 at 19:28
  • @DavidBrowne-Microsoft is it possible to extract the data of the table in the format given in the example shown in the original post. i.e. some text on the first few lines and then the column names of the extracted tables listed between brackets Commented Nov 29, 2023 at 20:03
  • 1
    Script it from sys.columns or information_schema.columns. Use string_agg to generate the delimited list of columns (conditional date format on date/datetime/etc fields) and hard code the rest Commented Nov 30, 2023 at 0:45

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.