Delete And Insert In The Same Sp

Nov 15, 2005

Here's my sp. Before I insert my new values I want to delete the existing records from the table. Can I do this in the same sp ? If so, could someone tell me the syntax please.

CREATE PROCEDURE [spRB_AddBlockBookingDateRef]

** here Delete * from tblRB_BlockBookingDates

@strBookingDateRef nvarchar(100),
@strRoomRef nvarchar (50),
@strDateRequired datetime

AS

INSERT INTO tblRB_BlockBookingDates(
BB_BookingDateRef,
BB_RoomRef,
BB_DateRequired)


VALUES
(@strBookingDateRef,
@strRoomRef,
@strdateRequired
)
GO

View 5 Replies


ADVERTISEMENT

Delete After Insert

Jun 24, 2008

Using OrderRebate Table.

Have two records getting inserted. All fields are identical except for cd_tp.

If 2 records are inserted I would like the record with cd_tp 3 deleted.

Code below doesn't seem to be working


delete orderrebate
FROM OrderRebate inner join inserted on inserted.ord_type = OrderRebate.ord_type and
inserted.ord_no = OrderRebate.ord_no and inserted.line_seq_no = OrderRebate.line_seq_no
where inserted.ord_type = OrderRebate.ord_type and inserted.ord_no = OrderRebate.ord_no
and inserted.line_seq_no = OrderRebate.line_seq_no and inserted.cd_tp = '3'
END

View 1 Replies View Related

Update Or Delete With Insert?

Dec 13, 2007

Hi All,
I have this project I'm working on it's Product Content Management System rewrite. I got to the point of updating the Product By Sku and not sure if I should use UPDATE statement or I should DELETE sections assosiated with the ProductContentID and then re-insert them again? I'm not sure which is more afficient?
I can really do both and it's really not that complicated, the only problem I see with DELETE then INSERT is the ProductContentSectionId in the Sections table is going to grow very fast and I'm a bit concerned about it.
We use SQL Server 2000 and we have about 4 bound tables where we keep the data. The one I'm talking about is the sections table where we keep the actual types of product content like a BoxShot, Description, Key Features and so on...
Thank you in advance!
Tatyana Hughes
 

View 3 Replies View Related

FeedBack After Insert Or Delete

Jan 6, 2008

 
hi all
iwant to make feedback after insert data or delet date Like( the data is sucssfuly way) or(data is delete sucssuse way)
but not alert iwant feedback
thank you

View 4 Replies View Related

Insert And Delete Problem

Feb 23, 2006

hello all i have a problem in deleting and updating records when i configuring my data source i click on advanced button in the configuration datasource and then i check the two boxes to generate insert and update and optimistic concurrency but the problem is these two choices are not active unless there was a primary key in the table i have some tables doesnt have a primary key how i can fix that what should i do to allow insert and delete in these tables.thanks in advance

View 4 Replies View Related

Update, Delete, Insert

Jan 8, 1999

I am trying to update a SQL database with data from a Wang system. The Wang data is dumped to a txt file. I then import it into an update table in SQL via Access. Some of the data is new and some of the data is updated records. At this point I have been trying to create a script to update and add data to a table via the query tool in SQL Then delete data from the update table.

I was able to get the UPDATE and DELETE to work but I have not figured out how to insert new records at the same time? Can I use an IF statement? I will apreciate any help or sample code, Thanks.

UPDATE MemberList
Set Name = NameUpd, Address1 = Address1Upd, Address2 = Address2Upd, City = CityUpd, State = StateUpd, ZipCode = ZipCodeUpd, MemberStatus = MemberstatusUpd
FROM MemberList, MemberListUpd
WHERE MemberList.MemberNumber = MemberListUpd.MemberNumberUpd

INSERT ?

DELETE MemberListUpd

View 2 Replies View Related

Insert And Delete In One Statement

Jan 2, 2007

i am creating an insert based on a select statement -- i need to delete the row from the select statment table after it has been inserted

something like

insert into table_insert(value1, value2)
(select table_exclude_id, value1, value2 from table exclude)
delete from table_exclude where table_exclude_id in "the select statement"

can you do this?

View 4 Replies View Related

Delete/insert Vs Update

Jan 22, 2008

Im trying to keep a mirror image of some data Im getting from Quickbooks.

As the records are inserted into the database I need to check if a record exists and either update or insert a new one.

it seems easier just to delete using the tnxid and reinsert vs updating

my question is if I go

begin

INSERT INTO QBInvoicesQue(100s of feilds)

