May 26, 2015 at 10:27 am
Hi All,
I have to modify a stored procedure that is written by someone else.Basically the stored prcoedure uses a cursor to fetch the data from the table and then insert that data in another table. While fetching the code form another table, it also gets some distinct columns from another table Below is my code:
Declare data_cursor cursor for
Select emp_no, emp_name, event_date, Test_no, Code, Test_result
From test_table1
order by emp_no
declare
@empNo varchar(100),
@emp_name varchar(2000),
@eventDate varchar(20),
@TestNo varchar(100),
@Code varchar(100),
@TestReuslt varchar(100),
@ProcessName varchar(100),
@FileProcess varchar(200),
@TestProcess varchar(100),
@countA int,
@error_count int
SELECT @ProcessName = (select distinct userID from test_table1)
SELECT @FileProcess = 'EW' + @ProcessName
Select @TestProcess = (Select distinct userID from testTable1) + 'TXT'
select @countA = 0
begin tran
Open data_cursor
fetch data_cursor into
@empNo ,
@emp_name ,
@eventDate ,
@TestNo ,
@Code ,
@TestReuslt
while (@@FETCH_STATUS=0)
begin
insert into TESTTable2
(
empNum, empName, eventDate,TestNum, Code, TestResult, Testprocess, FileProcess, ProcessName)
values (@empNo, @emp_name, @eventDate , @TestNo , @Code, @TestReuslt, @FileProcess, @ProcessName, @TestProcess)
if @@error > 0
begin
select @error_count = @error_count + 1
end
else
set @record_id = @@Identity
if @code like 'D%'
Insert into TESTTable3
(testProcess, FileProcess, empNum)
values (@TestProcess, @FileProcess, @empNo )
if @@error > 0
begin
select @error_count = @error_count + 1
end
set @countA = @countA + 1
fetch data_cursor into
fetch data_cursor into
@empNo ,
@emp_name ,
@eventDate ,
@TestNo ,
@Code ,
@TestReuslt
if @@error > 0
begin
select @error_count = @error_count + 1
end
end
if @error_count > 0
begin
rollback tran
end
else
begin /* @@error = 0 */
commit tran
close data_cursor
deallocate data_cursor
Insert into LOG_File
(Name, Count, Processname)
values ('Test1', @CountA,@ProcessName)
Select 'TotalCount' = @CountA
The reason, I have to modify the above stored proc because now because of application changes, I am getting around 50 distinct userID from test_table1 so the above subquery(SELECT @ProcessName = (select distinct userID from test_table1) won't work. How can I loop through the above stored proc so that each @ProcessName can get inserted in table TESTTable2 so in other words
I want to pass each userId one at a time and insert it in table test_table1 and other subsequent tables. I can declare another cursor to accomplish this, but I was wondering if there is any better way to rewrite this stored proc and not use the cursor at all.
because of my application changes all these three statements above are throwing the error:
SELECT @ProcessName = (select distinct userID from test_table1)
SELECT @FileProcess = 'EW' + @ProcessName
Select @TestProcess = (Select distinct userID from testTable1) + 'TXT'
any help will be greatly appreciated.
May 26, 2015 at 10:48 am
anjaliagarwal5 (5/26/2015)
Hi All,I have to modify a stored procedure that is written by someone else.Basically the stored prcoedure uses a cursor to fetch the data from the table and then insert that data in another table. While fetching the code form another table, it also gets some distinct columns from another table Below is my code:
Declare data_cursor cursor for
Select emp_no, emp_name, event_date, Test_no, Code, Test_result
From test_table1
order by emp_no
declare
@empNo varchar(100),
@emp_name varchar(2000),
@eventDate varchar(20),
@TestNo varchar(100),
@Code varchar(100),
@TestReuslt varchar(100),
@ProcessName varchar(100),
@FileProcess varchar(200),
@TestProcess varchar(100),
@countA int,
@error_count int
SELECT @ProcessName = (select distinct userID from test_table1)
SELECT @FileProcess = 'EW' + @ProcessName
Select @TestProcess = (Select distinct userID from testTable1) + 'TXT'
select @countA = 0
begin tran
Open data_cursor
fetch data_cursor into
@empNo ,
@emp_name ,
@eventDate ,
@TestNo ,
@Code ,
@TestReuslt
while (@@FETCH_STATUS=0)
begin
insert into TESTTable2
(
empNum, empName, eventDate,TestNum, Code, TestResult, Testprocess, FileProcess, ProcessName)
values (@empNo, @emp_name, @eventDate , @TestNo , @Code, @TestReuslt, @FileProcess, @ProcessName, @TestProcess)
if @@error > 0
begin
select @error_count = @error_count + 1
end
else
set @record_id = @@Identity
if @code like 'D%'
Insert into TESTTable3
(testProcess, FileProcess, empNum)
values (@TestProcess, @FileProcess, @empNo )
if @@error > 0
begin
select @error_count = @error_count + 1
end
set @countA = @countA + 1
fetch data_cursor into
fetch data_cursor into
@empNo ,
@emp_name ,
@eventDate ,
@TestNo ,
@Code ,
@TestReuslt
if @@error > 0
begin
select @error_count = @error_count + 1
end
end
if @error_count > 0
begin
rollback tran
end
else
begin /* @@error = 0 */
commit tran
close data_cursor
deallocate data_cursor
Insert into LOG_File
(Name, Count, Processname)
values ('Test1', @CountA,@ProcessName)
Select 'TotalCount' = @CountA
The reason, I have to modify the above stored proc because now because of application changes, I am getting around 50 distinct userID from test_table1 so the above subquery(SELECT @ProcessName = (select distinct userID from test_table1) won't work. How can I loop through the above stored proc so that each @ProcessName can get inserted in table TESTTable2 so in other words
I want to pass each userId one at a time and insert it in table test_table1 and other subsequent tables. I can declare another cursor to accomplish this, but I was wondering if there is any better way to rewrite this stored proc and not use the cursor at all.
because of my application changes all these three statements above are throwing the error:
SELECT @ProcessName = (select distinct userID from test_table1)
SELECT @FileProcess = 'EW' + @ProcessName
Select @TestProcess = (Select distinct userID from testTable1) + 'TXT'
any help will be greatly appreciated.
Are "test_table1" and "testTable1" typos for the same table?
If so, then this should work, assuming I understood your problem.
INSERT INTO TESTTable2 (
empNum,
empName,
eventDate,
TestNum,
Code,
TestResult,
Testprocess,
FileProcess,
ProcessName
)
SELECT emp_no,
emp_name,
event_date,
Test_no,
Code,
Test_result,
'EW' + userID,
userID,
userID + 'TXT'
FROM test_table1
INSERT INTO TESTTable3 (
testProcess,
FileProcess,
empNum
)
SELECT
'EW' + userID,
userID,
empNum
FROM test_table1
WHERE code LIKE 'D%'
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 26, 2015 at 11:05 am
Sorry, they are typos.
May 26, 2015 at 11:18 am
This might work for you. I'm not sure if we're missing something you left out, so you might need to tweak some things.
BEGIN TRY
BEGIN TRAN
CREATE TABLE #Output(
testProcess varchar(10),
FileProcess varchar(10),
empNum varchar(10),
code varchar(10),
ProcessName varchar(10)
)
INSERT INTO TESTTable2
(empNum, empName, eventDate,TestNum, Code, TestResult, Testprocess, FileProcess, ProcessName)
OUTPUT inserted.testProcess, inserted.FileProcess, inserted.empNum, inserted.Code, inserted.ProcessName INTO #Output
Select emp_no, emp_name, event_date, Test_no, Code, Test_result, userID, 'EW' + userID, userID
From test_table1
INSERT INTO TESTTable3
(testProcess, FileProcess, empNum)
SELECT testProcess, FileProcess, empNum
FROM #Output
WHERE code like 'D%'
INSERT INTO LOG_File (Name, Count, Processname)
SELECT 'Test1', COUNT(*), ProcessName
FROM #Output
GROUP BY ProcessName
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
Select 'TotalCount' = COUNT(*)
FROM #Output
DROP TABLE #Output
May 26, 2015 at 11:52 am
Thank you.
In the above code, after creating the table, should I insert the data into the #OUTPUT table right after creating it so
BEGIN TRY
BEGIN TRAN
CREATE TABLE #Output(
testProcess varchar(10),
FileProcess varchar(10),
empNum varchar(10),
code varchar(10),
ProcessName varchar(10)
)
INSERT INTO #Output(testProcess, FileProcess, empNum, Code, processname)
values ( select distinct userID from test_table1, ...
something like this. Should I do that before inserting in the other tables. Please let me know if I need to insert those values in the OUTPUT table. How should I proceed?
May 26, 2015 at 12:10 pm
The #Output table is populated using the OUTPUT clause in the insert. I included it because is a good option if you're using identity columns from which you might not know the inserted value.
You could try to search for OUTPUT Clause to get detailed information on how and when you could use it. Don't forget to read the documentation in Books OnLine (BOL) https://msdn.microsoft.com/en-us/library/ms177564.aspx
May 26, 2015 at 1:30 pm
Thanks for all your help. Works great!!
Learned something new!!
May 26, 2015 at 2:20 pm
Luis Cazares (5/26/2015)
This might work for you. I'm not sure if we're missing something you left out, so you might need to tweak some things.
BEGIN TRY
BEGIN TRAN
CREATE TABLE #Output(
testProcess varchar(10),
FileProcess varchar(10),
empNum varchar(10),
code varchar(10),
ProcessName varchar(10)
)
INSERT INTO TESTTable2
(empNum, empName, eventDate,TestNum, Code, TestResult, Testprocess, FileProcess, ProcessName)
OUTPUT inserted.testProcess, inserted.FileProcess, inserted.empNum, inserted.Code, inserted.ProcessName INTO #Output
Select emp_no, emp_name, event_date, Test_no, Code, Test_result, userID, 'EW' + userID, userID
From test_table1
INSERT INTO TESTTable3
(testProcess, FileProcess, empNum)
SELECT testProcess, FileProcess, empNum
FROM #Output
WHERE code like 'D%'
INSERT INTO LOG_File (Name, Count, Processname)
SELECT 'Test1', COUNT(*), ProcessName
FROM #Output
GROUP BY ProcessName
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
Select 'TotalCount' = COUNT(*)
FROM #Output
DROP TABLE #Output
Hi,
In the above code, Looks like this OUTPUT table that has five columns in it should also be present in the Testtable2 so
testProcess ,
FileProcess ,
empNum ,
code ,
ProcessName
that are declared in #OUTPUT table should be present in TestTable2. I have some more columns in test_table1 that are not present in TestTable2, but are present in Testtable3 so there so there is one column called convertedProcess. This column is not present in TestTable2, but is present in TestTable3 and test_table1.
Is there any way, I can modify the above code to accommodate the above requirements.
Thanks again for your help.
I really need help with this. Any help will be greatly appreciated.
May 26, 2015 at 3:08 pm
Michael L John (5/26/2015)
anjaliagarwal5 (5/26/2015)
Hi All,I have to modify a stored procedure that is written by someone else.Basically the stored prcoedure uses a cursor to fetch the data from the table and then insert that data in another table. While fetching the code form another table, it also gets some distinct columns from another table Below is my code:
Declare data_cursor cursor for
Select emp_no, emp_name, event_date, Test_no, Code, Test_result
From test_table1
order by emp_no
declare
@empNo varchar(100),
@emp_name varchar(2000),
@eventDate varchar(20),
@TestNo varchar(100),
@Code varchar(100),
@TestReuslt varchar(100),
@ProcessName varchar(100),
@FileProcess varchar(200),
@TestProcess varchar(100),
@countA int,
@error_count int
SELECT @ProcessName = (select distinct userID from test_table1)
SELECT @FileProcess = 'EW' + @ProcessName
Select @TestProcess = (Select distinct userID from testTable1) + 'TXT'
select @countA = 0
begin tran
Open data_cursor
fetch data_cursor into
@empNo ,
@emp_name ,
@eventDate ,
@TestNo ,
@Code ,
@TestReuslt
while (@@FETCH_STATUS=0)
begin
insert into TESTTable2
(
empNum, empName, eventDate,TestNum, Code, TestResult, Testprocess, FileProcess, ProcessName)
values (@empNo, @emp_name, @eventDate , @TestNo , @Code, @TestReuslt, @FileProcess, @ProcessName, @TestProcess)
if @@error > 0
begin
select @error_count = @error_count + 1
end
else
set @record_id = @@Identity
if @code like 'D%'
Insert into TESTTable3
(testProcess, FileProcess, empNum)
values (@TestProcess, @FileProcess, @empNo )
if @@error > 0
begin
select @error_count = @error_count + 1
end
set @countA = @countA + 1
fetch data_cursor into
fetch data_cursor into
@empNo ,
@emp_name ,
@eventDate ,
@TestNo ,
@Code ,
@TestReuslt
if @@error > 0
begin
select @error_count = @error_count + 1
end
end
if @error_count > 0
begin
rollback tran
end
else
begin /* @@error = 0 */
commit tran
close data_cursor
deallocate data_cursor
Insert into LOG_File
(Name, Count, Processname)
values ('Test1', @CountA,@ProcessName)
Select 'TotalCount' = @CountA
The reason, I have to modify the above stored proc because now because of application changes, I am getting around 50 distinct userID from test_table1 so the above subquery(SELECT @ProcessName = (select distinct userID from test_table1) won't work. How can I loop through the above stored proc so that each @ProcessName can get inserted in table TESTTable2 so in other words
I want to pass each userId one at a time and insert it in table test_table1 and other subsequent tables. I can declare another cursor to accomplish this, but I was wondering if there is any better way to rewrite this stored proc and not use the cursor at all.
because of my application changes all these three statements above are throwing the error:
SELECT @ProcessName = (select distinct userID from test_table1)
SELECT @FileProcess = 'EW' + @ProcessName
Select @TestProcess = (Select distinct userID from testTable1) + 'TXT'
any help will be greatly appreciated.
Are "test_table1" and "testTable1" typos for the same table?
If so, then this should work, assuming I understood your problem.
INSERT INTO TESTTable2 (
empNum,
empName,
eventDate,
TestNum,
Code,
TestResult,
Testprocess,
FileProcess,
ProcessName
)
SELECT emp_no,
emp_name,
event_date,
Test_no,
Code,
Test_result,
'EW' + userID,
userID,
userID + 'TXT'
FROM test_table1
INSERT INTO TESTTable3 (
testProcess,
FileProcess,
empNum
)
SELECT
'EW' + userID,
userID,
empNum
FROM test_table1
WHERE code LIKE 'D%'
Hi,
How will I get the record count with your query which is declared as CountA in my stored proc.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply