How can i do a multiple insert or multiple updates or inserts and updates to the same table..

Hi...

 I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...

 

this is my sproc...

 

ALTER   PROCEDURE [dbo].[usp_Import_Plan] 
	@ClientId int,
	@UserId int = NULL,
	@HistoryId int,
	@ShowStatus bit = 0		-- Indicates whether status messages should be returned during the import.

AS

SET NOCOUNT ON

DECLARE 
	@Count int,
	@Sproc varchar(50),
	@Status varchar(200),
	@TotalCount int

SET @Sproc = OBJECT_NAME(@@ProcId)

SET @Status = 'Updating plan information in Plan table.'
UPDATE 
	Statements..Plan 
SET 
	PlanName = PlanName1,
	Description = PlanName2
FROM
	Statements..Plan cp
		JOIN (
			SELECT DISTINCT
				PlanId,
				PlanName1,
				PlanName2
			FROM
				Census
		) c
		ON cp.CPlanId = c.PlanId
WHERE
	cp.ClientId = @ClientId
	AND 
	(
		IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
		OR
		IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
	)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
	SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
	END
ELSE
BEGIN
	SET @Status = 'No records were updated in Plan.'
	END

SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
	ClientId,
	ClientPlanId,
	UserId,
	PlanName,
	Description
	)
	SELECT DISTINCT
		@ClientId,
		CPlanId,
		@UserId,
		PlanName1,
		PlanName2
	FROM
		Census
	WHERE
		PlanId NOT IN (
			SELECT DISTINCT
				CPlanId
			FROM
				Statements..Plan
			WHERE
				ClientId = @ClientId
				AND
				ClientPlanId IS NOT NULL
			)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
	SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
	END
ELSE
BEGIN
	SET @Status = 'No information was added Plan.'
	END

SET NOCOUNT OFF

 

So how do i do multiple inserts and updates using this stored procedure...

 

Regards

Karen

0
Karenros
10/30/2007 1:25:27 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

5 Replies
1180 Views

Similar Articles

[PageSpeed] 5

Looks like youa re doing this per @clientid. Perhaps you can join the staging table with the clientid? You can do a bulk update followed by a bulk update. You might have to tweak this a little but I hope it gives you an idea:

 

UPDATE cp
SET PlanName = PlanName1,
	Description = PlanName2
FROM Statements..Plan cp
JOIN ( SELECT DISTINCT PlanId,PlanName1,PlanName2
		FROM Census
	) c ON cp.CPlanId = c.PlanId
JOIN StagingTable ST ON St.Clientid = cp.ClientId
WHERE
	(
	IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
	OR
	IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
	)

INSERT INTO Statements..Plan (
	ClientId,
	ClientPlanId,
	UserId,
	PlanName,
	Description
	)
SELECT DISTINCT
	ClientId,
	CPlanId,
	UserId,
	PlanName1,
	PlanName2
FROM Census C
JOIN StagingTable ST ON ST.Clientid = C.Clientid
WHERE NOT EXISTS ( SELECT DISTINCT CPlanId
			FROM Statements..Plan P
			WHERE P.ClientId = ST.ClientId
			AND P.CPlanId = C.CplanId
			AND ClientPlanId IS NOT NULL
			)
			
 
***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
10/30/2007 3:47:14 PM

Dinakar,

  Thanks for your anwer... in the above sproc that I pasted Census is the staging database table and Plan is the production database table...

Normally when a client sends in a file his he may either one Cplanid listed or more for eg,,, the data in the staging directory will be as follows..

CPlanId   PlanName1     PlanName2     RTNADDR1, RTNADDR2,  RTNADDR3

190121    ABC                 Plan              abc co.                           abc,WV,120330

132300   BCA                 Plan              bca co.                            bca,NC,28078

 etc

so i want my sproc to handle update if the plan exists and Insert a new one if they dont exist..

Thank you.

Regards,

Karen

 

0
Karenros
10/30/2007 3:59:23 PM

If you have a file per client, then the proc you have should be good enough. It would do batch insert for plans that dont exist and batch update for plans that exist. I would remove the WHERE condition for the update though and let it update all the planName and descriptions from the file rather than compare like this:

(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)

It will unnecessarily slow the query down. Worst case, if the planname's are same it will be updated back to the same name. If you have multiple files, you can open multiple windows in query analyzer and run the procs in parallel, or create multiple jobs and schedule them at the same time so they can run in parallel.


***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
10/30/2007 4:19:26 PM

Thanks a lot for your answer... And yes i do have multiple files....

but i am gonna write a master sproc and exec then exec the other sprocs to put the data in different tables....

 

any ways thanks a lot for your help..

oh I have a another question regarding updates...some times the  particular column wont update even thoough the values are different do u think... its because of the where condition... thats

(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)

Regards

Karen

0
Karenros
10/30/2007 4:28:22 PM