end


begin

delete from QBInvoices where txnid = @TxnID

end

and there is not matching txnid to delete from will it cause any problems? before going to the insert statement?


begin

INSERT INTO QBInvoices(100s of feilds)

end

View 6 Replies View Related

DELETE FROM Table Before INSERT XML

Feb 27, 2008

I like to empty my table before I insert a xml. How can this be done in SSIS?

View 1 Replies View Related

Update = Delete && Insert?

Oct 16, 2007

I vaguely recall reading an article that I can no longer find that an update statement is executed as a combination of a Delete and an Insert by SQL server. Does anyone know if this a still a true statement in SQL Server 2005?

Thanks,

-shl

View 10 Replies View Related

Insert And Delete In Cursor

May 1, 2007

I want to do insert and delete in a cursour but it's not working for me can any one check the below code and help me out to solve the problem,



DECLARE @LINKID AS INT

DECLARE @INITCOUNT AS INT

DECLARE @NumberOfDuplicates AS INT

SELECT @InitCount = (SELECT COUNT(*) FROM [SSDTemp])

Declare DedupeDataInSSDTempTableNewCur Cursor For

SELECT LINKID FROM SSD WHERE LINKID IS NOT NULL

OPEN DedupeDataInSSDTempTableNewCur

FETCH NEXT FROM DedupeDataInSSDTempTableNewCur

into @LINKID

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @CNT AS INT

SELECT @CNT = COUNT(LINKID) FROM SSD WHERE LINKID=@LINKID

if (@cnt =1) begin

INSERT INTO SSD (

[CallID],

[LinkID], [FirstName], [MiddleInit], [LastName],

[Suffix], [DateOfBirth], [SSN], [PhoneNumber],

[Addr1], [Addr2], [City], [State], [Zip] )

SELECT top 1

[CallID], [LinkID], [FirstName], [MiddleInit],

[LastName], [Suffix], [DateOfBirth], [SSN],

[PhoneNumber], [Addr1], [Addr2], [City], [State], [Zip] from SSDTemp

where linkid = @LINKID



DELETE top 1 from ssdtemp where linkid=@LINKID

end

if (@cnt =0) begin

INSERT INTO SSD (

[CallID],

[LinkID], [FirstName], [MiddleInit], [LastName],

[Suffix], [DateOfBirth], [SSN], [PhoneNumber],

[Addr1], [Addr2], [City], [State], [Zip] )

SELECT top 2

[CallID], [LinkID], [FirstName], [MiddleInit],

[LastName], [Suffix], [DateOfBirth], [SSN],

[PhoneNumber], [Addr1], [Addr2], [City], [State], [Zip] from SSDTemp

where linkid = @LINKID

DELETE top 2 from ssdtemp where linkid=@LINKID of DedupeDataInSSDTempTableNewCur

End

SELECT @NumberOfDuplicates = @InitCount - (SELECT COUNT(*) FROM [SSDTemp])



FETCH NEXT FROM DedupeDataInSSDTempTableNewCur

INTO @LINKID

END

CLOSE DedupeDataInSSDTempTableNewCur

DEALLOCATE DedupeDataInSSDTempTableNewCur

View 2 Replies View Related

Best Practices For Insert/Update/Delete

Jul 24, 2007

 for now, doing a small school project, i find doing SPs for Insert useful, like checking for existing data and not inserting, that might not be the best method, i had advice from here i can use unique constraints instead, then what about update and delete? SPs also? the pros make SPs for everything? currently use dynamically generated SQL from SqlDataSources. for Update / delete. some delete are SPs too...

View 2 Replies View Related

Can't Update, Insert, Or Delete Rows

Oct 20, 2007

