There are not many real life databases that do not import data from external sources or do not export data for delivery to external targets. I am going to devote a series of talks on different ways to perform these functions.<\/p>\n
The INSERT<\/a> statement is the easiest way to load small sets of static information into a database. We will be working again with the Boy Scouts of America (BSA) hypothetical database.<\/p>\n The first example loads the rank table with a data. We can see the auto-increment surrogote key is incremented.<\/p>\n On interesting thing about an identity <\/a>column is that seed value is not reset with the DELETE<\/a> statement. The example below shows the second set of inserts are given values of 11 to 20.<\/p>\n On the other hand, the TRUNCATE TABLE<\/a> statement does reset the seed value of the table. The example below shows the third set of inserts which are given values 1 to 10.<\/p>\n Importing data by using INSERT statements is a good technique for small sets of static data. The sqlcmd utility<\/a> can be used to automate the execution of these statements from a batch program. <\/p>\n-- Use new rowset notation\r\nINSERT INTO [RECENT].[TBL_RANK] ([RANK_DESC]) VALUES\r\n('Scout'), \r\n('Tenderfoot'),\r\n('Second Class'),\r\n('First Class'),\r\n('Star'),\r\n('Life'),\r\n('Eagle'),\r\n('Bronze Palms'),\r\n('Gold Palms'),\r\n('Silver Palms');\r\nGO\r\n\r\n-- Show the rows \r\nSELECT RANK_ID, RANK_DESC FROM [RECENT].[TBL_RANK];\r\nGO<\/span><\/pre>\n<\/p>\n-- Delete does not reset the identity column\r\nDELETE FROM [RECENT].[TBL_RANK];\r\n\r\n-- Use new rowset notation\r\nINSERT INTO [RECENT].[TBL_RANK] ([RANK_DESC]) VALUES\r\n('Scout'), \r\n('Tenderfoot'),\r\n('Second Class'),\r\n('First Class'),\r\n('Star'),\r\n('Life'),\r\n('Eagle'),\r\n('Bronze Palms'),\r\n('Gold Palms'),\r\n('Silver Palms');\r\nGO\r\n\r\n-- Show the rows \r\nSELECT RANK_ID, RANK_DESC FROM [RECENT].[TBL_RANK];\r\nGO<\/span><\/pre>\n<\/p>\n-- Truncate resets the identity column\r\nTRUNCATE TABLE [RECENT].[TBL_RANK];\r\n\r\n-- Use new rowset notation\r\nINSERT INTO [RECENT].[TBL_RANK] ([RANK_DESC]) VALUES\r\n('Scout'), \r\n('Tenderfoot'),\r\n('Second Class'),\r\n('First Class'),\r\n('Star'),\r\n('Life'),\r\n('Eagle'),\r\n('Bronze Palms'),\r\n('Gold Palms'),\r\n('Silver Palms');\r\nGO\r\n\r\n-- Show the rows \r\nSELECT RANK_ID, RANK_DESC FROM [RECENT].[TBL_RANK];\r\nGO<\/span><\/pre>\n<\/p>\n