How to Copy Data from One Table to Another

A quick way to populate data from one table to another using Transact-SQL. The objective is to create a copy of the table and populating it at the same time and then create an SQL script to refresh the data when necessary. This can be achieved by using two similar SQL (three if we want to include the TRUNCATE command).

The first run we use “SELECT INTO” statement to query the source table and create a new table and populate it at the same time.

SELECT [ID]
      ,[System Manufacturer]
      ,[Computer Type]
      ,[Computer Model]
      ,[Name]
      ,[Serial Number]
      ,[Image]
      ,[OS]
      ,[OS Revision]
      ,[Architecture]
      ,[Primary User]
      ,[Domain]
      ,[Last Logon User]
      ,[Last Logon Domain]
      ,[Client Date] 
INTO [dbo].[DiscoveryAgentCopy] 
FROM [dbo].[DiscoveryAgent]
GO

You can use * if you want to copy all fields from the table.

SELECT * INTO [dbo].[DiscoveryAgentCopy] 
FROM [dbo].[DiscoveryAgent]
GO

If the copy table is already existed and we want to refresh only the data, we use “INSERT SELECT” statement to read the data from source and map them to the fields on destination table.

-- CLEAR COPY TABLE OF ALL DATA
TRUNCATE TABLE [dbo].[DiscoveryAgentCopy]
GO

-- POPULATE DATA
INSERT INTO [dbo].[DiscoveryAgentCopy] (
       [ID]
      ,[System Manufacturer]
      ,[Computer Type]
      ,[Computer Model]
      ,[Name]
      ,[Serial Number]
      ,[Image]
      ,[OS]
      ,[OS Revision]
      ,[Architecture]
      ,[Primary User]
      ,[Domain]
      ,[Last Logon User]
      ,[Last Logon Domain]
      ,[Client Date])
SELECT [ID]
      ,[System Manufacturer]
      ,[Computer Type]
      ,[Computer Model]
      ,[Name]
      ,[Serial Number]
      ,[Image]
      ,[OS]
      ,[OS Revision]
      ,[Architecture]
      ,[Primary User]
      ,[Domain]
      ,[Last Logon User]
      ,[Last Logon Domain]
      ,[Client Date]
FROM [dbo].[DiscoveryAgent]
GO