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?
sys.columnsorinformation_schema.columns. Usestring_aggto generate the delimited list of columns (conditional date format on date/datetime/etc fields) and hard code the rest