Store procedure help

Transact-SQL
1

Hi Guy

II need to create a store procedure which will push data from a view to table ( this table keeps historic data ),

I have the view below (this is an example)

System Name Group Report date T_NAME C_NAME U_NAME
solid 1 21/09/2017 aaa bbb ccc
solid 2 22/09/2017 aab aac aar

I need to push data across from this view to the table ( which has the field name System Name,Group,Report date,Field name,Field data,Field Measure)Because the view has more fields than the table , the Field name,Field data Field Measure will store the new data .

The new store proc will need to take the system name, Group and report data from the view and insert it into the table and also insert t_name into Field name, t_name data into the field_data and a count of the data in the field measure , this needs to be repeated for c_name and U _name

The table should look like this after ( using the above view example

System Name Group Report date Field name Field data Field Measure
solid 1 21/09/2017 T_NAME aaa 1
solid 2 22/09/2017 T_NAME aab 1
solid 1 21/09/2017 C_NAME bbb 1
solid 2 22/09/2017 C_NAME aac 1
solid 1 21/09/2017 U_NAME ccc 1
solid 2 22/09/2017 U_NAME aar 1

Can this be done?

My stored proc knowledge isn’t very good I would be grateful for any help.

Thank you guys

2

Fortunately with CROSS APPLY this is actually very straightforward. I have no idea what value you wanted for "Field Measure" or how to calculate it, so I just hard-coded "1".

create table #data (
    [System Name] varchar(30) not null,
    [Group] smallint not null,
    [Report date] date not null,
    T_NAME varchar(30) not null,
    C_NAME varchar(30) not null,
    U_NAME varchar(30) not null
    )
insert into #data values
('solid', 1, '20170921','aaa','bbb','ccc'),
('solid', 2, '20170922','aab','aac','aar')

--insert into dbo.new_table ( ... )
select [System Name], [Group], [Report Date], 
    [field name], [field data], [field measure]
from #data
cross apply (
    select 'T_NAME' as [field name], t_name AS [field data], 1 as [field measure]
    union all
    select 'C_NAME', c_name, 1
    union all
    select 'U_NAME', u_name, 1
) as ca1
3

I will try this out! I know this might be a silly question can this same logic be used for oracle syntax?

4

I'm not an Oracle DBA any more (not since Oracle 8!), but, I believe that Oracle 12 has support for CROSS APPLY; CROSS APPLY is part of the ANSI SQL standard.

5

thank you sir

6

why did you add the as ca1 at the end? sorry it might be a silly question but my knowledge isnt that great

7

SQL Server requires an alias name for all subqueries/derived tables. For CROSS APPLYs, I use "ca#", with "ca" just standing for "CROSS APPLY", and 1 being the first CROSS APPLY. CROSS APPLYs can be "stacked", thus often there will be ca2, ca3, etc.. Note that alias name(s) from earlier ca(s) are available to later cas to use; that is, an alias name can be used to generate another alias name: how sweet is that!! For example:

SELECT *
FROM ( SELECT 1 AS col0 ) AS main_data
CROSS APPLY (
    SELECT col0 + 1 AS col1
) AS ca1
CROSS APPLY (
    SELECT col1 + 1 AS col2 --<<-- refers to a column from ca1
) AS ca2
8

thank you for your help