SQLTeam.com | Weblogs | Forums

Dynamically join views and dump data to single table

I have a bunch of views all gathering 4 fields of information with one of the fields being a datetime when data captured. I would like to dynamically read those views and based upon
a field in another table called lastcaptured pull information from the views and dump that info to a single table. When process runs it grabs lastcaptured date then uses that as a driver to pull info from views.. when last view processed it updates lastcaptured to the new datetime. The next process cycle it uses lastcaptured to pull anything equal to greater..

What updates lastcaptured?

I was thinking the process that calls all the views

since you have not provided any sort of schema sample or view sample here is one attempt

use sqlteam
go

--create table lastupdated(lastupdate date)

--insert into lastupdated
--select getdate()

drop view vw_columns;
go

create view vw_columns
as
	select top 10 c.name, o.create_date
	  from sys.columns c
	 join sys.objects o on c.object_id = o.object_id
go

declare @sql varchar(max), @lastdate date

select top 1 @lastdate = lastupdate
 from lastupdated
 order by lastupdate desc


set @sql  = 'select name, create_date  from vw_columns where create_date >= ''' + convert(varchar(10), @lastdate, 120) + ''''

print @sql;

create table #columns(name varchar(2500), dddate date)
insert into #columns
exec(@sql)


insert into lastupdated
select getdate()

select * From #columns

drop table #columns

Sorry,
All of the views return the same data elements:
Machine
Tag_Description
CaptureValue
DateRecorded
When the views run they select machines from a TagID and Tag_Description and report back
a recording(capturevalue) using the Daterecorded. So when the process runs I only want what lastupdated to compare against Daterecorded from the view. If lastupdated has record
of 6-8-2021 22:45:00 Then the process will pull any record from that datetime to current when completed update lastupdated for next run.

Many Thanks

Post table schemas and sample data?

Proper ddl and dml

Declare @sample table(id int, name varchar(50))

Insert into @sample 
Select 1, 'guguh"

The views are reading 2 tables to produce query results outlined above, do you want me to provide schema for NEW table I want the data dumped into? as well as sample data?

Many Thanks.

Yes please. The more you provide the better. Other devs here also can pick it up anytime

Why do you have 3 views that read the same table?

They where separated to distinguish the different tag data they where capturing, but basically the only difference in data is Tag_Description.

CREATE TABLE [dbo].[Tag_Capture](
    [CaptureID] int,
    [Machine] [nvarchar](35) NULL
	[TagDescription] [nvarchar](35) NULL
	CaptureValue [int] NULL,
	[DateRecorded] [datetime] NULL
) ON [PRIMARY]
GO
declare @lastdate date

select top 1 @lastdate = lastupdate
 from lastupdated
 order by lastupdate desc

insert into [dump_that_info_to_a_single_table]
select [CaptureID], [Machine], [TagDescription], 
CaptureValue,DateRecorded  
 from vw_Capture1 
 where [DateRecorded] >= @lastdate

insert into [dump_that_info_to_a_single_table]
select [CaptureID], [Machine], [TagDescription], 
CaptureValue,DateRecorded  
 from vw_Capture2 
 where [DateRecorded] >= @lastdate

insert into [dump_that_info_to_a_single_table]
select [CaptureID], [Machine], [TagDescription], 
CaptureValue,DateRecorded  
 from vw_Capture3
 where [DateRecorded] >= @lastdate


insert into lastupdated
select getdate()

If the views are all from the same source tables - why use the views at all? Why not query the source tables directly with the parameters you need?

I am assuming that the views query multiple tables to generate the results - but if the source of the views is a single table then it doesn't make sense to create yet another table with the same data.

What issue are you trying to resolve - and why do you believe creating a new table is the solution?

After getting your comments I'm thinking I should have never went down the views route
just query from source tables and gather the info with above logic
where [DateRecorded] >= @lastdate
Creating the NEW table will be the single source for another app to pull the information.