I have recently started an ASP.Net application and am having some issues updating, inserting and deleting rows. When I started working with it, I was getting errors because it could not find any update command. Eventually, I figured out how to automatically generate the commands, by configuring my SQLDataSource control and clicking the "advanced" button. Right now though, I have generated the commands, but I still can not insert, update or delete rows. When I attempt to update anything, I recieve an error that says "The data types text and nvarchar are incompatible in the equal to operator." Nowhere in my table do I have any rows that use the datatype "nvarchar", only "text" and "int". I tried switching all of my text columns to "nvarchar(500)", which did not help. I am led to believe that the auto generated SQL procedures are trying to do something behind the scenes that is making my database act up, because even when I delete rows, I get the same exception, so the datatypes cannot be messed up there, because all that the datasource is doing is deleting rows, therefore there is no need to worry about data types. I only get the error when I check the "Use optimistic concurrency" box. When I do not use optimistic concurrency, I can delete, insert, and update rows... but nothing happens. There are no errors, but nothing is deleted, updated or inserted either. Upon postback, nothing has changed. I may upload a copy of the exact exception page, if someone thinks that it may help. Here is the update command that was generated: UPDATE [Record Information] SET [Speed] = @Speed, [Recording Company] = @Recording_Company, [Year] = @Year, [Artist] = @Artist, [Side 1 Track Title] = @Side_1_Track_Title, [Side 1 Track Duration] = @Side_1_Track_Duration, [Side 2 Track Title] = @Side_2_Track_Title, [Side 2 Track Duration] = @Side_2_Track_Duration, [Sleeve Description] = @Sleeve_Description WHERE [Record Database ID] = @original_Record_Database_ID
Apparently no stored procedures exist for any of these operations, and I am unsure why. The "Record Database ID" is my identity column, and is the only field that is (and is supposed to be) uneditable.

View 1 Replies View Related

Advanced (insert, Delete ..) Option Not Available

Feb 18, 2008

Hi,
i want to create a sqldatasource in VWD also with advanced (insert, delete, update) option.
This works fine for 'normal' tables, but when i try to do that with e.g. aspnet_roles table (indicated as vw_aspnet_Roles in dropdownlist of VWD), or aspnet_users table, that advanced option is not available, even when i select all fields.
Why, and how can i solve that?
Thanks
Tartuffe
 

View 1 Replies View Related

SQL Update/Insert/Delete Problem

Nov 13, 2005

Hi,I just upgraded my ASP.NET 2.0 BETA 2 environment to the final release of ASP.NET 2.0 VWD.Once the update was finished, I could open my website without any problems..... Now, I noticed that in the final release, some modifications have been included in the Membership Stored Procedure and other stored procedures. So I created a new database (SQL Express) and added my data again.After re-creating my SQLDataSources, I tryed to enable the Editing and Deleting option in VWD and once I run my web application, it seems when selecting editing and then update, it doesn't work anymore....This is my code :
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IMMOASPNETDBConnectionString %>"
DeleteCommand="DELETE FROM aspnet_test WHERE (testID = @Original_testID)" SelectCommand="SELECT BuyID, BuyNL, BuyFR, Lastupdated FROM aspnet_Buy"
UpdateCommand="UPDATE aspnet_Buy SET BuyNL = @BuyNL, BuyFR = @BuyFR WHERE (BuyNL = @original_BuyID)">
<DeleteParameters>
<asp:Parameter Name="Original_testID" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="BuyNL" />
<asp:Parameter Name="BuyFR" />
<asp:Parameter Name="original_BuyID" />
</UpdateParameters>
</asp:SqlDataSource>
&nbsp;<br />
&nbsp;<br />
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
DataKeyNames="BuyID" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="BuyID" HeaderText="BuyID" InsertVisible="False" ReadOnly="True"
SortExpression="BuyID" />
<asp:BoundField DataField="BuyNL" HeaderText="BuyNL" SortExpression="BuyNL" />
<asp:BoundField DataField="BuyFR" HeaderText="BuyFR" SortExpression="BuyFR" />
<asp:BoundField DataField="Lastupdated" HeaderText="Lastupdated" SortExpression="Lastupdated" />
</Columns>
</asp:GridView>Can someone help me with this ? What is wrong with the Update command ?Thanks to all,Bart

View 3 Replies View Related

SqlDataSource: Insert, Update And Delete Not Available

Jan 3, 2006

Hello Guys,
 
I’m trying to create a sqlDataSource using the wizard, I can get the select statement, the here clause and the order by clause but when I click on the Advanced button the options to generate the insert, update and delete statements are not available. Does anyone know what am I doing wrong?

View 4 Replies View Related

Insert, Select, Update And Delete

Apr 7, 2006

I've got four pages with in the first page a insert, in the second a select, in the thirth a update and in the fourth a delete statement. First the values of a textbox will be inserted in the database, then the values will be shown in labels and than it is possible to edit or delete the values inserted. Every inserted item belonging to each other has one ID. The follwing values has a second ID etc.
How can I make that possible?? I think that I should pass the ID's between the pages so I'm sure that I edit or delete the values that I want. So insert value 1 in page 1, show with select value 1 in page 2, edit or delete value 1 in page 3 and 4.
Maybe I didn't explain it good enough for you, please tell me then!!
Thanks!!

View 3 Replies View Related

