The multi-part identifier "column_name" could not be bound

• SSMfana

  SSC Eights!

  Points: 877

  More actions

  February 29, 2008 at 3:21 am

  #203804

  Hi ALL

  In my script below. I'm trying to update a SQL Server 2000 DB with data from SQL Server 2005. I've written a Cursor to call tables from information schema. Compare the data between 2005 and 2000 then Insert into SQL 2000 Table. But I'm getting The multi-part identifier "c.ID" could not be bound.. Can you please help me to resolve this.

  DECLARE @TableName VARCHAR(255)

  DECLARE @sql VARCHAR(1000)

  DECLARE TableCursor CURSOR FOR

  SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES

  WHERE Table_Type = 'Base Table' and Table_Name like 'L_%'

  OPEN TableCursor

  FETCH NEXT FROM TableCursor INTO @TableName

  WHILE @@FETCH_STATUS = 0

  BEGIN

  DECLARE @Line VARCHAR(1000)

  SET @Line = ''

  SELECT @Line = @Line + 'c.'+ c.Column_Name + ' , '

  FROM INFORMATION_SCHEMA.TABLES T

  JOIN INFORMATION_SCHEMA.COLUMNS C

  ON t.Table_Name = c.Table_Name

  WHERE t.Table_Name = @TableName and t.Table_Name like 'L_%'

  SELECT @sql = SUBSTRING(@Line, 1, LEN(@Line) -1)

  SELECT @sql = 'SELECT'+ ' ' + @sql

  SELECT @sql = @sql + 'INTO dbo.L_BrokerTest'

  SELECT @sql = @sql + ' '+ 'FROM'+ ' '

  SELECT @sql = @sql + @TableName + ' ' +'A' + ' '

  SELECT @sql = @sql + 'LEFT JOIN'

  SELECT @sql = @sql + '[Blake-DBN12].Staging.dbo.'

  SELECT @sql = @sql + @TableName + ' ' +'B'+ ' '

  SELECT @sql = @sql + 'ON' + ' '

  SELECT @sql = @sql + 'A.ID <> B.ID'

  --PRINT @sql

  EXEC (@Sql)

  FETCH NEXT FROM TableCursor INTO @TableName

  END

  CLOSE TableCursor

  DEALLOCATE TableCursor

• Piotr.Rodak

  SSCrazy Eights

  Points: 9376

  More actions

  February 29, 2008 at 10:58 am

  #783961

  seems that you use alias 'c' when you generate column name, but do not use this alias for the table name in the query.

  ...SELECT @Line = @Line + 'c.'+ c.Column_Name + ' , ' ...

  Piotr

  ...and your only reply is slàinte mhath

• SSMfana

  SSC Eights!

  Points: 877

  More actions

  March 3, 2008 at 12:38 am

  #784308

  Thankx

  Yes it's was alias and also the server that i was inserting to uses Sql 2000. For some reasons when you use select into it comes up with the above error. I then change to Insert....Into its then worked fine.