SQL Server Merge Update Insert Stored PROCEDURE

Here is my Example for single row MERGE / upsert in SQL Server.

This Stored PROCEDURE get input and upsert into the table.

It's faster than do select count(1) before insert/update.

CREATE PROCEDURE [dbo].[PROC_Heartbeat_Save]
	-- Add the parameters for the stored procedure here
	@session_id varchar(50),
	@user_id int,
	@system_id varchar(32)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Saving Session info
    MERGE [dbo].[Heartbeat] AS T
    USING
    (
        SELECT @session_id
           ,@user_id
           ,@system_id
    ) AS S
    (      session_id
           ,user_id
           ,system_id
    )
    ON (T.session_id = S.session_id and T.user_id = S.user_id)

    WHEN MATCHED THEN
        UPDATE SET last_uptime = GETDATE()

    WHEN NOT MATCHED THEN
        INSERT
            ([session_id]
           ,[user_id]
           ,[system_id]
           ,[usb_serial]
           ,[version]
           ,[firmware_slot])
        VALUES
            (@session_id
           ,@user_id
           ,@system_id
           ,@usb_serial
           ,@version
           ,@firmware_slot)
    ;

END

Debian Jessie PHP access SQL Server Database

Use FreeTDS and unixODBC for this task.

1. Installation of the freetds packages with apt-get




apt-get install freetds-common freetds-bin unixodbc php5-sybase
service apache2 restart





2. Add configuration into /etc/freetds/freetds.conf




# Kinamo SQL Server
[test_sqlserver]
  host = milliondollarserver.com
  port = 1433
  tds version = 8.0

Create Oracle Database Link access SQL Server

Just finished a project: customer A use Oracle Database 10G, customer B user SQL Server 2005.
Need read SQL Server from Oracle Database.

Here is how:

Request: Microsoft Windows 32 bit OS
Memory Great than 2 Giga-Byte

1. First Download oracle for windows from otn.oracle.com

http://www.oracle.com/technology/software/products/database/index.html
Oracle Database 10g Release 1 (10.1.0.1.0) for Microsoft Windows

2. Download Oracle Database 10g Release 1 patch 10.1.0.5 for Microsoft Windows
3. Uncompress files
4. Start Installation
5. “Oracle Transparent Gateway for Microsoft SQL Server” has to bee install

(Manual attached: b14270.pdf)

6. Create a Database for convert (SID name = ora)
7. Notice the “Database Character Set” to
8. Create a Listener for connection
9. Setup MS SQL Server (Host Name and Database Name)
10. create a user in SQL Server for Oracle connection (User Name: cyx Password:testora)
11. Create a user in Oracle Server for operation (User Name: cyx Password: cyx)
12. Setup 4 configure files:

tnsnames.ora /listener.ora /inittg4msql.ora /tg4msql_tx.sql
(Files attached)

13. Create Database Link in Oracle:

create database link msql connect to "cyx" identified by "testora" using 'tg4msql';

14. Create Mirror Table in Oracle:

create table t2008_test
as
select *
from t2008_geo@msql;

15. Export Mirror Talbe by using command “exp”:

set ORACLE_SID=ora
exp cyx/cyx file=test.dmp table=t2008_test

PS:For save some memory and 3 services has been set to Manual:
OracleCSService /OracleORACLE_HOMETNSListener /OracleServiceORA
Please start these 3 services before your operation.


For More info and screen shoot please left commit and let me know.