Granting Insert, Delete Permissions

Sep 15, 2000

When granting INSERT, DELETE permissions, is this done for
the logon-id OR for the user associated with the logon-id?

I know you can do this for roles.

View 2 Replies View Related

TRIGGER FOR INSERT,UPDATE,DELETE

Aug 15, 2001

I HAVE TWO TABLES IN THE DATABSE AND THE SECOND TALE SI FOR AUDITING.
I WANT CREATE THE TRIGGER ON FIRST TABLE SO THAT I CAN PUT THE STATUS
LIKE INSERT,UPDATE OR DELETE IN THE STATUS COLUMN IN SECOND TABLE.
CAN SOMEBODY HELP IN WRITING THAT TRIGGER..?
HOW CAN I DETERMINE WAETHER THE RECORD IS BEEN INSERTED OR UPDATED OR DELETED.

DO I HAVE TO WRITE A SEPERATE TRIGGER FOR EACH ACTIVITY..OR I CAN WRITE IT IN THE
SINGLE TRIGGER..?

PLEASE SUGGEST ME..ITS URGENT.

THANKS IN ADVANCE
HARISH

View 2 Replies View Related

What Is Faster?! Update Or Delete And Insert

Dec 6, 2007

Hello,

What is faster / better for performance?

Check if something already excist and update the changed values? or delete the whole table and then insert everything?

Thanks!

View 2 Replies View Related

Insert/Delete Trigger Misfires

Jan 6, 2006

I am having problems with a trigger that is designed to audit changes to a particular field in a table. If that field is updated, then the old record is inserted into an audit table.

This trigger never fails when I run test data against it from Query Analyzer. It works some of the time when the web application updates it, fails other times.

Typically, multiple records are updated at the same time. Any ideas?

Here is the Trigger:

create trigger t_u_product_rate_detail
on product_rate_detail
for insert, update, delete

as

/--Local variable
declare
@auditdate datetime,
@audituser sysname

--Set values so function isn't executed a bunch of times
select
@auditdate = getdate(),
@audituser = suser_sname()

if exists (select * from inserted)
begin
if exists (select * from deleted)
begin
insert into product_rate_detail_audit_log
select d.product_rate_detail_id,
d.product_rate_id,
d.day_of_week_id,
d.ad_size_id,
d.rate,
d.plan_vol,
d.plan_freq,
@auditdate, @audituser, 'U'
from deleted d
join inserted i on i.product_rate_detail_id = d.product_Rate_detail_id
where (d.rate <> 0 and d.rate is not null)
and i.rate <> d.rate -- this determines if the rate has changed.
end
end


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

View 2 Replies View Related

Insert/ Update/ Delete Slowness.

Jul 10, 2006

SQL2K sp4

Howdy all. I opened a 200 mb. file in Query Analyzer that is full of Inserts/ Updates/ and Deletes. I tried just to parse it, and killed it after 18 hours. There is no blocking. All of the appropriate indexes exist. I even removed them and retried JIC. The box is plenty powerful for this task. Does anyone have any ideas?
I've tried several times with no luck. At the top of the file is SET IMPLICIT_TRANSACTIONS ON and then every 10,000 statements is COMMIT WORK. I've tried adjusting the number of commits to a lower number with no luck. This works fine on smaller files (3 - 20 mb).

View 1 Replies View Related

CAN I INSERT,DELETE,UPDATE2tables At The Same Time

Apr 25, 2008

i've read the transact-sql command,

i known that the select command use to retrieve many fields from many tables with one command

select * from table1,table2

yes,

but i ' ve not seen the way to add,delete or update those fields from those tables with one command...



Is it possible? why?

I don't have any idea , can u help me

I want to know the sql commands , if it's possible

thanks for reply,

mochi

View 4 Replies View Related

How To Insert / Update And Delete Without Merge

Feb 18, 2014

How to Insert,Update and delete through script without using merge.I have simple requirement of Deleting,Inserting and Update from one table to other table...Here is the Sample Data

