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')