Using a variable table [modified]
-
I am trying to read table data from all my tables in a database to a summary table which contains the table anme in a column, but I am not sure what is the best way to go about doing this. Should I use temp tables or sp_executesql or some other method. I want to use a variable for the table name but I don't think that will work unless I split the string below in 2 and insert a table name. Or should I use temp tables which I am not familiar with or other method, if so how would I do that. I appreciate any help I can get. Thanks in advance, Michael SET @SQLString1 = N' DECLARE @dtLatestDate DATETIME SELECT @dtLatestDate = B.dtDateTime FROM ( SELECT TOP (1) dtDateTime FROM ' (split here the add tablename, see below) @TableName SET @SQLString2 = N' ORDER BY dtDateTime DESC ) AS B' OPEN strTableName_cursor; FETCH NEXT FROM strTableName_cursor INTO @strTableName; WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_executesql @SQLString1 + @TableName + @SQLString2 UPDATE [dbo].[Summary] SET dtLatestDate = @dtLatestDate WHERE strTableName = @strTableName FETCH NEXT FROM strTableName_cursor INTO @strTableName END CLOSE strSymbol_cursor
modified on Saturday, May 14, 2011 3:50 PM
-
I am trying to read table data from all my tables in a database to a summary table which contains the table anme in a column, but I am not sure what is the best way to go about doing this. Should I use temp tables or sp_executesql or some other method. I want to use a variable for the table name but I don't think that will work unless I split the string below in 2 and insert a table name. Or should I use temp tables which I am not familiar with or other method, if so how would I do that. I appreciate any help I can get. Thanks in advance, Michael SET @SQLString1 = N' DECLARE @dtLatestDate DATETIME SELECT @dtLatestDate = B.dtDateTime FROM ( SELECT TOP (1) dtDateTime FROM ' (split here the add tablename, see below) @TableName SET @SQLString2 = N' ORDER BY dtDateTime DESC ) AS B' OPEN strTableName_cursor; FETCH NEXT FROM strTableName_cursor INTO @strTableName; WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_executesql @SQLString1 + @TableName + @SQLString2 UPDATE [dbo].[Summary] SET dtLatestDate = @dtLatestDate WHERE strTableName = @strTableName FETCH NEXT FROM strTableName_cursor INTO @strTableName END CLOSE strSymbol_cursor
modified on Saturday, May 14, 2011 3:50 PM