Product:

Microsoft SQL server 2016

Microsoft Windows 2016

Problem:

How add data from one table to other table?

Suggested solution:

For the example we have downloaded the StackOverflow2013 database from here

https://www.brentozar.com/archive/2021/03/download-the-current-stack-overflow-database-for-free-2021-02/

Create a new table;

CREATE TABLE [dbo].[Staff](
[Id] [int] NOT NULL,
[CreationDate] [datetime] NOT NULL,
[DisplayName] [nvarchar](40) NOT NULL,
[DownVotes] [int] NOT NULL,
[LastAccessDate] [datetime] NOT NULL,
[Location] [nvarchar](100) NULL,
[Reputation] [int] NOT NULL,
[UpVotes] [int] NOT NULL,
[Views] [int] NOT NULL,
[AccountId] [int] NULL,
CONSTRAINT [PK_Staff_Id] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

To be able to copy IDENTITY values between tables you need to use IDENTITY_INSERT and specify the columns in the INSERT INTO statement (not used in our example) .

SET IDENTITY_INSERT dbo.Employee ON

To copy all rows for users from Denmark to the new table;

INSERT INTO dbo.Staff
SELECT
    [Id],
    [CreationDate] ,
    [DisplayName] ,
    [DownVotes] ,
    [LastAccessDate],
    [Location] ,
    [Reputation] ,
    [UpVotes] ,
    [Views] ,
    [AccountId]
FROM windows2016.stackoverflow2013.dbo.users
WHERE location = 'Denmark'

To set the value to zero for column DownVotes when View is less than 100;

UPDATE [StackOverflow2013].[dbo].[Staff]
  SET DownVotes = 0
  WHERE views < 100

Select only one row by use of ID column;

SELECT *
FROM StackOverflow2013.dbo.staff
WHERE id = 19 AND location = 'denmark'

UPDATE StackOverflow2013.dbo.staff
SET DownVotes = 1
WHERE id = 19 AND location = 'denmark'

SELECT 
       [Id]
      ,[CreationDate]
      ,[DisplayName]
      ,[DownVotes]
      ,[LastAccessDate]
      ,[Location]
      ,[Reputation]
      ,[UpVotes]
      ,[Views]
      ,[AccountId]
  FROM [StackOverflow2013].[dbo].[Staff]
  ORDER BY views DESC

More information:

http://www.sql-server-helper.com/error-messages/msg-8101.aspx

https://www.sqlshack.com/how-to-copy-tables-from-one-database-to-another-in-sql-server/

https://www.sqlshack.com/overview-of-the-sql-insert-statement/

https://www.tutorialspoint.com/sql/sql-order-by.htm