How to insert text with single quotation sql server 2005

I want to insert text with single quote Eg john's to table in sql server 2005 database

From stackoverflow

• 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.

  solairaja : how it can be possible when the value comes from a variable

  Kirtan : @solairaja - @somevar = 'Test''s'

• 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.

  Brannon : Can you give more detail on your query? Parameters will work in nearly all queries. I'm curious why they won't work in your case.

• but adding another single quote with existing one will create problem while saving the data into another table eg.:

  insert into table1(select * from table2)