CODEWITHSUNDEEP
sqlstored-proceduresbulkinsert
Nightmare Games
Nightmare Games

Reputation: 2435

SQL: Writing a bulk insert script that ignores existing entries

I'm starting with an existing program that uses an sql database and trying to modify it so that it uses a bulk insert (rather than one-by-one) and also prevents repeat entries.

Here's what I've got for my function to add one user (as long as that user doesn't already exist), which should be working:

use [DebugDatabase]
go

set ansi_nulls on
go

set quoted_identifier on
go

create procedure [dbo].[AddUser]
    @Id bigint, @Login nvarchar(100), @ConsoleName nvarchar(100), @Ip nvarchar(50)
as
begin
    set nocount on;

    declare @FoundId bigint = (select Id from [User] where Id = @Id)

    if @FoundId is null
        begin
            insert into [User] (Id, Login, ConsoleName, Ip) values (@Id, @Login, @ConsoleName, @Ip)
        end
    else
        begin
            update [User]
            set [Id] = @Id,
                [Login] = @Login,
                [ConsoleName] = @ConsoleName,
                [Ip] = @Ip
            where Id = @Id
        end
    select top 1 * from [User] where Id = @Id;
end
go

Now I'd like to write a function for the bulk-insertion, which calls the above function to check each entry. The way I was shown, this requires a custom table type:

use [DebugDatabase]
go

create type [dbo].[UserTableType] as table
    (
    [Id] [bigint] not null,
    [Login] [nvarchar](100) not null default 'Unknown',
    [ConsoleName] [nvarchar](100) not null default 'Unknown',
    [Ip] [nvarchar](50) not null default '0.0.0.0'
    )
go

And the function to add multiple entries (this is the one I'm having trouble with):

use [DebugDatabase]
go

set ansi_nulls on
go

set quoted_identifier on
go

create procedure [AddMultipleUsers]
    @users UserTableType readonly

as

declare
    @Id bigint,
    @Login nvarchar(100),
    @ConsoleName nvarchar(100),
    @Ip nvarchar(50)

begin
    insert into @Id select Id from @users
    insert into @Login select Login from @users
    insert into @ConsoleName select ConsoleName from @users
    insert into @Ip select Ip from @users

    exec AddUser @Id, @Login, @ConsoleName, @Ip
end
go

I'm getting "Must declare the table variable "@Id". I'm not sure how to extract the individual values from the table type in order to pass them over to the AddUser function. What I'd REALLY love, though, is some way to do it all in one function call, but I haven't come across anything like that yet.

Upvotes: 0

Views: 123

Answers (2)

user4313720
user4313720

Reputation: 1

create procedure [AddMultipleUsers]
  @users UserTableType readonly

as

declare
  @Id bigint,
  @Login nvarchar(100),
  @ConsoleName nvarchar(100),
  @Ip nvarchar(50)

--  A opcion ... 
-- Declare the cursor for the list of users to be processed.
DECLARE userT CURSOR FOR SELECT * FROM UserTableType ;

-- Open the cursor.
OPEN userT;

-- Loop through the users.
FETCH NEXT
  FROM userT
  INTO @Id, @Login, @ConsoleName, @Ip;

WHILE @@FETCH_STATUS = 0
BEGIN;
  exec AddUser @Id, @Login, @ConsoleName, @Ip

  FETCH NEXT FROM userT INTO @Id, @Login, @ConsoleName, @Ip;
END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

Upvotes: 0

cha
cha

Reputation: 10411

This type of operation can be done using a single query WITHOUT the use of a stored procedure. (BTW, please use the correct terminology: your AddUser is a stored procedure, not a function). Please have a look at the MERGE command

MERGE [User] AS target
    USING (SELECT [Id], [Login], [ConsoleName], [Ip] FROM @users) AS source
    (Id, Login, ConsoleName, Ip)
    ON (target.Id = source.Id)
WHEN MATCHED THEN 
        UPDATE SET [Login] = source.Login,
                   [ConsoleName] = source.ConsoleName,
                   [Ip] = source.Ip
WHEN NOT MATCHED THEN
    INSERT (Id, Login, ConsoleName, Ip)
    VALUES (source.Id, source.Login, source.ConsoleName, source.Ip);

Upvotes: 3

Related Questions