Insert rows (if not already exist) to a already existing table (in MS-SQL / T-Sql)

Question

We already have this table (IPConfig) in db. (Sql Server 2k8)

IP         |   Member
-----------------------
10.1.2.100 | Joe
10.2.2.200 | Maley

Now, I should have a query which should do this:

Leave current table intact. If table is not there, create one.
Insert new records (defined in the next version deployment)

I started with this but could not proceed.

    IF (NOT EXISTS (SELECT * 
                     FROM INFORMATION_SCHEMA.TABLES 
                     WHERE TABLE_SCHEMA = 'dbo' 
                     AND  TABLE_NAME = 'IPConfig'))
    BEGIN
      CREATE TABLE dbo.IPConfig (
        IP CHAR(10) PRIMARY KEY NOT NULL,
        Member VARCHAR(32) NOT NULL)
    END

    DECLARE @TempTable Table(               -- Create a temp table.
     IP CHAR(10) PRIMARY KEY NOT NULL,
     Member VARCHAR(32) NOT NULL)

   INSERT INTO @TempTable( 
    IP,
    Member)
    SELECT
      '10.1.2.100',   --Already existing Ip; dont insert
      'Joe'
    UNION SELECT
      '10.2.2.200',   --Already existing Ip; dont insert
      'Maley',
    UNION SELECT
      '10.3.5.200',   --New; Insert.
      'NewUser',

UPDATE TABLE dbo.IPConfig 
/// Here what should be done ? Should I loop through the temp table using triggers or what is the best way ?

A super simple example please.

Sorry if this a naive question; totally new to MSSQL programming.

Are you trying to Merge the @TempTable with the IPConfig table? Insert / Update? — mxix, Sep 30 '14 at 11:08
I want to insert all records in `Temptable` in to `IpConfig` (only if they dont exist already in `IpConfig` table) — now he who must not be named., Sep 30 '14 at 11:12

score 5 · Accepted Answer · answered Sep 30 '14 at 11:08

5

If I understand correctly you only insert new unmatched IPs to dbo.IPConfig. Left outer join is the best choice here. Insert the results to your target table.

INSERT INTO dbo.IPConfig([IP], [Member])
SELECT a.[IP], a.[Member]
FROM @TempTable a
LEFT OUTER JOIN dbo.IPConfig b ON a.[IP]=b.[IP]
WHERE b.[IP] is null

answered Sep 30 '14 at 11:08

bmlsoftware

121
5

so, this will insert all `Ip`s from `Temptable` to `IpConfig` if they wont exist. Great. – now he who must not be named. Sep 30 '14 at 11:11
But a quick question: `How come all rows inserted with out a loop` ? – now he who must not be named. Sep 30 '14 at 11:11
1

SQL supports the insertion of complete select results. You can test this in query analyser with a select the returns multiple rows being inserted to a temp table. – bmlsoftware Sep 30 '14 at 11:18

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

You don't need any temp table, you could create a stored procedure though. The simplest way is described here INSERT VALUES WHERE NOT EXISTS

INSERT  IPConfig (IP, Member) 
SELECT  '10.1.2.100', 'Joe'
WHERE   NOT EXISTS 
        (   SELECT  1
            FROM    IPConfig 
            WHERE   IP = '10.1.2.100'
        );

As stored procedure:

CREATE PROCEDURE InsertIfNotExist 
    @IP nvarchar(50), 
    @Member nvarchar(50) 
AS 

    INSERT  IPConfig (IP, Member) 
    SELECT  @IP, @Member
    WHERE   NOT EXISTS 
            (   SELECT  1
                FROM    IPConfig 
                WHERE   IP = @IP
            );
GO


EXECUTE InsertIfNotExist '10.1.2.100', 'Joe';
EXECUTE InsertIfNotExist '10.2.2.200', 'Maley'

edited May 23 '17 at 12:22

Community

1
1

answered Sep 30 '14 at 11:19

Joseph Jeganathan

234
1
6

1

this will do for one record; for other records ? – now he who must not be named. Sep 30 '14 at 11:20
You must keep repeating that :) As I said you can create a stored procedure with `IP, Member` as parameters. which mean a single line instead of repeating this many times. – Joseph Jeganathan Sep 30 '14 at 11:22

score 0 · Answer 3 · answered May 02 '15 at 04:33

this worked for me

$query_reg="INSERT dbo.Registro (registro,fecha)
            SELECT 'Inscripcion','".$date."'
            WHERE NOT EXISTS (
                              SELECT registro,fecha 
                              FROM dbo.Registro
                              WHERE registro ='Inscripcion' AND
                                       fecha = '".$date."'
                              )";

             $sql_reg = sqlsrv_query($conn,$query_reg);
          if ($sql_reg) // Se eejectuto la sentencia SQL?
              {
              echo "SQLSuccess"; // Mensaje Afirmativo.            
                } else {
          die( print_r( sqlsrv_errors(), true)); // error.        
               }    

             exit();

Insert rows (if not already exist) to a already existing table (in MS-SQL / T-Sql)

3 Answers3