Karenros:
Thanks a lot for your answer... And yes i do have multiple files....

but i am gonna write a master sproc and exec then exec the other sprocs to put the data in different tables....

If you call the proc multiple times via a loop, then you are essentially doing it in serial, not in parallel.

Karenros:
  oh I have a another question regarding updates...some times the  particular column wont update even thoough the values are different do u think... its because of the where condition... thats

(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)

 
Possibly. String matching isnt always as straight forward as numbers matching, especially for these type of fields as description or planname. Even an additional space at the end can exclde the row from being selected. Hopefully you are using VARCHAR(X) and nor CHAR(X).

***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
10/30/2007 4:32:42 PM
Reply:

Similar Artilces:

Showing table columns from multiple tables in Formview, but insert/update/delete rows of Master table
I have a page on which I have GridView and FomView control. On selection of a row in GridView the row gets displayed in the Item template of the FormVIew. The values are the result of inner join from multiple tables i.e. table Stock,Product and Workshop.  Please let me know how I could insert/update/delete data for the base table i.e. Stock table, but be able to see the column values from the associated tables on the Itemtemplate,Edittemplate and Inserttemplate of the FormView. Thanks,Anita Hi:   Take a look at this tutorial. It's for DetailsView and same ...

How do you insert/update multiple tables in a datawindow
I have a datawindow that's designed using 2 tables. How do you insert and update both tables in the datawindow. Thanks, Jainine OK, imagine you have 2 tables : TABLE_A with 2 fields : field1 and field2 TABLE_B with 2 fields : fieldx, fieldy These 2 tables are used in your datawindow dw_1. - set TABLE_A updateable in the dw, - write a script like this : // Variable declarations string modstring // update of TABLE_A : without reset the flags IF dw_1.Update(True, False) = 1 THEN // modify the datawindow definition to update TABLE_B modstring = "DataWindow.T...

Problem with SQL Update/Insert Proc in a Gridview
Hi,I am using SQL 2005 and ASP.Net 2.0 with C# code behind and I am stuck.  What I am doing:I have a gridview that calls information from several tables to display user details - guid, first name, last name, phone number, etc.  Then I inserted an 'edit' command button which calls a stored proc that runs an update transaction on the tables.  The proc is linked (I don't know the correct term) through an object data source tied to a dataset that has a method that calls the proc.  I use this proc in another location and it works fine.The problem:When I create a ne...

Update Insert & Delete on Multiple Tables
Hi all I m stuck with a  strange problem. I have an Asp.net gridview that displays the data from more than one table like name & id from one table & Productid from another table. Now I have an Edit,delete Button with my gridview How can I fire a single query to update & delete from these two tables (Table1 (name & id) table2(Productid)). Or is there is any way other than a single query to Insert Update & Delte from two tables simultaneously. plz kindly help me. Thanks in advance Thanks & Regards HR...

Multiple Insert/Update/Delete-Triggers on same table
Dear all How can i create more than one insert/update/delete-trigger on a table? In newsgroups i saw the possibility of creating before- and after-triggers and of assigning the trigger an order number, but in Transact-SQL User's guide (12.5), there is just: create trigger [owner.]trigger_name on [owner.]table_name {for {insert , update , delete} as SQL_statements The reason in need this is that i have to create additional triggers to synchronize data for an application that already has triggers. I'd like not to touch the existing triggers but building my own ...

Multiple updates or insertions
Hello everybody,       I have an application which has been built on Asp.net with framwork 1.1 and Oracle 9i as database.I have a table named sequencetable which stores the serial numbers for the master tables.When the save button is clicked Iam fetching the maximum of the serial number for the particular master table from the  sequence table and then  updating the sequencetable as well as saving it into the mastertable with some other details .My problem is that sometimes two or more clients are saving the record at the same time. If this is...

table splited into multiple tables, what's the procedure of updating multiple tables in GridView?
hello all, i have a 5-table normalized database, with primary keys and foreign keys ...and when i present the data using GridView, i use a VIEW the collects columns from all tables ...the problem is that, when using GridView & SqlDataSource, the SqlDataSource update command doesn't work because it has to update multiple tables.so i'm asking what's the right procedure when using normalized DB and GridView that make me enable editing and deletion built in GridView's capablities ?  thx all in advance. GLORY~Please mark as answer if this helps u~  use so...

multiple update panel and object datasource insert parameters problem
Hi Guys Newbie using vs2--5, vb.net .net2.0 and ajax with SQL express Ok have a page, the page works fine, accept i added some ajax update panels onto it, this normally isnt a problem but i am also using a freetext box control and when some of my code and validation was firing it was causing an issue if it my controls were all held inside an update panel so i had to create several.  My validation now works fine but when i am trying to use my objectdatasource to add my insert parameters it cannot see the controls as they are now sat in several different update panels, this normally isn...

