CODEWITHSUNDEEP
c#sqlsql-serverdatabase-design
Simon Kiely
Simon Kiely

Reputation: 6070

INSERT INTO if not exists SQL server

I have a database structured as follows:

users

userid (Primary Key)
username

group

groupid (PK)
groupName

user_groups

userid (Foreign Key)
groupid (Foreign Key)

The first time a user logs in I would like their info to be added to the users table. So essentially the logic I would like to have if

if (//users table does not contain username)
{
INSERT INTO users VALUES (username);
}

How can I do this intelligently using SQL Server/C# ?

Upvotes: 14

Views: 50822

Answers (8)

Jason
Jason

Reputation: 3960

I would first create a stored proc on the db to do the check and insert if necessary:

CREATE PROCEDURE AddNewUserProc
(
@username       VarChar(50) -- replace with your datatype/size
)

AS

    IF NOT EXISTS (SELECT * FROM users WHERE username = @username)
    BEGIN
        INSERT INTO users
        VALUES (@username)
    END

Then a method on the app that will call this procedure

public void AddNewUserMethod(string userName)
{
    SqlConnection connection = new SqlConnection("connection string");
    SqlCommand command = new SqlCommand("AddNewUserProc", connection);

    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("username", SqlDbType.VarChar, 50).Value = userName;

    try
    {
        connection.Open();
        command.ExecuteNonQuery();
    }
    finally
    {
        if (connection.State == ConnectionState.Open) { connection.Close(); }
    }
}

Note leaving this as alternative/historical, but for purpose of correctness the correct way is using the Merge statement, see answer https://stackoverflow.com/a/9649040/167304 or checkout MS doc https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15

Upvotes: 4

levkaster
levkaster

Reputation: 2730

There is a simple solution! I Found it in this post!

INSERT INTO users (Username) 
SELECT ('username')
WHERE NOT EXISTS(SELECT * FROM users WHERE Username= 'username')

In my project I needed to do it with 2 values. So my code was:

INSERT INTO MyTable (ColName1, ColName2) 
SELECT 'Value1','Value2' 
WHERE NOT EXISTS
(SELECT * FROM MyTable WHERE ColName1 = 'Value1' AND ColName2= 'Value2')

Hope this helps!

Upvotes: 2

user194076
user194076

Reputation: 9027

Basically you can do it like this: 

IF NOT EXISTS (SELECT * FROM USER WHERE username = @username)
    INSERT INTO users (username) VALUES (@username)

But seriously, how you're going to know if user visited your website for the first time? You have to insert records in table user, when somebody register on your website, not login.

Upvotes: 12

Steam
Steam

Reputation: 9866

@gbn answer needs SQL server 2008 or higher. I tried a non-merge way to do it. Ugly perhaps, but it works. 

declare @user varchar(50)
set @user = 'username'
insert into users (username) 
select @user
where not exists (
 select username 
 from users 
 where username = @user
);

If you want to test any of the answers, here is the SQL for the table - 

CREATE TABLE [dbo].[users](
    [userid] [int] NULL,
    [username] [varchar](50) NULL
)

INSERT [dbo].[users] ([userid], [username]) VALUES (1, N'John')
INSERT [dbo].[users] ([userid], [username]) VALUES (2, N'David')
INSERT [dbo].[users] ([userid], [username]) VALUES (3, N'Stacy')
INSERT [dbo].[users] ([userid], [username]) VALUES (4, N'Arnold')
INSERT [dbo].[users] ([userid], [username]) VALUES (5, N'Karen')

Upvotes: 1

Amen Ayach
Amen Ayach

Reputation: 4348

The following code is a method that returns 0 if user already exists and returns the new user ID that just added :

  private int TryToAddUser(string UserName)
        {
            int res = 0;
            try
            {
                string sQuery = " IF NOT EXISTS (select * from users where username = @username) \n\r" + 
                " BEGIN \n\r" + 
                "     INSERT INTO users values (@username) \n\r" + 
                " SELECT SCOPE_IDENTITY() \n\r " + 
                " END \n\r " + 
                " ELSE SELECT 0";
                using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
                {
                    cmd.CommandText = sQuery;
                    cmd.Parameters.AddWithValue("@username",UserName);
                    cmd.Connection = new System.Data.SqlClient.SqlConnection("SomeSqlConnString");
                    cmd.Connection.Open();
                    res = (int)cmd.ExecuteScalar();
                    cmd.Connection.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return res;
        }

Upvotes: 1

ron tornambe
ron tornambe

Reputation: 10780

Here is a (probably oversimplified) example that addresses your issue. Note that it is always best to use parameters to prevent injection attacks, especially when verifying users.

CREATE PROCEDURE AddUser
  @username varchar(20)
AS
  IF NOT EXISTS(SELECT username FROM users WHERE username=@username)
    INSERT INTO users(username) VALUES (@username)

Upvotes: 0

user330315
user330315

Reputation:

Or using the new MERGE syntax:

merge into users u
using ( 
   select 'username' as uname
) t on t.uname = u.username
when not matched then 
  insert (username) values (t.uname);

Upvotes: 21

Sascha
Sascha

Reputation: 10347

IF NOT EXISTS (select * from users where username = 'username')
BEGIN
    INSERT INTO ...
END

Upvotes: 4

Related Questions