January 4, 2006 at 5:50 am
Does anyone know why this returns an error in the "FROM @table" line, saying that the variable must be declared?
DECLARE @table CHAR(10)
SET @table = 'mytable'
UPDATE xxtstats
SET xts_dw_count =
(SELECT COUNT(*)
FROM @table)
WHERE xts_table = @table
What I want to do is update a single table (xxtstats) with various statistics from other tables (row count etc) and I'm trying to do it using a cursor. The xxtstats table has a field in it called xts_table and I want to flip through each record here and update it from the other tables.
Hope that makes sense, but if not, if anyone knows a way around the problem with the bit of code at the top, I'll be able to fix everything else.
Many thanks.
January 4, 2006 at 6:03 am
Try this:
DECLARE @table CHAR(10)
SET @table = 'mytable'
EXEC ('UPDATE xxtstats
SET xts_dw_count =
(SELECT COUNT(*)
FROM [' + @table + '])
WHERE xts_table = ''' + @table + '''')
Alternatively:
DECLARE @table CHAR(10)
DECLARE @cmd varchar(1000)
SET @table = 'mytable'
set @cmd = 'UPDATE xxtstats
SET xts_dw_count =
(SELECT COUNT(*)
FROM [' + @table + '])
WHERE xts_table = ''' + @table +''''
exec sp_executesql @cmd
Hope that helps.
January 4, 2006 at 6:08 am
CREATE TABLE XXTSTATS
(
xts_dw_count INT,
xts_table VARCHAR(10)
)
GO
INSERT INTO XXTSTATS VALUES (0, 'MY_TABLE')
GO
CREATE TABLE MY_TABLE (COL1 INT)
INSERT INTO MY_TABLE VALUES (1)
INSERT INTO MY_TABLE VALUES (2)
INSERT INTO MY_TABLE VALUES (3)
GO
DECLARE @table VARCHAR(10), @CMD NVARCHAR(100)
SET @table = 'my_table'
SET @CMD = N'UPDATE xxtstats SET xts_dw_count = (SELECT COUNT(*) FROM '+@table+') WHERE xts_table = '''+@table+''''
EXEC SP_EXECUTESQL @CMD
GO
SELECT * FROM XXTSTATS
xts_dw_count xts_table
------------ ----------
3 MY_TABLE
You can do this for all the tables with a simple while loop by getting the list of the table-names into a table variable and then using rownum (create this column in the table variable using select into using identity(int, 1, 1) as rownum)..that way you can avoid a cursor as well.
January 4, 2006 at 7:18 am
Many thanks Karl and rsharma for your blindingly quick replies!
All those solutions are superb and I thank you for coming up with them and teaching me something about T-SQL!
Regards,
Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply