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
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
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?
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()
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()
ASKER
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
sorry
This could be done
each time the records form history will be deleted and reinserted
This could be done
declare @count int
select @count = COUNT(*) from History where DATE = @date
if @count > 0
insert into History
select * from @cte
or each time the records form history will be deleted and reinserted
delete History where date = @date
insert into History
select * from @cte
ASKER
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?
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
ASKER
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?
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
to
form
select @count = COUNT(*) from History where DATE = @date
if @count > 0
to
select @count = COUNT(*) from History where convert(date,DATE) = convert(date,@date)
if @count = 0
HISTORY table should contain the column name date which will be checked in the query each time
ASKER
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?
[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
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
apologies take the insert out of line 96
so shoudl read into #cte
so shoudl read into #cte
ASKER
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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 ...
so, please double-check the tables and columns, especially the History table ...
ASKER
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.
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
ASKER
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
[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
ASKER
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!
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!
ASKER
Sorry, I called it @cte1
Just running it now....
Just running it now....
ASKER
It works great when I run the procedure on it's own but how do I pass the date variable from outside it?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi all,
Thank you so much for your help in this, I really appreciate it.
Thank you so much for your help in this, I really appreciate it.
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 .