I want to build a list of table names and its column names from a view. This example has the loop and some other things removed until I get the syntax right. The error comes from using @tabname in the SET @cols statement. If I replace it with a literal, it works. I tried select @tabname into tabname and using tabname instead of @tabname, but neither works. I think I'm missing something about variable scope since it is defined, but the error says it's not. Any insights would be appreciated.
Running this returns
Msg 1087, Level 16, State 1, Line 21
Must declare the table variable "@tabname"
BEGIN
DECLARE @tabname VARCHAR(200);
DECLARE @site_id VARCHAR(12);
DECLARE @logger_field VARCHAR(30);
DECLARE @cols NVARCHAR(MAX);
DECLARE tab_cursor CURSOR READ_ONLY LOCAL
FOR
SELECT tabname, site_id, logger_field
FROM dbo.LN_SITES_V;
OPEN tab_cursor;
FETCH NEXT FROM tab_cursor INTO @tabname, @site_id, @logger_field;
SELECT @tabname AS tabname;
SELECT @site_id AS site_id;
SELECT @logger_field AS logger_field;
SET @cols = stuff((select ',' + quotename(@logger_field)
from @tabname
for xml path(''),
TYPE).value('.', 'nvarchar(max)'),1,1,'');
SELECT @cols AS cols;
You do not have a table variable named @tabname but you are doing a select from @tabname. try this
BEGIN
DECLARE @tabname VARCHAR(200);
DECLARE @site_id VARCHAR(12);
DECLARE @logger_field VARCHAR(30);
DECLARE @cols NVARCHAR(MAX);
DECLARE tab_cursor CURSOR READ_ONLY LOCAL
FOR
SELECT tabname, site_id, logger_field
FROM dbo.LN_SITES_V;
OPEN tab_cursor;
FETCH NEXT FROM tab_cursor INTO @tabname, @site_id, @logger_field;
SELECT @tabname AS tabname;
SELECT @site_id AS site_id;
SELECT @logger_field AS logger_field;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cols = stuff((select ',' + quotename(@logger_field)
for xml path(''),
TYPE).value('.', 'nvarchar(max)'),1,1,'');
SELECT @cols AS cols;
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM tab_cursor INTO @tabname, @site_id, @logger_field;
END
END;
CLOSE tab_cursor;
DEALLOCATE tab_cursor;
go
That is so close! I was using a variable @tabname, but not a table variable, so I'm going to read and understand more about that. I new to T-SQL, so I appreciate the help.
Confusing to me is that there is not FROM tabname, how does that even work?
The piece that becomes missing then is the association of table name and its columns. This gives me all of the columns but I don't have a tabname associated. The original data looks like this, although there are thousands of rows.
I ultimately want to build a sql statement that does:
select TmStamp, site_id, value from
(select TmStamp, G0025890, G0025891 from [BIG SLOUGH (STN 953)_HOURLY] where TmStamp=(select max(TmStamp) from [BIG SLOUGH (STN 953)_HOURLY])) p
unpivot
(value FOR site_id IN (G0025890, G0025891))AS unpvt;
but you might be going at this in a very really tough way, a little bit complicated maybe? It is fun to do loop and dynamic stuff but what is it you are attempting to do in a non technical, simple English way?
I agree that this is a really tough way of doing this, I'd like to ultimately use SET processing for performance, but couldn't figure out how to build that. So I reverted to the loop, which I don't think is right either. It's a hard problem to describe. I was thinking I'd ultimately to get a bunch of SELECT statements UNIONED together, and then execute the sql. That might be dumb too, this is a hard puzzle. I basically want to read a table and extract the table and its columns (which are variable in number). The final result that i care about is the last picture, where I get the max timestamp, site id (which is the column name) and its value, so one row for each site.
The column names are the data values in the logger_field column. I agree, I think I am doing this the hard way, and would love to do it with set processing as I'm worried about performance. Ultimately I want to end up with a view that shows the output above for a service to consume.
I have a table that looks like the following. This is an example of two records, there are tens of thousands of them. There will be one or more duplicate table names, which corresponds with one or more column names in the LOGGER_FIELD column.
From data in this table, Iād like to create a view that would have this sql:
select TmStamp, site_id, value from
(select TmStamp, G0025890, G0025891 from [BIG SLOUGH (STN 953)_HOURLY] where TmStamp=(select max(TmStamp) from [BIG SLOUGH (STN 953)_HOURLY])) p
unpivot
(value FOR site_id IN (G0025890, G0025891))AS unpvt;
The table [BIG SLOUGH (STN 953)_HOURLY] looks like:
The table name in the FROM is tabname in the view, and the values in LOGGER_FIELD are the column names. The ending view will look like:
Sorry for the delay, I was working on an emergency issue.
The tables have a different number of columns, and they are named differently. I have that info in the first table, where the column LOGGER_FIELD holds the column name of the tabname.
Is this assumption correct => You are adding new columns for each new site. it seems like you are tracking some wildlife refuge || nature || water data. hence new sites on each location you add a new column?
In fact I think you are creating new tables for each new site, station, etc. scary but maybe it is what it is
Yes, these are water quality site ids, so the collector adds a new table with new columns for every site, which can have one or more ids. I haven't seen more than five.
It's somewhat controlled, where I have a table of sites (tables) and their site_ids (logger_field).
It's getting the tablename and its columns out to do a select with that is the hard part.
how much control do you have on schemas of the tables and are you open to design change recommendations that could make your life less stressful and improve water quality ? or is the situation you are in fast moving and it would be hard to make changes?
Is this collector a human or some automation entity?
often, not always, a unique/quirky way of tackling an issue is an indication of a need for a design reconsideration.
It's a third-party app that monitors data loggers in the field, there isn't room for any design changes. It's difficult because they use spaces in their table names, don't follow any conventions. The tables get named as such by what the Field Tech enters from their mobile device.
create table #cleanwater(tmStamp datetime2, value float, site_id varchar(50))
BEGIN
DECLARE @tabname VARCHAR(200);
DECLARE @site_id VARCHAR(12);
DECLARE @logger_field VARCHAR(30);
DECLARE @cols NVARCHAR(MAX);
declare @sql nvarchar(max);
DECLARE tab_cursor CURSOR READ_ONLY LOCAL
FOR
SELECT distinct tabname
FROM dbo.LN_SITES_V;
OPEN tab_cursor;
FETCH NEXT FROM tab_cursor INTO @tabname;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
select @cols = STUFF(( SELECT ', ' + logger_field
FROM LN_SITES_V
where tabname = @tabname
FOR XML PATH(''),TYPE)
.value('.','NVARCHAR(MAX)'),1,2,'')
set @sql =
'insert into #cleanwater
select TmStamp, value, site_id
from
(
select top 1 TmStamp,' + @cols +
' from ' + @tabname +
' order by TmStamp desc ) p unpivot ( value FOR site_id IN (' +
@cols + ')) AS unpvt'
PRINT @sql
EXEC (@sql);
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM tab_cursor INTO @tabname;
END
END;
CLOSE tab_cursor;
DEALLOCATE tab_cursor;
select * From #cleanwater
? or is the situation you are in fast moving and it would be hard to make changes?