VS2005 - using detailsview to update, insert and delete rows from SQL 2005 database. Delete and insert work but update does not
  Using VS 2005 DetailsView to insert, delete, and update rows in SQL 2005 database.  insert and delete work but update does not.  I recieve no errors and the detailsView comes back unchanged (as well as table row is unchanged).   I am trying to use as little code behind as possible. However I do have ItemUpdating routines that seem to work (i.e Checking table for new login duplicates and encrypting passwords).  The following is the source code generated by VS2005:<%@ Page Language="VB" AutoEventWireup="false" CodeFile="frmDbRegionMgr...

Insert into multiple tables with multiple records
Hi, I have a sql db that is setup as an equipment booking system (create a booking request for x number of equipment & then the request is transfered to a live booking when equipment is handed to the requester). I am trying to use this syetm to create a front-end in asp.net but i'm finding the whole issue of creating new records in multiple tables a bit of a problem. The basic overview of the DB structure is that each booking can have many equipment items on it (booklings table is 1:many with equipment table). How do i make this work using the ASP.Net controls? Nothing seems t...

Insert/Update trigger question--how to know if insert or update
Is there an easy way to tell in a single trigger for both insert/update whether it was an insert or update. I know how to do this in transact sql (for each statement) , but the for each row thing is messing me up. in transact sql I can just count the number of deleted rows (if 0 then its an insert) but in the sql anywhere syntax its kinda strange-- Thanks Dave Stienessen davids@xata.com Couldn't you check if there's an 'old' row? If there is it's an update, otherwise it's an insert. David. Dave Stienessen wrote: > Is there an easy way to ...

multiple Insert into multiple tables with a stored procedure
Hello I am building a survey application.  I have 8 questions.   Textbox -  Call reference  Dropdownmenu  - choose Support method  Radio button lists - Customer satisfaction questions 1-5 Multiline textbox - other comments. I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID. I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score. Please help me! Thanks Andrew ...

Multiple updates to a single table from multiple clients.........
Hi everyone, I have tried to use multiple clients to update a single table through the same dw. The case is that 2 records in talbe ABC. User 1 & User 2 retrieved data at around the same time. CASE 1 User 1 and User 2 are adding some new records without duplication. There is no errors. CASE 2 If User 1 adding 1, 2, 3 and User adding 2, 4, 6 and User 1 performing update first, when User 2 updates, The error is shown that duplicate record is found. However, for User 2, he doesn't know which record is duplicated. So, he simply ignore the changes. CASE 3 If User ...

SQL Insert to multiple tables
Here is my problem.  I have a relational Access database where I need to insert data to Table 1 (parent) as a new record.  I also need to insert data into Table 2 (child).> I can get the insert to work for the Parent, but a record also needs> to be inserted into the Child.   Below is an example of my tables.  I> get Table 1 to insert no problem.  What I need is table 2 to populate> based on the catID from table 1 which is primarty key and selected> imgID from table 3.  Since the catID is Autonumber in Table 1, I need to pa...

Web resources about - How can i do a multiple insert or multiple updates or inserts and updates to the same table.. - asp.net.sql-datasource

Multiple sequence alignment - Wikipedia, the free encyclopedia
A multiple sequence alignment (MSA) is a sequence alignment of three or more biological sequences , generally protein , DNA , or RNA . In many ...

Facebook Adds Multiple Matching Options for Custom Audiences
Facebook has added real-time matching across multiple data types to its custom audiences ad-targeting tool. The social network said in a Facebook ...

Multiple explosions kill at least seven near Baghdad
Multiple explosions kill at least seven near Baghdad

Meet the man who made "multiple six figures" in 5 days — playing daily fantasy sports
In the world of daily fantasy sports, there are two types of games: cash games and tournaments. Cash games can be head-to-head against one person, ...

Adult film star James Deen responds to multiple assault allegations
View image - gettyimages.com The following story is not for readers under 18 James Deen is probably the most famous adult film star working ...

Multiple Victims In San Bernadino Mass Shooting; 1-3 Shooters Sought
When is it enough to actually do something? Fuck the NRA. Fuck the Republican party and the right wing hate machine. Those Syrian refugees might ...

Instagram update lets you seamlessly manage multiple accounts
Instagram is bringing a great new update to Android and iOS that allows users to seamlessly manage multiple accounts. The change means you’ll ...

The Philadelphia 76ers announced that they suspended Jahlil Okafor—he of the multiple fights outside
The Philadelphia 76ers announced that they suspended Jahlil Okafor—he of the multiple fights outside of bars and one of their only competent ...

Video: Instagram’s New Multiple Account Feature
This morning, we showed you the new Instagram multiple accounts feature that is slowly rolling out on Android , but wanted to follow that up ...

Instagram tests multiple account support on Android
The Instagram team appears to be readying an Android update that will introduce one of its most highly requested features: multiple account support. ...

Resources last updated: 12/3/2015 5:49:24 AM