I have two separate SQL Server databases with identical tables and column structures. I am trying to insert new data, including an identity column into db2 from db1 where the identity column doesn't exist.
For what it's worth I am running this query from the source database. Not sure from a best practice approach if it should be ran from the destination server that gets updated or if it matters.
I use the query shown here, which executes but doesn't update anything. Not sure what I am missing in my approach and guidance would be greatly appreciated.
USE dbname
SET IDENTITY_INSERT Client ON;
INSERT INTO Client (ID, DNIS, Toll_Free, Greeting, Voicemail, Client, Group_Number)
SELECT ID, DNIS, Toll_Free, Greeting, Voicemail, Client, Group_Number
FROM [linkedserver].[dbname].dbo.Client
WHERE NOT EXISTS (SELECT ID FROM Client)
1条答案
按热度按时间klr1opcd1#
It looks like your query is almost complete, but there is an error in it which is preventing the data from being inserted. Here is a corrected version of the query:
The problem in your original query was that the subquery in the WHERE clause was checking for the existence of any ID in the destination table, rather than checking for the existence of specific IDs in the source table. The corrected query uses the NOT IN operator to compare the IDs in the source table with the IDs in the destination table.
In terms of best practices, it generally makes sense to run the query from the destination server (i.e. the server that is being updated), as this reduces the risk of accidentally modifying the wrong database. However, if you are confident that you are running the query on the correct server and have verified that the database names and table structures are identical, then it should be safe to run the query from the source server as well.