Select Statement Where Column Does Not Exist

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736160

  More actions

  July 22, 2020 at 4:34 pm

  #3773869

  I wouldn't think column order matters. At least, it shouldn't. Not sure dynamic SQL is better than just the loop shown above. I might actually store the 30 cols in a table and then use that to decide what to populate if it's missing.

• pietlinden

  SSC Guru

  Points: 63500

  More actions

  July 22, 2020 at 6:34 pm

  #3773927

  Just to see if I could do it, I wrote some code to do the testing for me... <g>

  I shamelessly stole Phil's checking code.... Seems to work, though.

  DROP TABLE IF EXISTS #Test2;
  
  CREATE TABLE #Test2
  (
      A INT
     ,B INT
     ,C INT
  );
  
  INSERT #Test2
  (
      A
     ,B
     ,C
  )
  VALUES
  (1, 1, 1)
  ,(2, 2, 2);
  
  CREATE TABLE ColumnList (columnName CHAR PRIMARY KEY);
  GO
  -- these are all the values that should exist as columns in the imported table.
  INSERT INTO ColumnList VALUES ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H');
  
  DECLARE @colName CHAR;
  DECLARE @SQLStmt NVARCHAR(100);
  
  DECLARE curColumns CURSOR FOR
  SELECT columnName FROM ColumnList;
  
  OPEN curColumns
  
  FETCH NEXT FROM curColumns INTO @colName;
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
  PRINT 'colName: ' + @colName;
  IF COL_LENGTH('#Test2', @colName) IS NULL
  BEGIN
  SET @SQLStmt = REPLACE ('ALTER TABLE #Test2 ADD xx INT DEFAULT NULL', 'xx',@colName);
  --print @SQLStmt;
  EXEC(@SQLStmt); -- run the ALTER TABLE to add this column.
  END;
  FETCH NEXT FROM curColumns INTO @colName;
     END
  
     CLOSE curColumns;
     DEALLOCATE curColumns;