User Tools

Site Tools


notes:sql:insert

INSERT statement

Insert a row with default values:

INSERT INTO Book DEFAULT VALUES

INSERT INTO … SELECT … FROM

-- Example #1
-- Copy records to a table BookCopy from a Book table.
CREATE TABLE BookCopy(BookIsbn CHAR(13))
GO
INSERT INTO BookCopy(BookIsbn) SELECT Book13 FROM Book
GO
 
-- Example #2
-- Insert records to a temp table. The temp table has to be created first.
CREATE TABLE #customers (
    CustomerId UNIQUEIDENTIFIER NOT NULL,
    CustomerName NVARCHAR(100) NOT NULL,
    CustomerCode NVARCHAR(20) NULL,
    CustomerType INT)
 
INSERT INTO #customers
    SELECT CustomerId, CustomerName
    FROM  Customer
    WHERE CustomerCode = COALESCE(@CustomerCode, CustomerCode)
    AND (CustomerType=1 OR CustomerType=5)
    ORDER BY CustomerName
 
-- Example #3
CREATE TABLE #item (
   ItemId UNIQUEIDENTIFIER,
   Price MONEY)
 
INSERT INTO #item SELECT CustomerId, SUM(ISNULL(Price,0))
FROM Item
WHERE ItemType=0
GROUP BY CustomerId

More complicated examples:

-- Example #1
INSERT INTO #output
SELECT CustomerId, CustomerName
    ,ISNULL(LTRIM(CityId),'00000000-0000-0000-0000-000000000000') + 
     ISNULL(LTRIM(CountryId),'00000000-0000-0000-0000-000000000000')
    ,ISNULL(CityName,'') + ISNULL('/'+CountryName,'')
    ,dbo.fun_GetDate(1,1,YEAR(DateCreated))
    ,COUNT(DISTINCT PartyId) 
FROM #records WHERE CityId IS NOT NULL OR CommunityId IS NOT NULL
GROUP BY CustomerId, CustomerName, CityId, CountryId, CityName, CountryName, YEAR(DateCreated)
 
-- Example #2
INSERT INTO #output
SELECT DISTINCT CustomerId, CustomerName, TypeCode, TypeCodeName
    -- Top DateCreated
    ,(SELECT TOP 1 DateCreated FROM #rec rec2
        WHERE rec1.PartyId=rec2.PartyId
        AND rec1.CustomerId=rec2.CustomerId
        AND rec1.TypeCode=rec2.TypeCode
        ORDER BY DateCreated) AS TopDateCreated
    -- ActivityId corresponding to the top DateCreated
    ,(SELECT TOP 1 ActivityId FROM #rec rec2
        WHERE rec1.PartyId=rec2.PartyId
        AND rec1.CustomerId=rec2.CustomerId
        AND rec1.TypeCode=rec2.TypeCode
        ORDER BY DateCreated) AS ActivityId
FROM #records rec1

INSERT INTO and dynamic SQL:

-- Example #1
-- INSERT INTO ... SELECT * FROM (SELECT * FROM ... ) Temp
DECLARE @SQL VARCHAR(1000)
SET @SQL = 'INSERT INTO #customer(CutomerName,Address,City,PostalCode,CustomerType) ' +
'SELECT CutomerName,Address,City,PostalCode,CustomerType ' +
'FROM (SELECT CutomerName,Address,City,PostalCode,CustomerType FROM ' + @TableName + ') Temp'
EXEC(@SQL)
 
-- Example #2
DECLARE @SQL VARCHAR(1000)
CREATE TABLE #customer (CustomerId UNIQUEIDENTIFIER, CustomerName NVARCHAR(200))
SET @SQL = 'INSERT INTO #customer SELECT CustomerId, CustomerName FROM (' + @TableName + ') AS Temp'
EXEC(@SQL)
 
-- Example #3: Grab the structure of the Customer table, create a temporary table, and insert records.
DECLARE @SQL VARCHAR(1000)
SELECT TOP 1 * INTO #customers FROM Customer
DELETE FROM #customers
SET @SQL = 'INSERT INTO #customers SELECT * FROM (' + @TableName + ') AS Temp'
EXEC(@SQL)

Generate INSERT INTO statements using a cursor:

-- Create a test table with some data
CREATE TABLE #test (ID INT, [Name] VARCHAR(20))
INSERT INTO #test VALUES (2, 'Abu')
INSERT INTO #test VALUES (45, 'Bulo')
INSERT INTO #test VALUES (32, 'Maka')
 
-- Declare variables and a cursor
DECLARE @ID INT, @Name VARCHAR(20)
DECLARE cur CURSOR FOR SELECT ID, [Name] FROM #test ORDER BY ID
 
-- Open the cursor
OPEN cur FETCH NEXT FROM cur INTO @ID, @Name
 
-- Iterate over all records
WHILE @@FETCH_STATUS=0
BEGIN
    PRINT 'INSERT INTO #test (ID, [Name]) VALUES '+
          '(' + CAST(@ID AS VARCHAR) + ', ''' + @Name + ''')'
 
    FETCH NEXT FROM cur INTO @ID, @Name
END
 
-- Dispose the cursor
CLOSE cur
DEALLOCATE cur
 
-- Drop the test table
DROP TABLE #test
 
-- Results:
INSERT INTO #test (ID, [Name]) VALUES (2, 'Abu')
INSERT INTO #test (ID, [Name]) VALUES (32, 'Maka')
INSERT INTO #test (ID, [Name]) VALUES (45, 'Bulo')

INSERT INTO … FROM Microsoft Access Database:

INSERT INTO Customer
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'C:\Temp\Company.mdb';'User';'Password',
                'SELECT * FROM Customer')
notes/sql/insert.txt · Last modified: 2015/06/24 by admin