Dynamically join views and dump data to single table

Transact-SQL
1

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..

2

What updates lastcaptured?

3

I was thinking the process that calls all the views

4

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
5

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

6

Post table schemas and sample data?

Proper ddl and dml

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

Insert into @sample 
Select 1, 'guguh"
7

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.

8

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?

9

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
10 
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()
11

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?

12

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.