Link to home
Start Free TrialLog in
Avatar of kenuk110
kenuk110

asked on

Copy results to History table

Hi,

I have a function that runs that extracts data based on the day the function is run. This data is used each time we run the function by Crystal Reports. The data currently gets put in to a temporary table but I would also like the same data to be copied in to a HISTORY table so we can retain excatly what was extracted each day. The part of the function though that run the extract to the history table though should only run once and not repeatedly every time the function is run. I was thinking that I could simply use the date column to stop it doing this: sort of 'if date = @date then end else select in to HISTORY from ctetemp.

I'm no DBA and this function that may need to be amended is probably the most critical function we have so I'd like advice from you guys on how best to do this. I have attached the current function, maybe it's a case of changing part of it to allow the copy out in to the HISTORY table (which I also don't have), I'm really not sure.

Any assistance would be much appreciated.

Regards,

Ken


ALTER FUNCTION [dbo].[ServiceViewBatch] (@date datetime)
RETURNS @cte table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL,
	[RunTotal] [decimal](38, 2) NULL,
	[BatchGroup] [int] NULL)


AS
-- body of the function
BEGIN

declare @ctetemp table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL);
	
insert into @ctetemp
  SELECT [ServiceValue]
       , [Cost]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       --, [itemCode]
       , [itemCodeParsed]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]
       , ROW_NUMBER() 
            OVER (PARTITION BY countryCode, cityCode, storeType, storeCode ORDER BY CountryCode, CityCode, StoreCode, storeType, RC, gridCodeX) rnum
  FROM [T1].[dbo].[ServiceSchedule2]  s
  WHERE serviceday = DATENAME(WEEKDAY, @date)
    AND ( ServiceType <> 'IDR' 
        OR NOT EXISTS( select null from [T1].[dbo].[ServiceSchedule2] o
                   where o.UPCcode = s.UPCcode         
                     and o.ServiceDay = s.ServiceDay
                     AND o.ServiceType = 'SRR'
                     )
        )


INSERT INTO @cte
SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup
FROM @ctetemp a 
CROSS APPLY(
  SELECT SUM(serviceValueTotal) svSum
  FROM @ctetemp b
  WHERE b.rnum <= a.rnum
AND b.countryCode = a.countryCode AND  b.cityCode = a.cityCode AND b.storeType = a.storeType AND b.storeCode = a.storeCode
) rt 

RETURN
END

Open in new window

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

sorry, but a FUNCTION cannot store data into real tables.
it can only use @table variables to store intermediate results etc ...

you need to change the logic/flow to either use a stored procedure instead of a function .
Avatar of kenuk110
kenuk110

ASKER

Is it possible that this function could infact be a stored procedure, it's fine running outside of this function and only needs to be run once a day, I'm just not sure which part I could use, if any?
you can use a function in a stored procedure, but not in the other way, unfortunately.

does it have to be a function?
You cannot use insert or update or delete statements inside function you can use sub stored procedure

one thing you could do is insert the data in history table from function
means
Instead of passing date parameter in function check the date condition before.

if @date = getdate()
insert into History
select * from dbo.ServiceViewBatch()
else
select * from dbo.ServiceViewBatch()

No it doesn't have to be a function at all, I just thought as it was running is may as well do two jobs. It can by all means be a stored procedure but I'm still at a loss as to how to do it.
if changing your function to store procedure is ok then check the following script
create proc [dbo].[ServiceViewBatch] 
@date datetime
as
BEGIN
-- body of the proc
declare @cte table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL,
	[RunTotal] [decimal](38, 2) NULL,
	[BatchGroup] [int] NULL)

declare @ctetemp table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL);
	
insert into @ctetemp
  SELECT [ServiceValue]
       , [Cost]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       --, [itemCode]
       , [itemCodeParsed]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]
       , ROW_NUMBER() 
            OVER (PARTITION BY countryCode, cityCode, storeType, storeCode ORDER BY CountryCode, CityCode, StoreCode, storeType, RC, gridCodeX) rnum
  FROM [T1].[dbo].[ServiceSchedule2]  s
  WHERE serviceday = DATENAME(WEEKDAY, @date)
    AND ( ServiceType <> 'IDR' 
        OR NOT EXISTS( select null from [T1].[dbo].[ServiceSchedule2] o
                   where o.UPCcode = s.UPCcode         
                     and o.ServiceDay = s.ServiceDay
                     AND o.ServiceType = 'SRR'
                     )
        )


INSERT INTO @cte
SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup
FROM @ctetemp a 
CROSS APPLY(
  SELECT SUM(serviceValueTotal) svSum
  FROM @ctetemp b
  WHERE b.rnum <= a.rnum
AND b.countryCode = a.countryCode AND  b.cityCode = a.cityCode AND b.storeType = a.storeType AND b.storeCode = a.storeCode
) rt 

