insert the source file as a column constraint

• ksexton

  SSCrazy

  Points: 2790

  More actions

  July 23, 2002 at 12:23 pm

  #158493

  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

• ojn

  Say Hey Kid

  Points: 673

  More actions

  July 24, 2002 at 5:50 pm

  #433328

  You want to check for the existence of DF before performing the drop.

  SELECT @sql='if object_id(''DF_RAW_ELIGIBILITY_INPUT_FILE'',''D'') is not null

  ALTER TABLE RAW_ELIGIBILITY DROP CONSTRAINT DF_RAW_ELIGIBILITY_INPUT_FILE'

  PRINT @sql

  --

  -oj

  Rac v2.1 Public Beta RELEASED.

  http://www.rac4sql.net