OpenRowset

• bhushanhegde

  Old Hand

  Points: 382

  More actions

  December 1, 2008 at 11:56 pm

  #196756

  Hi

  Check the following code. That inserts data from one text file to one table.

  INSERT INTO temp_dept

  (dept_id,dept_name,DEPT_GRADE,DEPT_LOC,

  DEPT_HEAD,CHANGE_DT,CHANGE_TYPE,CHANGE_BY,

  ACTIVE_FLAG,SCHEDULEID)

  SELECT dept_id,lower(dept_name),DEPT_GRADE,DEPT_LOC,

  DEPT_HEAD,CHANGE_DT,CHANGE_TYPE,CHANGE_BY,

  ACTIVE_FLAG,SCHEDULEID FROM OPENROWSET

  (BULK N'D:\bcp\temp.txt',

  FORMATFILE='D:\bcp\test\ord.fmt',

  CODEPAGE ='ACP',

  FIRSTROW = 0,

  LASTROW = 0,

  ERRORFILE = 'D:\bcp\test\error.txt',

  MAXERRORS = 100,

  ROWS_PER_BATCH = 100)

  as document

  where dept_id >'0'

  Same thing is possible with 2 tables and 2 file or 2 files with one table like different combination?

  Pls let me know

  bhushan

• brainy

  SSCrazy

  Points: 2445

  More actions

  December 2, 2008 at 2:46 am

  #906096

  I think you are looking for multiple tables and files..i.e. you want to import data from multiple files to multiple tables.

  For I suggest you to use the dynamic sql and use while loop for looping for each file or for each table.

  If you have different requirement, let me know.