if @date = GETDATE()
insert into History
select * from @cte 
END

Open in new window

sorry

This could be done
declare @count int
select @count = COUNT(*) from History where DATE = @date 
if @count > 0
insert into History
select * from @cte 

Open in new window

or
each time the records form history will be deleted and reinserted
delete History where date = @date
insert into History
select * from @cte 

Open in new window

Hi,

Sorry, do I need to do all three suggestions or the first long one or the second short one or the third short one.

If it's either of the second two, are these done as a stored procedure?



use this
create proc [dbo].[ServiceViewBatch] 
@date datetime
as
BEGIN
-- body of the proc
declare @cte table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL,
	[RunTotal] [decimal](38, 2) NULL,
	[BatchGroup] [int] NULL)

declare @ctetemp table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL);
	
insert into @ctetemp
  SELECT [ServiceValue]
       , [Cost]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       --, [itemCode]
       , [itemCodeParsed]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]
       , ROW_NUMBER() 
            OVER (PARTITION BY countryCode, cityCode, storeType, storeCode ORDER BY CountryCode, CityCode, StoreCode, storeType, RC, gridCodeX) rnum
  FROM [T1].[dbo].[ServiceSchedule2]  s
  WHERE serviceday = DATENAME(WEEKDAY, @date)
    AND ( ServiceType <> 'IDR' 
        OR NOT EXISTS( select null from [T1].[dbo].[ServiceSchedule2] o
                   where o.UPCcode = s.UPCcode         
                     and o.ServiceDay = s.ServiceDay
                     AND o.ServiceType = 'SRR'
                     )
        )


INSERT INTO @cte
SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup
FROM @ctetemp a 
CROSS APPLY(
  SELECT SUM(serviceValueTotal) svSum
  FROM @ctetemp b
  WHERE b.rnum <= a.rnum
AND b.countryCode = a.countryCode AND  b.cityCode = a.cityCode AND b.storeType = a.storeType AND b.storeCode = a.storeCode
) rt 

declare @count int
select @count = COUNT(*) from History where DATE = @date 
if @count > 0
insert into History
select * from @cte 

END

Open in new window

Hi,

I copied this in to a stored procedure, I also created the HISTORY table, the procedure runs and the following message appreas:


Procedure executed successfully
Query time: 30.156ms
Affected rows: 9874

Affected rows: 9874

when I look in the HISTORY table though I don't see anything. Also, I'm not sure how to get the date in there?
alter the storeprocedure condition and change the 2 lines
form
select @count = COUNT(*) from History where DATE = @date 
if @count > 0

Open in new window


to
select @count = COUNT(*) from History where convert(date,DATE) = convert(date,@date)
if @count = 0

Open in new window

HISTORY table should contain the column name date which will be checked in the query each time
Okay, I have changed that and when I try to save the procedure I get

[SQL Server]Column name or number of supplied values does not match table definition

I created a field in HISTORY called Date with type of date

Do I need to create this somewhere else too?
hi

Thought i would show you how I would do it see attached

the sp below creates table if it doesnt exist plus it enters the date into the history file hope that helps
(have added sachinpatil10d count at bottom - thanks)

hope this helps

USE 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[ServiceViewBatch]  

@date datetime

AS

SET NOCOUNT ON


	if  not exists (select * from db.dbo.sysobjects
where id = object_id(N'[db].[dbo].[history]'))
    CREATE TABLE [dbo].[history] (
	(
    -- columns returned by the function
	[date] datetime,
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL,
	[RunTotal] [decimal](38, 2) NULL,
	[BatchGroup] [int] NULL
	)

-- set date to todays date
set @date = getDate()

-- create temp tables
if object_id('tempdb..#cte','U') is not null drop table #cte
if object_id('tempdb..#ctetemp','U') is not null drop table #ctetemp


  SELECT [ServiceValue]
       , [Cost]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       --, [itemCode]
       , [itemCodeParsed]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]
       , ROW_NUMBER() 
            OVER (PARTITION BY countryCode, cityCode, storeType, storeCode ORDER BY CountryCode, CityCode, StoreCode, storeType, RC, gridCodeX) rnum
  into #ctetemp 
	FROM [T1].[dbo].[ServiceSchedule2]  s
  WHERE serviceday = DATENAME(WEEKDAY, @date)
    AND ( ServiceType <> 'IDR' 
        OR NOT EXISTS( select null from [T1].[dbo].[ServiceSchedule2] o
                   where o.UPCcode = s.UPCcode         
                     and o.ServiceDay = s.ServiceDay
                     AND o.ServiceType = 'SRR'
                     )
        )


SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup

INSERT INTO #cte

