February 22, 2011 at 2:00 am
Hi
I get the above error putting adding print statments in the code for the cursor I've written otherwise the cursor code executes fine but isn't updating the target table.
I have the cursor below which look at the source table that has the same field sizes as the variables declared and checks target table for if the data is there if not it inserts - if it is and the company name is different it updates the company name or if the groupcompany (category) is X in the target it updates it to what is in the source table (I'm using sql 2008)
Is there anything obvious you can see
BEGIN TRAN
Declare @AccId varchar(10), @GrpCmp varchar(1),@CmpName varchar(50), @Addr1 varchar(30), @Addr2 varchar(30), @Addr3 varchar(30), @Addr4 varchar(30), @Pstcd char(8), @Tel varchar(20) , @Fax varchar(20), @ContName varchar(20), @ContName2 varchar(20)
--source table
DECLARE Cursor1 Cursor
For
Select AccountId, GroupCompany, Companyname, Addr1, Addr2, Addr3, Addr4, Postcode, Telephone, Fax, ContactName, ContactName2
from dbo.source_table
OPEN Cursor1;
FETCH NEXT FROM Cursor1
INTO @AccId, @GrpCmp, @CmpName, @Addr1, @Addr2, @Addr3, @Addr4, @Pstcd, @Tel, @Fax, @ContName, @ContName2
WHILE @@FETCH_STATUS=0
BEGIN
IF NOT exists(Select @AccId, @GrpCmp from dbo.Customer )
IF NOT EXISTS(Select @AccId from dbo.Customer WHERE GroupCompany='X')
BEGIN
INSERT INTO dbo.Customer (AccountID, CompanyName, Addr1, Addr2, Addr3, Addr4, Postcode, Telephone, Fax, ContactName, ContactName2)
Values( @AccId, @CmpName, @Addr1, @Addr2, @Addr3, @Addr4, @Pstcd, @Tel, @Fax, @ContName, @ContName2 )
END
Else IF Not exists(Select @GrpCmp from dbo.Customer where AccountID=@AccId and GroupCompany='X')
BEGIN
Update dbo.Customer
Set GroupCompany=@GrpCmp
where CURRENT OF Cursor1
END
Else
IF Not exists (Select @CmpName from dbo.Customer where AccountID=@AccId and GroupCompany=@GrpCmp )
BEGIN
Update dbo.Customer
Set CompanyName=@CmpName
where CURRENT OF Cursor1
END
FETCH NEXT FROM Cursor1
INTO @AccId, @GrpCmp, @CmpName, @Addr1, @Addr2, @Addr3, @Addr4, @Pstcd, @Tel, @Fax, @ContName, @ContName2
END
CLOSE Cursor1
DEALLOCATE Cursor1
COMMIT TRAN
Thanks
February 23, 2011 at 2:44 am
Hello,
your cursor is over a table named dbo.source_table, so your update must be over the same table;
Update dbo.Customer
Set CompanyName=@CmpName
where CURRENT OF Cursor1
This code doesn't work because Cursor1 doesn't point to any row in the Customer table.
As I can see your queries are wrongly coded. For instance, coding
IF NOT exists(Select @AccId, @GrpCmp from dbo.Customer )
IF NOT EXISTS(Select @AccId from dbo.Customer WHERE GroupCompany='X')
is the same than coding
IF NOT exists(Select 'my', 'god' from dbo.Customer )
IF NOT EXISTS(Select 'oops' from dbo.Customer WHERE GroupCompany='X')
In the first question you ask for a empty table, the second question asks for any customer in group 'X'. I suppose you would code
IF NOT exists(Select AccountId from dbo.Customer WHERE AccountId = @AccId AND GroupCompany = @GrpCmp)
IF NOT EXISTS(Select AccountId from dbo.Customer WHERE AccountId = @AccId AND GroupCompany='X')
Codi these EXISTS maybe is correct but surely is poor in performance; you will do better including these conditions in your cursor through a JOIN, the code will be more clear and will run faster.
If your cursor runs over a query like that,
Select SRC.AccountId, SRC.GroupCompany, SRC.Companyname, SRC.Addr1, SRC.Addr2, SRC.Addr3, SRC.Addr4, SRC.Postcode, SRC.Telephone, SRC.Fax, SRC.ContactName, SRC.ContactName2
, CUS.AccountId, CUSX.AccountId
from dbo.source_table SRC
LEFT JOIN dbo.Customer CUS ON SRC.AccountId = CUS.AccountId AND SRC.GroupCompany = CUS.GroupCompany
LEFT JOIN dbo.Customer CUSX ON SRC.AccountId = CUS.AccountId AND SRC.GroupCompany = 'X'
your first and second EXISTS can be replaced by somethingt like
IF @CUS_Accid IS NULL
IF @CUSX_Accid IS NULL
Hope that this helps,
Francesc
February 23, 2011 at 2:07 pm
yoho23_2000 (2/22/2011)
Is there anything obvious you can see
Yes, you're using a CURSOR when you should be using a set-based approach. I can't tell you the exact set-based approach to use, because you didn't provide sample data and expected results, but something like the following should get you close. (NOTE: Since you are using SQL 2008, you'll probably want to use the MERGE feature. I don't have 2008, so I don't know the syntax for the MERGE.)
UPDATE dbo.Customer
SET GroupCompany=@GrpCmp, CompanyName=@CmpName
FROM dbo.Customer
INNER JOIN dbo.Source_Table
ON Customer.AccountID = Source_Table.AccountID
AND Customer.GroupCompany IN ('X', Source_Table.GroupCompany)
INSERT INTO dbo.Customer (AccountID, GroupCompany, CompanyName, Addr1, Addr2, Addr3, Addr4, Postcode, Telephone, Fax, ContactName, ContactName2)
SELECT Source_Table.AccountID, Source_Table.GroupCompany, Source_Table.CompanyName, Source_Table.Addr1, Source_Table.Addr2, Source_Table.Addr3, Source_Table.Addr4, Source_Table.Postcode, Source_Table.Telephone, Source_Table.Fax, Source_Table.ContactName, Source_Table.ContactName2
FROM dbo.Source_Table
LEFT OUTER JOIN dbo.Customer
ON Customer.AccountID = Source_Table.AccountID
AND Customer.GroupCompany IN ('X', Source_Table.GroupCompany)
WHERE Customer.AccountID IS NULL
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply