Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jharris3434
Starting Member
2 Posts |
 Posted - 2008-12-18 : 16:53:13
|
| I'm working from VB6 using ADO. Working with SQL Server 2008, native client connector.My app will connect over a VPN connection over the internet. I want to be as efficient as possible, and I'm not sure how to do this:I have a few tables:[Customers] have multiple[Leads] have multiple[UPs]all related by identity surrogate key fields (CustomerID, LeadID, UPID, etc)(UPs are the individual email requests for information, Leads are defined as all UPs received within 30 days of first new UP)When I receive an email, I scrape it for data. My task, which I want to accomplish in as few trips to the server as possible, becomes as follows:1) Check for matching previous Customer/Lead. This will be accomplished with a SELECT query I'm working on.2) If previous Customer found, get identity field, else Insert new Customer and get identity field3) If previous Lead found, get identity field, else Insert new Lead and get identity field4) Insert new UP and get identity fieldNote: My application needs to have these identity fields, so they need to be returned.I would just really appreciate some help with how I can approach this, as this is my first experience working with SQL Server. Thanks. |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-19 : 09:31:16
|
| You can do this with one trip to database if you keep all your logic inside a stored procedure which has in and out parameters. Here is a partial example:Create Procedure test (@CustID varchar(50), @LeadID varchar(50),@newCustID varchar(50) output,@newLeadID varchar(50) output)As declare @CustID varchar(50)set @CustID = (Select ID from Customers where CustomerId =@CustID )If @CustID Is not nullBegin----ELSEDECLARE @TmpTable TABLE (ID INT)INSERT INTO Customers (CustomerID)OUTPUT INSERTED.id into @TmpTable VALUES ( @CustID)set @newCustID = (select ID from @TmpTable )End |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|
|
Grouping statements to optimize over network