November 10, 2008 at 1:03 pm
How do I write a stored procedure that inserts if record is not there and updates if record is there?
////Alter Procedure
ALTER PROCEDURE [dbo].[BILLTO_Customers]
AS
DECLARE @message varchar(50),
set @message = ''
set nocount on
BEGIN
Declare BILL_TO_cursor CURSOR FOR
SELECT Account_number, Customer_name, Known_As, Legacy_Account_Number,Account_Established_Date, Status from AVW_11i_WA_BILLTO_Customers
OPEN BILL_TO_cursor
FETCH NEXT FROM BILL_TO_cursor
IF @@FETCH_STATUS = 0
select message @message = 'Not There'
BEGIN
FETCH NEXT FROM BILL_TO_cursor
CLOSE BILL_TO_cursor
DEALLOCATE BILL_TO_cursor
END
INSERT INTO dbo.Customers
(CustomerID, Name, ShortName, ReferenceNo, EffectiveDate, Deleted, Status)
VALUES
(@CustomerID, @Name, @ShortName, @ReferenceNo, @EffectiveDate, @Deleted, @status)
END
November 10, 2008 at 1:47 pm
Basic concept:
IF EXISTS(Select * From SomeTable Where Condition = True) -- Test the existence of your data
UPDATE SomeTable
...
ELSE
INSERT INTO Sometable...
That works for one row. If you're wanting to insert a whole set, the concept changes only slightly:
Select * -- Select rows you need to insert into temp table.
INTO #RowsToInsert
from RowsToHandle WHERE NOT EXISTS (Select * From SomeTable Where Condition = True and SomeTable.SomeValue = RowsToHandle.SomeValue) -- Find out what rows aren't there
INSERT INTO SomeTable -- Insert your rows.
SELECT ...
FROM #RowsToInsert
DELETE FROM RowsToHandle WHERE SomeValue IN #RowsToInsert -- Get rid of rows you inserted
UPDATE SomeTable -- Update the rest
SET ...
There may be more elegant ways of handling a whole set of data.
November 10, 2008 at 1:50 pm
what about adding the cursor to read each row
November 10, 2008 at 1:56 pm
Shouldn't be necessary, and probably isn't efficient. I don't see the need for a cursor here.
November 10, 2008 at 1:58 pm
Similar to what Seth has already said and based on your existing procedure:
[font="Courier New"]ALTER PROCEDURE [dbo].[BILLTO_Customers]
AS
SET NOCOUNT ON
-- updates existing customers
UPDATE dbo.Customers
SET Name = A.Customer_Name,
ShortName = A.Known_As,
ReferenceNo = A.Legacy_Account_Number,
EffectiveDate = A.Account_Established_Date,
Status = A.Status
FROM
dbo.Customers C JOIN
AVW_11i_WA_BILLTO_Customers A ON
C.CustomerID = A.Account_Number
-- inserts new ones
INSERT INTO dbo.Customers
(
CustomerID,
Name,
ShortName,
ReferenceNo,
EffectiveDate,
Deleted,
Status
)
SELECT
A.Account_Number,
A.Customer_Name,
A.Known_As,
A.Legacy_Account_Number,
A.Account_Established_Date,
'' AS Deleted, -- I have no idea what this should be
A.Status
FROM
AVW_11i_WA_BILLTO_Customers A LEFT OUTER JOIN
dbo.Customers C ON
A.Account_Number = C.Customer_ID
WHERE
C.Customer_ID IS NULL[/font]
This assumes that Account_Number is the Customer_ID. I also don't know what Deleted should be set to based on the information you have already provided.
Also a cursor will kill performance. If my assumptions are correct then the code I provided will work in a set-based manner with the joins doing the work of the cursor.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 10, 2008 at 2:02 pm
Jack Corbett (11/10/2008)
Similar to what Seth has already said and based on your existing procedure:
This would be that aforementioned "more elegant way". :hehe:
November 10, 2008 at 2:05 pm
paul.starr (11/10/2008)
How do I write a stored procedure that inserts if record is not there and updates if record is there?////Alter Procedure
ALTER PROCEDURE [dbo].[BILLTO_Customers]
AS
DECLARE @message varchar(50),
set @message = ''
set nocount on
BEGIN
Declare BILL_TO_cursor CURSOR FOR
SELECT Account_number, Customer_name, Known_As, Legacy_Account_Number,Account_Established_Date, Status from AVW_11i_WA_BILLTO_Customers
OPEN BILL_TO_cursor
FETCH NEXT FROM BILL_TO_cursor
IF @@FETCH_STATUS = 0
select message @message = 'Not There'
BEGIN
FETCH NEXT FROM BILL_TO_cursor
CLOSE BILL_TO_cursor
DEALLOCATE BILL_TO_cursor
END
INSERT INTO dbo.Customers
(CustomerID, Name, ShortName, ReferenceNo, EffectiveDate, Deleted, Status)
VALUES
(@CustomerID, @Name, @ShortName, @ReferenceNo, @EffectiveDate, @Deleted, @status)
END
Assumptions:
AVW_11i_WA_BILLTO_Customers is the table from which updates/inserts are to occur.
dbo.Customers is the table where data is to be updated/inserted
CustomerID is the primary key in dbo.Customers
Here is the code:
update dbo.Customers set
Name = Customer_name [,...]
from
dbo.Customers cust
inner join dbo.AVW_11i_WA_BILLTO_Customers newcust
on (cust.Customer_ID = newcust.Account_number);
insert into dbo.Customers
select Account_number, Customer_name, Known_As, Legacy_Account_Number,Account_Established_Date, Status
from
dbo.AVW_11i_WA_BILLTO_Customers newcust
left outer join dbo.Customers cust
on (newcust.Account_number = cust.Customer_ID)
where
cust.Customer_ID is null;
November 10, 2008 at 2:14 pm
Customers is the table and AVW_11i_WA_BILLTO_Customers is a view i created that displays the data selected by the query. Some columns in my view are related to some columns in the table that either need to be updated or inserted into.
SELECT Account_number, Customer_name, Known_As, Legacy_Account_Number,Account_Established_Date, Status from AVW_11i_WA_BILLTO_Customers
INSERT INTO dbo.Customers
(CustomerID, Name, ShortName, ReferenceNo, EffectiveDate, Status,CreateDate)
CustomerID = Account_#,
Name = Customer_Name,
ShortName = Known_AS,
ReferenceNo= Legacy_Account_#,
EffectiveDate =Account_estab_date,
Status = Status
CreateDate = ACcount_estab_date
November 10, 2008 at 3:05 pm
when i run this i get an error:
Msg 208, Level 16, State 1, Procedure BILLTO_Customers, Line 7
Invalid object name 'dbo.cust.Customers'.
USE [AtriumWOE_CS_UserData_52]
GO
/****** Object: StoredProcedure [dbo].[BILLTO_Customers] Script Date: 11/10/2008 16:02:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BILLTO_Customers]
AS
SET NOCOUNT ON
-- updates existing customers
UPDATE dbo.cust.Customers
SET
CustomerID = Account_Number,
Name = Customer_Name,
ShortName = Known_As,
ReferenceNo = Legacy_Account_Number,
EffectiveDate = Account_Established_Date,
Status = Status,
CreateDate = Account_Established_Date
from
dbo.cust.Customers
inner join dbo.AVW_11i_WA_BILLTO_Customers newcust
on (cust.CustomerID = newcust.Account_number);
-- inserts new ones
insert into dbo.cust.Customers
select Account_number, Customer_name, Known_As, Legacy_Account_Number,Account_Established_Date, Status, CreateDate
from
dbo.AVW_11i_WA_BILLTO_Customers newcust
left outer join dbo.Customers cust
on (newcust.Account_number = cust.CustomerID)
where
cust.CustomerID is null;
November 10, 2008 at 3:12 pm
paul.starr (11/10/2008)
when i run this i get an error:Msg 208, Level 16, State 1, Procedure BILLTO_Customers, Line 7
Invalid object name 'dbo.cust.Customers'.
USE [AtriumWOE_CS_UserData_52]
GO
/****** Object: StoredProcedure [dbo].[BILLTO_Customers] Script Date: 11/10/2008 16:02:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BILLTO_Customers]
AS
SET NOCOUNT ON
-- updates existing customers
UPDATE dbo.cust.Customers
SET
CustomerID = Account_Number,
Name = Customer_Name,
ShortName = Known_As,
ReferenceNo = Legacy_Account_Number,
EffectiveDate = Account_Established_Date,
Status = Status,
CreateDate = Account_Established_Date
from
dbo.cust.Customers
inner join dbo.AVW_11i_WA_BILLTO_Customers newcust
on (cust.CustomerID = newcust.Account_number);
-- inserts new ones
insert into dbo.cust.Customers
select Account_number, Customer_name, Known_As, Legacy_Account_Number,Account_Established_Date, Status, CreateDate
from
dbo.AVW_11i_WA_BILLTO_Customers newcust
left outer join dbo.Customers cust
on (newcust.Account_number = cust.CustomerID)
where
cust.CustomerID is null;
problem is here:
from
dbo.cust.Customers
inner join dbo.AVW_11i_WA_BILLTO_Customers newcust
on (cust.CustomerID = newcust.Account_number);
should be
from
dbo.Customers cust
inner join dbo.AVW_11i_WA_BILLTO_Customers newcust
on (cust.CustomerID = newcust.Account_number);
November 10, 2008 at 3:13 pm
It's not dbo.cust.Customers. You only have dbo.Customers. cust is an alias that you would use in place of the table name (dbo.Customers).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 10, 2008 at 3:14 pm
Can you explain why you are "updating" the primary key in your update statement?
November 10, 2008 at 3:25 pm
error message:
Msg 515, Level 16, State 2, Procedure BILLTO_Customers, Line 7
Cannot insert the value NULL into column 'EffectiveDate', table 'AtriumWOE_CS_UserData_52.dbo.Customers'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Msg 208, Level 16, State 1, Procedure BILLTO_Customers, Line 25
Invalid object name 'dbo.cust.Customers'.
USE [AtriumWOE_CS_UserData_52]
GO
/****** Object: StoredProcedure [dbo].[BILLTO_Customers] Script Date: 11/10/2008 16:02:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BILLTO_Customers]
AS
SET NOCOUNT ON
-- updates existing customers
UPDATE dbo.Customers
SET
CustomerID = Account_Number,
Name = Customer_Name,
ShortName = Known_As,
ReferenceNo = Legacy_Account_Number,
EffectiveDate = Account_Established_Date,
Status = Status,
CreateDate = Account_Established_Date
from
dbo.Customers cust
inner join dbo.AVW_11i_WA_BILLTO_Customers newcust
on (cust.CustomerID = newcust.Account_number);
-- inserts new ones
insert into dbo.cust.Customers
select Account_number, Customer_name, Known_As, Legacy_Account_Number,Account_Established_Date, Status, CreateDate
from
dbo.AVW_11i_WA_BILLTO_Customers newcust
left outer join dbo.Customers cust
on (newcust.Account_number = cust.CustomerID)
where
cust.CustomerID is null;
November 10, 2008 at 3:37 pm
You may need to edit the data. If you have nulls where you can't have them, you will need to provide a proper alternative using ISNULL.
November 10, 2008 at 3:45 pm
EffectiveDate column is NULL
Account_Established_Date column is from the view, i dont understand what im supposed to do next
Viewing 15 posts - 1 through 15 (of 61 total)
You must be logged in to reply to this topic. Login to reply