SQL statements – INSERT

How to INSERT data?

How to insert by EXECUTE sql string?

• Example

  [sql]
  DECLARE @strSQL varchar(max)
  SET @strSQL = ‘SELECT * FROM <one_table>’

  INSERT INTO <table_name>
  EXECUTE (@strSQL)
  [/sql]

How to insert a row with identity key value?

• SQL server

  [sql]
  SET IDENTITY_INSERT [dbo].[table_name] ON
  INSERT___
  SET IDENTITY_INSERT [dbo].[table_name] OFF
  [/sql]

How to insert a split string into a table?

• SQL Server

  [sql]
  — Example
  declare @example_string varchar(max) = ‘123456,789012,345678’
  SELECT Split.a.value(‘.’, ‘VARCHAR(100)’) AS items
  FROM
  (
  SELECT CAST (‘<M>’ + REPLACE(@example_string, ‘,’, ‘</M><M>’) + ‘</M>’ AS XML) AS String
  ) AS A
  CROSS APPLY String.nodes(‘/M’) AS Split (a);

  –Result
  –items
  –123456
  –789012
  –345678
  [/sql]

How to UPDATE record?

How to UPDATE with SELECT statement?

• SQL Server

  [sql]UPDATE books
  SET books.primary_author = authors.name
  FROM books
  INNER JOIN authors ON books.author_id = authors.id
  WHERE books.title = ‘The Hobbit'[/sql]

Different UPDATE queries example

[sql]
UPDATE table_name
SET desc = (select top 1 equipment.desc from EQUIPMENT where upper(rtrim(ltrim(descr))) = upper(rtrim(ltrim(t.descr))))
from table_name t
WHERE desc IS null
[/sql]

[sql]
UPDATE table_name
SET table_name.desc = EQUIPMENT.desc
FROM table_name, EQUIPMENT
WHERE table_name.id = EQUIPMENT.id and table_name.desc IS null
[/sql]

[sql]
UPDATE table_name
SET descr = case when descr =’aaa’ then ‘AAA’
when descr =’bbb’ then ‘BBB’
when descr =’cde’ then ‘EDC’ end
where descr in (‘aaa’,’bbb’,’cde’)
[/sql]

How to copy records to different DB?

How to copy record including primary key?

• SQL Server

  [sql]
  — Remember to run this on destination DB
  SET IDENTITY_INSERT [DB_To].[dbo].[destination_table] ON
  INSERT [DB_To].[dbo].[destination_table]
  SELECT * FROM [DB_From].[dbo].[source_table] WHERE column1 = ‘ABCD’
  SET IDENTITY_INSERT [DB_To].[dbo].[destination_table] OFF
  [/sql]

How to copy record ignoring a primary key?

• SQL Server

  [sql]
  — Instead of SELECT *, spell out the columns that you want
  INSERT INTO Data (time, serial_number…)
  SELECT (time, serial_number…) FROM Temp_data;
  [/sql]