Hi,
The part which is broken is the constraint. The constraint doesn't exist at first and the whole thing fails.
declare @FILEPATH VARCHAR(100),@FILENAME VARCHAR(100),@SQL VARCHAR(1000)
SELECT @FILEPATH='\\server_name\c\folder\'
SELECT @FILENAME= MIN(FILENAME) FROM DATAFILES --loop thru the file names
WHERE FILETYPE='ELIGIBILITY'
WHILE @FILENAME IS NOT NULL
BEGIN /*
this is the broken part. IT's purpose is to populate the table witht he name of the source file.
Example if one table was created by 20 files you can match the rows to the ascii file.
*/
SELECT @SQL='ALTER TABLE RAW_ELIGIBILITY DROP CONSTRAINT DF_RAW_ELIGIBILITY_INPUT_FILE'
PRINT @SQL
EXEC (@SQL)
SELECT @SQL=''
SELECT @SQL='ALTER TABLE RAW_ELIGIBILITY ADD CONSTRAINT DF_RAW_ELIGIBILITY_INPUT_FILE
DEFAULT '''+
@FILEPATH+@FILENAME+'''
FOR INPUT_FILE'
PRINT(@SQL)
exec (@sql)
SELECT @SQL=''
SELECT @SQL='
BULK INSERT database.DBO.table_name
FROM ''\\server_name\c\folder\'+@FILENAME +'''
WITH
(
FORMATFILE = ''F:\local_drive\format_file.fmt''
, TABLOCK
) '
PRINT @SQL
exec (@sql)
--loop it
SELECT @FILENAME=MIN(FILENAME) FROM DATAFILES
WHERE FILETYPE='ELIGIBILITY' AND FILENAME>@FILENAME
END