but one table or both tables contain some records that the other is missing. To consolidate the tables, you must first determine which records are unique to one table or to the other table.
Use dynamic SQL and populate a variable with all the column names that exist in both tables, then do the same union query. If you then absolutely must, outer join by ID to get the extra non-shared columns. P.S. use information_schema.columns, not the system tables, as the schema view...
select * from View_BookInfoList where BookInfoID not in (select BookInventoryInfoID from InventoryBookList where BookInventoryPlanId=2) -- TEMPLATE - SQL Server T-SQL compare two tables SELECT Label='Found IN BookInfoList, NOT IN InventoryBookList',* FROM (SELECT BookInfoID,BookInfoBarCode FROM...
the code is fairly short and easy to follow, and you can view exceptions from both tables at the same time.Well, now in SQL 2005, we have another option: usingEXCEPT and INTERSECT. And these are even easier
SQL Server database schemas SQL Azure Cloud objects SQL Server native backups Script folders Database Snapshots Custom mapped tables SQL Server installed on Linux Supported Schema Objects Wth Schema Compare, you can apply the Object Filter feature and compare the following schema objects: Application ...
SQL Server Compare 2 tables in 2 databasesYou can Left join or Exists
-- TEMPLATE - SQL Server T-SQL compare two tables SELECT Label='Found IN BookInfoList, NOT IN InventoryBookList',* FROM (SELECT BookInfoID,BookInfoBarCode FROM BookInfoList EXCEPT SELECT BookInventoryInfoID,BookInventoryBarCode FROM InventoryBookList where BookInventoryPlanId=1) x UNION ALL SELECT...
* To use Redgate's command line tools as part of a Redgate Pipeline (an automated database change delivery process) a Redgate Flyway Enterprise, Redgate Deploy or SQL Toolbelt license is required. For more information, see theProduct Specific Terms. ...
(‘002′,’2′,’80’), (‘003′,’3′,’120’), (‘004′,’4′,’160’) GO The T-SQL code generates 2 tables in different databases, but the table in databaseDB_Targetcontains an extra row: Note: The table names could be same in two different databases. ...
For more information, see Compare and Synchronize Data in One or More Tables with Data in a Reference Database. Note You can also compare the schema of two databases or of two versions of the same database. For more information, see How to: Use Schema Compare to Compare Different Database...