How to insert text with single quotation sql server 2005

Question

Escape single quote with an additional single as Kirtan pointed out
And if you are trying to execute a dynamic sql (which is not a good idea in the first place) via sp_executesql then the below code would work for you

sp_executesql N'INSERT INTO SomeTable (SomeColumn) VALUES (''John''''s'')'

The answer really depends on how you are doing the INSERT.

If you are specifying a SQL literal then you need to use the double-tick approach:

-- Direct insert
INSERT INTO Table1 (Column1) VALUES ('John''s')

-- Using a parameter, with a direct insert
DECLARE @Value varchar(50)
SET @Value = 'John''s'
INSERT INTO Table1 (Column1) VALUES (@Value)

-- Using a parameter, with dynamic SQL
DECLARE @Value varchar(50)
SET @Value = 'John''s'
EXEC sp_executesql 'INSERT INTO Table1 (Column1) VALUES (@p1)', '@p1 varchar(50)', @Value

If you are doing the INSERT from code, use parameters:

// Sample ADO.NET
using (SqlConnection conn = new SqlConnection(connectionString)) {
    conn.Open();
    using (SqlCommand command = conn.CreateCommand()) {
        command.CommandText = "INSERT INTO Table1 (Column1) VALUES (@Value)";

        command.Parameters.AddWithValue("@Value", "John's");

        command.ExecuteNonQuery();
    }
}

If your data contains user-input, direct or indirect, USE PARAMETERS. Parameters protect against SQL Injection attacks. Never ever build up dynamic SQL with user-input.

This worked for me:

  INSERT INTO [TABLE]
  VALUES ('text','''test.com''', 1)

Basically, you take the single quote you want to insert and replace it with two. So if you want to insert a string of text ('text') and add single quotes around it, it would be ('''text'''). Hope this helps.

INSERT INTO Table1 (Column1) VALUES ('John''s')

Or you can use a stored procedure and pass the parameter as -

usp_Proc1 @Column1 = 'John''s'

If you are using an INSERT query and not a stored procedure, you'll have to escape the quote with two quotes, else its OK if you don't do it.