How to create a dynamic table
I have a dynamic pivot query that generates a result set and I want to insert this data into a table. But the problem is that the columns are dropped or generated by the point in time. Therefore, at that point, I cannot predict the columns. This is why I created a dynamic rotating dataset. So how do you insert this dataset into a table?
One solution is to drop and re-create the table every time, but I don't know how. I've tried table CTE
, TEM
P, but EXEC
only supports select, insert, update, delete:
DECLARE @columns NVARCHAR(MAX), @sqlquery NVARCHAR(MAX), @orderby Nvarchar(MAX),@value Nvarchar(max);
SET @columns = N'';
SET @value=N'0'
SELECT @columns += N', ' + QUOTENAME([Note_Type])
FROM
(
SELECT distinct
No_T
FROM [DS_DM].[dbo].[DAILY_TABLE]
where No_T not in (570,80,150,590,80,99)
)as A order by No_T
SET @sqlquery = N'
Select
K._Number
,D.C_Number
,' + STUFF(@columns, 1, 2, '') + '
from
(
select
_Number
,' + STUFF(@columns, 1, 2, '') + '
from
(
select distinct
right(REPLICATE('+@value+',11) +_Number,11) as [_Number]
,No_t
,No_T_Des
FROM [DS_DM].[dbo].[DAILY_TABLE]
where No_T not in (570,80,150,590,80,99)
)AS J
pivot
(
count(No_T_Des) FOR [No_t] IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
)P
)K
left join
[DS_DM].[dbo].[D_TABLE] D on k._Number = D._Number
';
EXEC sp_executesql @sqlquery
source to share
So, I found the answers to my questions.
there are 2 fields that repeat each time. so I created an ETL that drops this table and recreates every day with these two files. and the remaining 66 columns that are dynamic (blobs or newly created) every day. so I drew a cursor to cycle through all these categories and change the table that was created and added this code to ETL
So Basically Every Day ETL Runs Drops Existing Table. Create a new table with two static files and modify the same table with the dynamic file cursor.
source to share
I have modified my code to reflect our proposed solution.
IF OBJECT_ID (N'NEW_TABLE', N'U') IS NOT NULL
BEGIN
DROP TABLE NEW_TABLE
END
DECLARE @columns NVARCHAR(MAX), @sqlquery NVARCHAR(MAX), @orderby Nvarchar(MAX),@value Nvarchar(max);
SET @columns = N'';
SET @value=N'0'
SELECT @columns += N', ' + QUOTENAME([Note_Type])
FROM
(
SELECT distinct
No_T
FROM [DS_DM].[dbo].[DAILY_TABLE]
where No_T not in (570,80,150,590,80,99)
)as A order by No_T
SET @sqlquery = N'
Select
K._Number
,D.C_Number
,' + STUFF(@columns, 1, 2, '') + '
INTO NEW_TABLE
from
(
select
_Number
,' + STUFF(@columns, 1, 2, '') + '
from
(
select distinct
right(REPLICATE('+@value+',11) +_Number,11) as [_Number]
,No_t
,No_T_Des
FROM [DS_DM].[dbo].[DAILY_TABLE]
where No_T not in (570,80,150,590,80,99)
)AS J
pivot
(
count(No_T_Des) FOR [No_t] IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
)P
)K
left join
[DS_DM].[dbo].[D_TABLE] D on k._Number = D._Number
';
EXEC sp_executesql @sqlquery
source to share