CREATE TABLE #Table1
(ID INT ,Name VARCHAR(30),DATEKEY INT)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (1,'Mohan',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (2,'Raj',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (3,'Majja',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (4,'Majjaa',20131231)

[code]...

So now i need to update 1st record and add another new record..So i need to update as well as delete the existing data in Target table.

INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (5,'Macha',20131231)

My output should come like this one :

IDNameDATEKEY
1Mohan20131231
5Macha 20131231

with out using merge how can i handle Update,insert and Delete through TSQL

View 5 Replies View Related

Insert Update Delete Trigger?

Dec 2, 2014

I have to create a trigger that will log who changed information on a table and when (NOT what they have changed).

My idea is to get the users name and see if it is in a table if not create it and get the associated ID, also get the ID of table that was accessed along with the ID of the type of task that was performed. Take this data and insert it into a table.

Here is the SQL I have so far.

-- Primary Database Tables --
CREATE TABLE Physician (
Physician_ID int not null identity(1,1) primary key,
First_Name varchar(100),
Last_Name varchar(100),
Mobile_Number varchar(15),
Pager_Number varchar(15)

[code].....

View 1 Replies View Related

[RESOLVED]Delete And Insert Into Table Using SP

Apr 23, 2007

hi,
how can I use stored proc to delete all rows from a table (table1) and insert new ones.
for instance.
my query is:
-------------------------------------
select field1, two, three
from table1 innerjoin table2 on etc...
where condition1 and condition2
-------------------------------------
I want to place the result in my table (table1) and everything I run the stored pro I want to delete the content and place the new dataset in the table1?


I hope I'm clear what I'm trying to do.
thank you very much in advance.
P.S please be detailed when explaining.

View 3 Replies View Related

Trigger On Insert To Delete Record

Mar 26, 2008

Can anybody help me in writing a SQl trigger to delete a record when inserted after 70 seconds.?

View 20 Replies View Related

Why DELETE Takes Longer Than INSERT?

Jul 20, 2005

I'm running an ISP database in SQL 6.5 which has a table 'calls'. When thenew month starts I create a new table with the same fields and move the dataof previous month into that table and delete it from calls. So 'calls' holdsthe data of only the current month. for example at the start of november2003 I ran the queriesCreate Table Oct2003Calls {................................}/* Now insert data of october into new table */INSERT Oct2003CallsSELECT *FROM callsWHERE calldate < '11/1/03'/* Finaly delete october data from calls table */DELETE FROM callsWHERE calldate < '11/1/03'The problem is that while the insert query takes about 2 minutes to executethe delete queries takes over 10 minutes to affect the same no. of rows. Whyis that?This causes problems because user authentication stops when this query isrunning which means users cant connect to the internet.

View 4 Replies View Related

SSIS Delete/Insert/Update

Mar 20, 2006

I am still learning SSIS.

What is the best way to do delete/insert/update in a database in SSIS. I am looking to achieve something similar to what was there in a Data Driven Query in SQL Server 2000. I would like to delete/insert/update the destination table based on the condition that my lookuptable returns

For ex

If "the user in my source table has changed the status" Then

Update the user row in the destination table

Else skip the row

If "the user in my source table has delete flag =1 " Then

Delete the user in the destination table

Else skip the row

If "the user in my source table is new(has a new ID) " Then

Insert the user in the destination table

Else skip the row







Any suggestions ....

Cheers

Siaj

View 6 Replies View Related

Question About Trigger Insert Before Delete

Aug 14, 2007

Hello, everybody

I have a question about trigger.
I have a table called "Users", it has a ID (auto increment), UserName, FirstName...ect.
The question is if one row is deleted, I want to put this row into "Users_Histroy" Table whose structure is the same as "Users" Table and then delete this row in "Users" Table..
How can I achieve this? I am pretty new about triggers.
Thank you very much.

View 5 Replies View Related

SSIS SELECT / DELETE / INSERT

Mar 21, 2006

I am trying to do the following for a few tables in my DB. I have an ODBC datasource and would simply like to update my DB from this datasource. I want to pull the table data from my ODBC datasource, delete any existing data in my SQL tables and then insert the data from my ODBC datasource into SQL. I am trying to create an SSIS package to do this but am not sure where to start. Any help or examples would be appreciated.

View 6 Replies View Related

Insert/Update And Delete Change

Sep 2, 2006

hi,

I have a table which contained 5 columns and with 2 primary keys

Col 1 | Col 2 | Col 3 | Col 4 | Col 5 |

ab | 1 | abc | null | null

ab | 2 | def | null | null



Col 1 and Col 2 both are primary keys.



How do I update Col2 from 1 to 2 and from 2 to 1 in a single transaction statement and commit it?

Thanks







View 3 Replies View Related

SqlDataSource Not Allowing Insert, Update And Delete

Jul 4, 2006

i have default.aspx file and i put SqlDataSource into my designer form.when i want to configure SqlDataSource it not allowed my to insert, update and delete only insert is allowed.what is the problem and the solution for this case ...thx

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved