using tables with identity columns and trying to create a stored procedure to load records into 2 tables.
here is what i am trying to do. but when i try to save it i get
"Error 8101: An explicit value for the identity column in table 'XAPCHKDETAIL' can only be specified when a column list is used and IDENTITY_INSERT is ON"
Do i have to break this down into 2 SP's or can i do it some how with this one.
Also, how can i get the XAPCHECKS table to only load the DISTINCT values from the APPHISTF. the APPHISTF will possible have multiple check numbers that are the same but i only need that row in the XAPCHECKS table once.
CREATE PROCEDURE isp_ap_load_checks
@.p_comp char(2),
@.p_vend char(6),
@.p_user char(12),
@.p_date1 char(8),
@.p_date2 char(8)
as
if (@.p_user <= '')
begin
set @.p_user = 'system'
end
if (@.p_date1 <= '00000000')
begin
set @.p_date1 = '00000000'
end
if (@.p_date2 <= @.p_date1)
begin
set @.p_date2 = '99999999'
end
delete from XAPCHECKS
where xapck_comp = @.p_comp and xapck_vend = @.p_vend and xapck_user = @.p_user
delete from XAPCHKDETAIL
where xapcd_comp = @.p_comp and xapcd_vend = @.p_vend and xapcd_user = @.p_user
insert into XAPCHECKS
select apph_comp, apph_vend, @.p_user,
max(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0')), apph_payck, chm_type, chm_stat, apt_bank,
apph_paymnts, apph_stat
from APPHISTF LEFT JOIN APTRANF on apt_comp = apph_comp and apt_vend = apph_vend and
apt_type = apph_type and apt_id = apph_id
LEFT JOIN APBANKF ON apb_code = apt_bank
left join CHMASTF on chm_comp = apb_comp and chm_acct = apb_cash and chm_no = apph_payck
where (apph_comp = @.p_comp) and (apph_vend = @.p_vend) and
(apph_payck > 0 and
(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') > '00000000'))
group by apph_comp, apph_vend, apph_payck, chm_type, chm_stat, apph_paymnts, apph_stat, apph_type, apt_bank, apph_id
insert into XAPCHKDETAIL
select apph_comp, apph_vend, @.p_user, apph_payck, chm_type, chm_stat, apt_bank, apph_type, apph_id
from APPHISTF LEFT JOIN APTRANF on apt_comp = apph_comp and apt_vend = apph_vend and
apt_type = apph_type and apt_id = apph_id
LEFT JOIN APBANKF ON apb_code = apt_bank
left join CHMASTF on chm_comp = apb_comp and chm_acct = apb_cash and chm_no = apph_payck
where (apph_comp = @.p_comp) and (apph_vend = @.p_vend) and
(apph_payck > 0 and
(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') > '00000000'))
group by apph_comp, apph_vend, apph_payck, chm_type, chm_stat, apph_paymnts, apph_stat, apph_type, apt_bank, apph_id
GOPost the DDL for XAPCHKDETAIL.|||here is the sql script for the table...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XAPCHKDETAIL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[XAPCHKDETAIL]
GO
CREATE TABLE [dbo].[XAPCHKDETAIL] (
[xapcd_comp] [char] (2) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_vend] [char] (6) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_user] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_check] [int] NOT NULL ,
[xapcd_chk_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapck_check_status] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_bank] [char] (5) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[xapcd_id] [char] (18) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[A4GLIdentity] [numeric](9, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [IXAPCHKDETAIL0] ON [dbo].[XAPCHKDETAIL]([xapcd_comp], [xapcd_vend], [xapcd_user], [xapcd_check], [xapcd_chk_type], [xapck_check_status], [xapcd_bank]) ON [PRIMARY]
GO|||Visit http://support.microsoft.com/kb/878501 , an article from Microsoft to resolve the problem.
Cheers,
--Riaz
using tables with identity columns and trying to create a stored procedure to load records into 2 tables.
here is what i am trying to do. but when i try to save it i get
"Error 8101: An explicit value for the identity column in table 'XAPCHKDETAIL' can only be specified when a column list is used and IDENTITY_INSERT is ON"
Do i have to break this down into 2 SP's or can i do it some how with this one.
Also, how can i get the XAPCHECKS table to only load the DISTINCT values from the APPHISTF. the APPHISTF will possible have multiple check numbers that are the same but i only need that row in the XAPCHECKS table once.|||Change this:
insert into XAPCHKDETAIL
select apph_comp, apph_vend, @.p_user, apph_payck, chm_type, chm_stat, apt_bank, apph_type, apph_id
...
...to this:
insert into XAPCHKDETAIL
([xapcd_comp],
[xapcd_vend],
[xapcd_user],
[xapcd_check],
[xapcd_chk_type],
[xapck_check_status],
[xapcd_bank],
[xapcd_type],
[xapcd_id])
select apph_comp, apph_vend, @.p_user, apph_payck, chm_type, chm_stat, apt_bank, apph_type, apph_id
...
ALWAYS enumerate your column names.|||Is there a way that you know of to only load up a distinct record into the XAPCHECKS table. The APPHISTF can have many ID's associated with a single check.
apphistf:
ID 1 check 1
ID 2 check 1
ID 3 check 1
ID 4 check 2
ID 5 check 3
ID 6 check 3
...
what i am trying to accomplish is to get a recap for a vend by check date, check number that doesnt have to tie back to the ID, that is where the XAPCHKDETAIL comes in.|||Either SELECT DISTINCT, or use an aggregate query with MAX or MIN to select one of the IDs to insert.
