February 10, 2003 at 11:50 am
Hello,
I might be going about this totally the wrong way but here it is.
I have a group of 25 tables that need to be copied back into another database, updating column values.
Whats the best way to do it? I did something that might be a little over the top, but worse, it doesn't work.
I created a table called CostTables (intId,vchrTableName) populated it with the names that I want to copy. Then I wrote a cursor to create the UPDATE statement that I want:
DECLARE CopyCostTables CURSOR FAST_FORWARD FOR
SELECT vchrTableName
FROM CostTables
ORDER BY intID
DECLARE @vchrTableName varchar(30)
OPEN CopyCostTables
FETCH NEXT FROM CopyCostTables
INTO @vchrTableName
Declare @STR varchar(1000)
Set @STR=''
Declare @strhead varchar(100)
WHILE @@FETCH_STATUS = 0
BEGIN
Select @strhead='Update '+@vchrTableName+ ' set'
DECLARE TableInfo CURSOR FAST_FORWARD FOR
SELECT table_name=sysobjects.name,column_name=syscolumns.name FROM
sysobjects inner JOIN syscolumns ON sysobjects.id = syscolumns.id
inner join systypes on syscolumns.xtype=systypes.xusertype
where sysobjects.xtype='U' and sysobjects.name=''+@vchrTableName+'' and syscolumns.name<>'intID'
order by sysobjects.name,syscolumns.colid
Declare @TableName varchar(30)
Declare @ColumnName varchar(30)
Open TableInfo
Fetch Next From TableInfo
into @TableName,@ColumnName
--initial part of statement
print @strhead
WHILE @@FETCH_STATUS = 0
BEGIN
--print @TableName
--print @ColumnName
Select @STR=rtrim(@str)+'Database.'+@vchrTableName+'.'+@ColumnName+'='+@ColumnName+','
--print @STR
Fetch Next From TableInfo
into @TableName,@ColumnName
End
print @STR
set @STR=''
Close TableInfo
Deallocate TableInfo
FETCH NEXT FROM CopyCostTables INTO @vchrTableName
END --
CLOSE CopyCostTables
DEALLOCATE CopyCostTables
-=--=--=-
All that being said I get an update statement that looks like this (for 1 table):
This would be executed from Southampton_SalesAnalysis db
Update Southampton.dbo.BuildingTypes set
Southampton.dbo.BuildingTypes.intDepreciationID=Southampton_SalesAnalysis.dbo.BuildingTypes.intDepreciationID,
Southampton.dbo.BuildingTypes.vchrDescription=Southampton_SalesAnalysis.dbo.BuildingTypes.vchrDescription,
Southampton.dbo.BuildingTypes.vchrCode=Southampton_SalesAnalysis.dbo.BuildingTypes.vchrCode,
Southampton.dbo.BuildingTypes.intCondo=Southampton_SalesAnalysis.dbo.BuildingTypes.intCondo,
Southampton.dbo.BuildingTypes.lngValue=Southampton_SalesAnalysis.dbo.BuildingTypes.lngValue,
Southampton.dbo.BuildingTypes.intGroupCode=Southampton_SalesAnalysis.dbo.BuildingTypes.intGroupCode
where Southampton.dbo.BuildingTypes.intID=1
I get this error:
The column prefix 'Southampton_SalesAnalysis.dbo.BuildingTypes' does not match with a table name or alias name used in the query.
So now that I've confused everyone, including myself, it boils down to whats wrong with the update statement?
Regards,
Chris
February 10, 2003 at 3:35 pm
I dont see a join anywhere? Need to join the tables so the update makes sense.
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply