If column exists?

• MS Verbsthenoun

  Ten Centuries

  Points: 1153

  More actions

  December 15, 2005 at 3:09 pm

  #609905

  Excuse me for a second while I..

   

   

  JUMP UP AND DANCE A JIG!!!  😉

   

  That did the trick. Thank you ever so much.

  Here's the combined code:

   

  /* Need the intersection of column names between the 2 tables,

  Only include a column in the Insert/Select statements if the

  column is in *both* tables, Then adjust your SQL to generate

  only that subset of columns */

  Declare @sql varchar(1000)

  DECLARE @strColNames VarChar(500)

  SELECT * INTO Project01PreTemp

  FROM OpenRowSet('MSDASQL', 'Driver={Microsoft dBASE Driver (*.dbf)};

  DriverID=277;

  DBQ=C:\ReturnedProjects',

  'SELECT * FROM Proj01')

  SELECT @strColNames = isnull(@strColNames +', ', '')

  + c1.NAME

  FROM syscolumns AS c1

  INNER JOIN syscolumns as c2

    ON (c1.NAME = c2.NAME)  -- Join on column names being the same

  WHERE c1.id = object_id('Project01PreTemp')

  AND  c2.id = object_id('Project01tmp')

  Select @sql = 'INSERT INTO Project01tmp (' + @strColNames + ')SELECT '

  + @strColNames + ' FROM Project01PreTemp'

  -- Debug!!

  Print @sql

  Exec(@SQL)

  Which, when run, provides the following output:

  (60 row(s) affected)

  INSERT INTO Project01tmp (HOME_EMAIL, HOMEPHONE, INDUSTRY, LASTNAME, MARKET, MOD_DATE, PASTPART, PROJNUM, RECNUM, RECRUITER, RESULT, STATE, TITLE, URL, WORKPHONE, YOB, YRLY_REV, ZIP, ADDRESS, CELL, CITY, CLIENT_ID, COMMENTS, DATE, DB, DEPT, EMAIL, EMPLOYEES, EXT, FAX, FIRM, FIRSTNAME, GENDER)SELECT HOME_EMAIL, HOMEPHONE, INDUSTRY, LASTNAME, MARKET, MOD_DATE, PASTPART, PROJNUM, RECNUM, RECRUITER, RESULT, STATE, TITLE, URL, WORKPHONE, YOB, YRLY_REV, ZIP, ADDRESS, CELL, CITY, CLIENT_ID, COMMENTS, DATE, DB, DEPT, EMAIL, EMPLOYEES, EXT, FAX, FIRM, FIRSTNAME, GENDER FROM Project01PreTemp

  (60 row(s) affected)

   

  Now, after verifying data is not munging at all, I'll add the dynamic filename feature later.

  I post all this here, in case someone else runs into similar challenges, because I searched for days, and kept seeing people with hints, but no one posting back what they actually did to make it work, so hopefully this verbosity will help others if they ever have to deal with such a beast.

  Thank you again every so much!