February 29, 2008 at 3:21 am
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
February 29, 2008 at 10:58 am
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
March 3, 2008 at 12:38 am
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.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply