SQL Server stored procedure - check if ANY records exist, and insert if not

Question

Question:

Is it possible to send a single call to SQL Server 2012, with an array of specified of some time, so that the database server can check if any of those elements exist, and if not, insert them into the table?

Problem:

The problem I am facing, is that I have a scheduled job (calling an API), that returns about 400 records each time it is called. This job is called every minute, and the slowest thing, is that for each result, I am checking to see if the Database already has the record, and if it doesn't then I am doing an INSERT.

Example: API returns 400 people. I want to be able to send the ID of each person, as well as Name, etc. in a single call to the DB. The DB should then check and see if the ID exists (for each Person), and if not, do an INSERT.

Feedback:

I would also like advice on whether or not this is actually good practice, or if there is a better way of handling this.

Is it possible that many of these calls will be hitting the db simultaneously? If not then you should load them into a staging table and run some kind `MERGE`. If you _will_ have multiple calls (meaning you can't use a single table as they'll overwrite each other) then look into "passing arrays to stored procedures" - http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure — Nick.Mc, Nov 03 '15 at 02:18

score 3 · Accepted Answer · answered Nov 03 '15 at 02:59

As per my understanding, the job return the result to an application (C#), then this application is calling the DB for each record.

The Answer is yes, you can send multiple records (datatable) to the sql server in one patch then the sql server will process all these data.

First in SQL Server, you have to define a table datatype of the format which will be sent from the application to the DB.

CREATE TYPE YourDataType AS TABLE(
    [Col1] [int] NULL,              [Col2] [int] NULL,                  [Col3] [int] NULL,              [Col4] [nvarchar](255) NULL,
    [Col5] [nvarchar](255) NULL,    [Col6] [nvarchar](255) NULL,        [Col7] [nvarchar](255) NULL,    [Col8] [nvarchar](255) NULL,
    [Col9] [nvarchar](255) NULL,    [Col10] [nvarchar](255) NULL,       [Col11] [int] NULL,             [Col12] [nvarchar](255) NULL,
)
GO

Then create your procedure which will use this datatype

CREATE PROCEDURE YourProcedureName (@TableVariable YourDataType READONLY, @ScalarParameter nvarchar(255))
AS

BEGIN
INSERT INTO YourTable WITH (ROWLOCK) (Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12 ) 

    SELECT  Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12 

    FROM @TableVariable t
        /*Left Join then where ID is null to make sure the record doesn't exists*/
        LEFT JOIN YourTable PR WITH (NOLOCK) 
                                ON  PR.ID = @ScalarParameter
                                AND PR.Col1 = t.Col1    
                                AND PR.Col2 = t.Col2
                                ......
        WHERE PR.ID IS NULL
END

Finally call this procedure from your application using your data. Here i wrote it using VB.Net but you can rewrite it in C#:

    Dim connectionString As StringBuilder = New StringBuilder()
    connectionString.AppendFormat("Server={0};", ????)
    connectionString.AppendFormat("Database={0};", ???)
    connectionString.AppendFormat("User ID={0};", ????)
    connectionString.AppendFormat("Password={0}", ????)

    Dim InputTable As DataTable = New DataTable("YourDataType")
    InputTable = ds.Tables(0).Copy() 
    InputTable.TableName = "YourDataType"

    Try
        Using conn As New SqlClient.SqlConnection(connectionString.ToString())
            Using comm As New SqlClient.SqlCommand()
                Dim insertedRecords As Int32

                comm.Connection = conn
                comm.CommandText = "YourProcedureName"
                comm.CommandType = CommandType.StoredProcedure
                Dim TableVariable As SqlClient.SqlParameter = comm.Parameters.Add("@TableVariable", SqlDbType.Structured)
                TableVariable.Direction = ParameterDirection.Input
                TableVariable.Value = InputTable
                Dim ScalarVariable As SqlClient.SqlParameter = comm.Parameters.Add(@ScalarParameter, SqlDbType.VarChar)
                ScalarVariable.Direction = ParameterDirection.Input
                ScalarVariable.Value = ???

                conn.Open()
                insertedRecords = comm.ExecuteNonQuery()
                If (insertedRecords > 0) Then
                    _Changed = True
                End If
                conn.Close()

                comm.Dispose()
                conn.Dispose()
            End Using
        End Using
    Catch ex As Exception
        Return False
    End Try
    Return True

On creating the procedure, SQL Management Studio has on error on the first line, that states "CREATE PROCEDURE must be the only statement in the batch". Is there a particular order this has to be done in? — blgrnboy, Nov 03 '15 at 06:31

score 2 · Answer 2 · answered Nov 03 '15 at 02:17

2

You need a way to determine whether or not the record is already in the table. This requires some sort of comparison.

Once you have that, you can construct a query like this:

insert into t( . . .)
    select v.*
    from values ((v1), (v2), . . .) as v(vcol)
    where not exists (select 1 from v where v.vcol = t.<whatever>);

answered Nov 03 '15 at 02:17

Gordon Linoff

1,242,037
58
646
786

May be merge will be more expressive and uses keys if exists `MERGE INTO test AS trg USING (VALUES ( @v1),(@v2),(@v3)) AS src (Newt) ON trg.t = src.Newt WHEN NOT MATCHED THEN INSERT (t) VALUES (NewT);` – vitalygolub Nov 03 '15 at 10:46
@vitalygolub . . . It is probably me, but I almost never find `merge` easier to read. – Gordon Linoff Nov 03 '15 at 19:42

SQL Server stored procedure - check if ANY records exist, and insert if not

2 Answers2