FROM #ctetemp a 
CROSS APPLY(
  SELECT SUM(serviceValueTotal) svSum
  FROM #ctetemp b
  WHERE b.rnum <= a.rnum
AND b.countryCode = a.countryCode AND  b.cityCode = a.cityCode AND b.storeType = a.storeType AND b.storeCode = a.storeCode
) rt 

declare @count int
select @count = COUNT(*) from History 
where date = @date 
if @count > 0
insert into History
select date, * from #cte 

END

SET NOCOUNT Off

Open in new window

apologies take the insert out of line 96

so shoudl read into #cte
Hi,

Sorry for the delay, I had to go home!

I ran what you put, I put it in a query and when it runs I get this message:


[Err] 42000 - [SQL Server]Incorrect syntax near '('.
42000 - [SQL Server]Incorrect syntax near the keyword 'SET'.

Any ideas? And also is it because I am running it in a query?
ASKER CERTIFIED SOLUTION
Avatar of sachinpatil10d
sachinpatil10d
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

I run this as a stored oricedure and I get this error, I'm assuming it's due to the DATE field I have added to the History table.
--------------------------
Procedure execution failed
21S01 - [SQL Server]Column name or number of supplied values does not match table definition.

Query time: 31.732ms
Affected rows: 10154
------------------------------

I then ran the EXECUTE part thinking that would do something but I got this back:
---------------------------------------------
[SQL] declare @date datetime
set @date = getdate()
exec [ServiceViewBatchHistory] @date
select * from History

[Err] 21S01 - [SQL Server]Column name or number of supplied values does not match table definition.


Affected rows: 9874
--------------------------

Any ideas?
this error means that for at least 1 of the INSERT ... SELECT, the number of columns in the SELECT does not match the number of column in the table you are inserting to.
so, please double-check the tables and columns, especially the History table ...

Hi,

I have checked the History table and also copied the code to create it again in this messge. I 'think' it's copying everything from the @cte1 temp table so I checked the entries against that one. I can't see anything wrong though.

CREATE TABLE [dbo].[History] (
[ServiceValue] decimal(18) NULL ,
[Cost] decimal(18) NULL ,
[ItemName] nvarchar(255) COLLATE Latin1_General_CI_AS NULL ,
[countryCode] nvarchar(3) COLLATE Latin1_General_CI_AS NOT NULL ,
[storeCode] nvarchar(3) COLLATE Latin1_General_CI_AS NOT NULL ,
[cityCode] nvarchar(2) COLLATE Latin1_General_CI_AS NOT NULL ,
[itemCodeParsed] nvarchar(255) COLLATE Latin1_General_CI_AS NULL ,
[storeType] nvarchar(1) COLLATE Latin1_General_CI_AS NOT NULL ,
[vendorCode] nvarchar(3) COLLATE Latin1_General_CI_AS NOT NULL ,
[upcCode] nvarchar(30) COLLATE Latin1_General_CI_AS NOT NULL ,
[rc] nvarchar(3) COLLATE Latin1_General_CI_AS NULL ,
[gridCodeX] nvarchar(4) COLLATE Latin1_General_CI_AS NOT NULL ,
[gridCodeY] nvarchar(4) COLLATE Latin1_General_CI_AS NOT NULL ,
[facings] nvarchar(3) COLLATE Latin1_General_CI_AS NULL ,
[brand] nvarchar(255) COLLATE Latin1_General_CI_AS NULL ,
[serviceDay] nvarchar(9) COLLATE Latin1_General_CI_AS NULL ,
[serviceType] nvarchar(5) COLLATE Latin1_General_CI_AS NULL ,
[ARABIC] nvarchar(255) COLLATE Latin1_General_CI_AS NULL ,
[TREF] nvarchar(255) COLLATE Latin1_General_CI_AS NULL ,
[serviceValueTotal] decimal(29,2) NULL ,
[rnum] bigint NULL ,
[RunTotal] decimal(38,2) NULL ,
[BatchGroup] int NULL ,
[date] date NULL 
)
ON [PRIMARY]
GO

Open in new window

I'm not sure if this helps but I don't have a DATE field in the table that copies INTO History, I simply added the date field to History. Could this be the problem?
history table has 1 column more than @cte:
[date] date NULL




declare @count int
select @count = COUNT(*) from History where convert(date,DATE) = convert(date,@date)
if @count = 0
insert into History
select c.* , @date 
 from @cte c

Open in new window

Hi,

I take it I amend the last part of this script with the script you sent? I did that anyway and ran it and now I get"

Must declare the table variable "@cte"

Sorry to be stupid, I'm really not a DBA but trying to learn!

Sorry, I called it @cte1

Just running it now....
It works great when I run the procedure on it's own but how do I pass the date variable from outside it?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi all,

Thank you so much for your help in this, I really appreciate it.