Combining SQL Statements

[stanlyn]

Hi,

I need help with a SQL statement that combines a select and an insert or update statement all in one swoop. I see others that breaks it into 3 statements as in 1. select(to find), 2. delete, and 3. re-insert. I personally want to do only whats needed. Here is the flow:

1. select statement that will determine if the record exists,
2. if exists, then do a SQL update,
3. if not exists, do an insert,

Thats it...

I will load all the values up front while constructing the statement, so that it has all the values it needs whether its inserting or updating.

I'm working in Visual Foxpro, and while looping thru its records i'm creating a sql script that will be passed to sql server one at a time. Its that the script must contain everything that sql will need before allowing VFP to move to the next record...

Thanks, Stanley

 

[gmmastros]

SQL Server has a merge statement that does what you want. Personally, I have never used a merge statement. You can find the documentation on it here:

http://technet.microsoft.com/en-us/library/bb510625.aspx

Normally, in my own code, I would write a stored procedure to handle a situation like this. Your front end application (VFP app) would call the stored procedure and this stored procedure would be responsible for saving the data.

For example:

Create Procedure SavePerson
  @Name VarChar(50),
  @ShoeSize Decimal(3,1),
  @EyeColor VarChar(20)
AS
SET NOCOUNT ON

If Exists(Select 1 From People Where Name = @Name)
  Begin
    Update People
    Set    ShoeSize = @ShoeSize,
           EyeColor = @EyeColor
    Where  Name = @Name
  End
Else
  Begin
    Insert
    Into   People(Name, ShoeSize, EyeColor)
    Values (@Name, @ShoeSize, @EyeColor)
  End

Once you create the stored procedure using SQL Server Management Studio, you simply call it from your VFP application.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

Stanley,
Better ask in VFP forum.
There is better way to do this not with looping thru table records. but update all at once.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[stanlyn]

Ok, I discovered the @@ROWCOUNT command and it looks like this only requires one pass. Here is what I've tried so far...

UPDATE [database].[Assets].[People]
SET
[name]='Alice'
WHERE
[key]='999999'
IF @@ROWCOUNT=0
INSERT INTO [database].[Assets].[people]
(
key,
name,
age
)
VALUES
(
'999999',
'John',
36
)

The stored procedure way, or the batch way would not really work here as I am also archiving the script as pure sql to serve as a backup while the syncing of vfp data to sql was happening.

So, while iterating thru the vfp records that is marked as changed (dirty bit), i need to create the sql script that would do the inserting or updating on the sql side along with all the data. And in a single pass to sql, it would update or insert the data, while vfp is saving the entire batch as a single script file that can run standalone in sql anytime later. The script file is named after a datetime construct.

With this in mind, i had to remove all variables from the script... Hope this explains it better, as i know these details was not disclosed earlier...

Thanks, Stanley

 

[markros]

If you're using SQL Server 2008 and up, then I suggest to use MERGE and not 2 statements. See this blog post by Alex Kuznetsov

http://sqlblog.com/blogs/alexander_...02-patterns-that-do-not-work-as-expected.aspx

PluralSight Learning Library

 

[stanlyn]

Hi markros,

I read the article and the conclusion from the author and all the feedback comments was the any of the 3 ways was un-reliable if not properly applied.

What do you take away from all the comments?

Thanks, Stanley

 

[markros]

I took it as MERGE has much less problems that the common patterns used and therefore if there is a choice between what to use, we should favor MERGE.

Also, in the comments (close to the bottom) was a way to properly apply hints and even that pattern will work correctly.

PluralSight Learning Library

 

[stanlyn]

For me the merge function as it was presented in the document looks overly complicated. But if its the best way, then I can adapt, but I'm not sure if one of the older ways properly hinted would be easier and just as effective, as soon as you see the older ways in code, you know whats its doing, which was not the case with merge.

Just my 2c. Stanley

 

[markros]

IMHO, with merge (and with inline comments as needed) it's as easy as 2 statements.
WHEN MATCHED THEN ...
WHEN NON MATCHED THEN ...

Although I do look up help every time as I haven't remembered the syntax by heart yet.

PluralSight Learning Library