February 8, 2011 at 2:03 pm
Ok, the gist of it what I'm talking about is just to get the data over into a temp table and then do whatever else you need to with it.
ALTER PROCEDURE [dbo].[pGetReportData]
(@ReportNo varchar(10),
@monthid varchar(20),
@payorkey varchar(10)
) AS
declare @reportqry varchar(2000)
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'AposGrpwise') AND type = (N'U'))
DROP TABLE AposGrpwise
select @monthid = convert(varchar(4),datepart(year,dateadd(month,-1,getdate())))+
isnull(replicate('0',(2-datalength(ltrim(convert(varchar(4),datepart(month,dateadd(month,-1,getdate()))))))),'') +
ltrim(convert(varchar(4),datepart(month,dateadd(month,-1,getdate()))))
-- Create a temp table that'll hold all the fields you're pulling over from oracle.
CREATE TABLE #Table(
Client_id int,
Client_Code varchar(50)
....
)
-- Get rid of the @ReportNo here, you'll add it in down below.
set @reportqry = 'select * into AposGrpwise from openquery(EOM,''select ''''' +
null client_id,
r.payor_key as CLIENT_CODE,
null CLIENT_NAME,
r.payor_key as legacy_client_code,
null major_client_name,
null major_client_code,
null end_user_id,
null user_name,
null employer_group_id,
replace(r.group_ref_key, ''''/'''', '''' '''') as employer_group_code,
translate(substr(group_name,
instr(group_name, ''''-'''') + 1,
length(group_name)),
''''/\'''',
'''' '''') as employer_group_name,
null branch_group_id,
null branch_group_code,
null branch_group_name,
''''{R08_SUMMARY.PAYOR_KEY} = '''' || ''''"'''' || r.payor_key || ''''"'''' ||
'''' and {R08_SUMMARY.GROUP_NAME} = "'''' || r.group_name || ''''"'''' as selform,
null path,
null email,
''''A'''' status,
''''EMPGRPWISE CLIENT BO'''' burst_type,
''''PDF'''' burst_file_type,
''''-1'''' id,
null bouser,
''''G'''' std_rpt_burst
from r08_summary1 r
where r.month_id = '''''+ @monthid + '''''
and r.payor_key = '''''+ @payorkey +'''''
and r.print_flag = ''''Y'''''')'
INSERT INTO #Table(Fields)
exec(@reportqry)
SELECT @ReportNo ReportNo, *
FROM #Table
-- Join here to any sql tables you'd like
February 8, 2011 at 7:32 pm
Hi seth,
Thanks for the reply. I am able to gain some knowledge i guess. I created the temp table and it compiled without errors and executed for the first time. when i executed it second time it throwed an error.
Error:There is already an object named aposgrpwisetest in the database.
Any ideas?
February 8, 2011 at 8:04 pm
Sorry my bad i was still referencing the old table. I got rid of the error.
Now i have added an update statement after the exec statement and the process doesnt like it at all. It throws an error
update AposGrpwisetest
set client_id =
(select distinct client_id from client where legacy_client_code = AposGrpwisetest
.client_code),
major_client_code = (select distinct legacy_major_code from majorclient where majorclient_id = (select majorclient_id from client where legacy_client_code = AposGrpwisetest
.client_code))
Error:
string or binary data would be truncated
The statement has been terminated
February 9, 2011 at 6:18 am
That error means that the length of the character string you're trying to update to is bigger than the field can hold. Check your field lengths.
February 9, 2011 at 7:50 am
Awesome u da man. I changed the field length and it worked. The only final work remaining will be. Can i select the process data into a table or something like that. Because i need the data with the reportnumbers to be inserted into a production table. How to acheive that. I tried inserting another temp table in the process as
insert into table
select @reportno reportno,* from aposgrpwise test
It throws error.
February 9, 2011 at 8:18 am
February 9, 2011 at 9:36 am
oops forgot to mention the error
Error:
Msg 208, Level 16, State 1, Procedure pGetReportData
Invalid object name 'AposGrpwisetemp'.
February 9, 2011 at 11:02 am
That table doesn't exist at the point that you're trying to select out of it. Check the following.
Is it actually a "temp" table, (Preceded with #),
Is the spelling correct
Are you dropping the table before you get to that point
Also note that the error you posted doesn't match the code that you posted.
February 9, 2011 at 11:19 am
Hi Seth,
Yes it is a Temp Table preceded with #.
Please see the code below. Am selecting data from the AposGrpwisetest and dumping it into AposGrpwisetemp Table.
Not quite sure why its throwing error
ALTER PROCEDURE [dbo].[pGetReportData]
(@ReportNo varchar(10),
@monthid varchar(20),
@payorkey varchar(10)
) AS
declare @reportqry varchar(2000)
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'AposGrpwisetest') AND type = (N'U'))
DROP TABLE AposGrpwisetest
-- Create a Temp Table
Create Table AposGrpwisetest(
[CLIENT_ID] [int],
[CLIENT_CODE] [varchar](20),
[CLIENT_NAME] [varchar](50),
[LEGACY_CLIENT_CODE] [varchar](60),
[MAJOR_CLIENT_NAME] [varchar](60),
[MAJOR_CLIENT_CODE] [varchar](60),
[END_USER_ID] [int],
[USER_NAME] [varchar](60),
[EMPLOYER_GROUP_ID] [int],
[EMPLOYER_GROUP_CODE] [varchar](25),
[EMPLOYER_GROUP_NAME] [varchar](70),
[BRANCH_GROUP_ID] [int],
[BRANCH_GROUP_CODE] [varchar](20),
[BRANCH_GROUP_NAME] [varchar](60),
[SELFORM] [varchar](255),
[PATH] [varchar](255),
[varchar](2),
[STATUS] [char](1),
[BURST_TYPE] [char](20),
[BURST_FILE_TYPE] [char](30),
[ID] [int],
[BOUSER] [varchar](255),
[STD_RPT_BURST] [varchar](1))
set @reportqry = 'select * from openquery(EOM,''select
null client_id,
r.payor_key as CLIENT_CODE,
null CLIENT_NAME,
r.payor_key as legacy_client_code,
null major_client_name,
null major_client_code,
null end_user_id,
null user_name,
null employer_group_id,
replace(r.group_ref_key, ''''/'''', '''' '''') as employer_group_code,
translate(substr(group_name,
instr(group_name, ''''-'''') + 1,
length(group_name)),
''''/\'''',
'''' '''') as employer_group_name,
null branch_group_id,
null branch_group_code,
null branch_group_name,
''''{R08_SUMMARY.PAYOR_KEY} = '''' || ''''"'''' || r.payor_key || ''''"'''' ||
'''' and {R08_SUMMARY.GROUP_NAME} = "'''' || r.group_name || ''''"'''' as selform,
null path,
null email,
''''A'''' status,
''''EMPGRPWISE CLIENT BO'''' burst_type,
''''PDF'''' burst_file_type,
''''-1'''' id,
null bouser,
''''G'''' std_rpt_burst
from r08_summary1 r
where r.month_id = '''''+ @monthid + '''''
and r.payor_key = '''''+ @payorkey +'''''
and r.print_flag = ''''Y'''''')'
-- Insert into the Table
insert into AposGrpwisetest(client_id,
client_code,
client_name,
legacy_client_code,
major_client_name,
major_client_code,
end_user_id,
user_name,
EMPLOYER_GROUP_ID,
EMPLOYER_GROUP_CODE,
EMPLOYER_GROUP_NAME,
BRANCH_GROUP_ID,
BRANCH_GROUP_CODE,
BRANCH_GROUP_NAME,
SELFORM,
PATH,
email,
status,
burst_type,
burst_file_type,
id,
bouser,
std_rpt_burst)
exec(@reportqry)
-- Updating Client_id and Major_Client_Code
update AposGrpwisetest
set client_id =
(select distinct client_id from client where legacy_client_code = AposGrpwisetest
.client_code),
major_client_code = (select distinct legacy_major_code from majorclient where majorclient_id = (select majorclient_id from client where legacy_client_code = AposGrpwisetest
.client_code))
-- Updating the Path
update AposGrpwisetest
set path = (select distinct bo_client_path from bo_client_path where client_id = AposGrpwisetest
.client_id)
+'/'+ AposGrpwisetest
.client_code
-- Updating the Client_Name
update AposGrpwisetest
set client_name = (select substring(bo_client_path,
1,
charindex('/', bo_client_path)-1) from bo_client_path where client_id = AposGrpwisetest
.client_id)
insert into #AposGrpwisetemp
Select @ReportNo ReportNo,* from AposGrpwisetest
February 9, 2011 at 11:30 am
This syntax won't work for an object that doesn't already exist:
insert into #AposGrpwisetemp
Select @ReportNo ReportNo,* from AposGrpwisetest
You'd instead need to do that like this:
Select @ReportNo ReportNo,*
into #AposGrpwisetemp
from AposGrpwisetest
That syntax creates the temp table from your selection rather than expecting it to be there already.
Now, that said, why do you need it in another temp table again? If you just need to insert the data from there into production, you could always just do this:
INSERT INTO YourProdTable(Fields)
SELECT @ReportNo, * -- Or more likely specific fields rather than * here
FROM AposGrpwiseTest
February 9, 2011 at 2:18 pm
Simply awesome that worked like a magic. Final question is that if i execute the procedure with the same payor. It is appending the data again. How would i insert the record only when the data changes?
February 9, 2011 at 2:58 pm
The easy way: Delete any rows that match the criteria of the one you're about to insert before you insert it.
The better way: Check to see if the row is already there and the same, and if it is, don't insert it.
February 9, 2011 at 4:10 pm
Hi Seth,
I have included the code saying
If not Exists(select * from EmpGrpwiseData join AposGrpwisetest on EmpGrpwiseData.client_code = AposGrpwisetest.client_code). Looks like its not inserting when there is a match. Next month if i execute the code with the same payor paramater am sure that it will not insert. Because that payor is already in the data. What If the data changes for the payor??? How shall i write the logic. Do i need to delete the table everytime i do an insert?
February 10, 2011 at 2:15 pm
You need to decide which fields you care about when they change. Instead of just joining based on client_code, maybe you want to join on client_code and Month, or possibly those 2 plus report, etc.
Viewing 14 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply