How to compare different database table column and adding in SQL Server?

sql server compare two tables schema
how to compare two columns in two different tables in sql server
how to compare data in two tables in sql
compare two sql queries for differences
how to compare two databases in sql server
sql query to compare two tables in different databases in oracle
how to compare two tables in different servers in sql server
sql compare two tables and find records without matches

I have two different databases, the same column. I want to copy the pictures from the old database to the new database by comparing the numbers between the tables and having the same number.

For example:

Database name 1: StudentInformation, table name: Student

StudentID     Image
---------------------
  123         1.png    
  142         2.png    
  175         3.png    
  475         4.png

Database name 2: StudentInformation2, table name: NewStudent

 StudentID       Image
 --------------------------
  145            a14.png    
  196            7.png    
  175            5.png    
  875            9.png    
  475            12.png 

The two tables have common IDs (ID=175 and ID=475)

I want to get this table as a result:

StudentID    Image
-----------------------
  145        a14.png    
  196          7.png    
  175          3.png    
  875          9.png    
  475          4.png

I wrote this query:

INSERT INTO NEW STUDENT 
    SELECT STUDENT ID 
    FROM STUDENT 
    WHERE STUDENT INFORMATION1.STUDENT ID = STUDENT INFORMATION2.STUDENTID

But it doesn't run. How do I write SQL to add images to common ID columns by comparing the old table with the ID values of the new table?

You want an UPDATE, that changes existing rows, not an INSERT, which created new rows. For that, you can use a correlated subquery, to get the image of the old table with the shared ID. To make sure to only update rows where a row in the old table exists, use a correlated subquery with EXISTS. Otherwise you'd set the images of rows, where no old record exists to NULL.

UPDATE studentinformation2.dbo.newstudent
       SET image = (SELECT student.image
                           FROM studentinformation.dbo.student
                           WHERE student.studentid = newstudent.studentid)
       WHERE EXISTS (SELECT *
                            FROM studentinformation.dbo.student
                            WHERE student.studentid = newstudent.studentid);

I assumed the schema of the tables is dbo in both databases. If that's not the case you have to change the schema identifiers to reflect the actual situation.

Use Schema Compare to Compare Different Database Definitions , How can I compare two tables in different database in SQL Server? To compare data by using the New Data Comparison Wizard. On the SQL menu, point to Data Compare, and then click New Data Comparison. The New Data Comparison wizard appears. Also, the Data Compare window opens, and Visual Studio automatically assigns it a name such as DataCompare1. Identify the source and target databases.

It's not clear whether the - value in the second sample is really that character in the data, or if it's just standing in for NULL. In this answer, I assume the latter.


The first thing to understand is you can't leave the spaces in the table and column names. Best not to use spaces in table and column names at all, but if you must, you have to at least enclose those names in square brackets, ie: [NEW STUDENT].

Next, you already have data for StudentID values 175 and 475, so this will not be an INSERT command at all. It will be an UPDATE command.

Now, assuming the databases are on the same server or that a linked server already exists, and your connection has the needed privileges to both databases, you will have to fully qualify everything, including the schema (almost always dbo).

Put all that together, and you get this:

UPDATE n /* n = new, o = old */
SET n.Image = o.Image
FROM StudentInformation2.dbo.NewStudent n
INNER JOIN StudentInformation.dbo.Student o ON o.StudentID = n.StudentID
-- removed WHERE clause based on a comment to the question. The o
--WHERE n.Image IS NULL

How do I find the difference between two columns in SQL? There is a nice command line tool used to compare tables. This can be found in "C:\Program Files\Microsoft SQL Server\110\COM\" folder. This command line tool is used to compare tables. It also generates a script with the INSERT, UPDATE and DELETE statements to synchronize the tables.

If you are using microsoft sql server you can use for inserting new data with this

INSERT INTO NEW_STUDENT (ID,Image)
SELECT student.ID, stundet.Image
FROM STUDENT left join NEW_STUDENT on NEW_STUDENT.StundetID = Student.ID
where newstudent.ID is null

and for updating data use this

update newstudent set id = student.id,image = student.image
SELECT student.ID, stundet.Image
FROM STUDENT inner join NEW_STUDENT on NEW_STUDENT.StundetID = Student.ID

window opens, and Visual Studio automatically assigns it a name such as SqlSchemaCompare1 . In essance it creates a row level checksum on all the columns data, you could then compare the checksum of each row for each table to each other, use a left join, to find rows that are different. Hope that made sense Better with an example.

Useful T-SQL queries for SQL Server to explore database schema. Compare tables and columns in two databases on SQL Server column in dbo.columns table and added a number of columns in various tables as you can see below. The Column mapping feature is particularly useful in situations where two tables share the same name, but their column names are different; in that case each column pair needs to be mapped manually in order for the data stored within them to be included when SQL tables are compared to check for data differences

If you've been developing in SQL Server for any length of time, you've no doubt We'll need two tables to test with, so here is some simple code that will do the trick: Relational databases combine set theory with relational calculus. Now, so we don't compare the columns that we know will be different:  (In case you are wondering, SELECT * is fine in this case because of the way the temporary tables are created – the schema and column order will be the same for any given release of SQL Server.) If the query does return results, you’ll have to take those differences into account.

I have two database's, named DB1 and DB2 in Sql server 2008. These two database's have the same tables and same table data also. However, I want to check if  Try to use Information_Schema to create a table compare procedure. Check out these Information Schema Columns Tips; Evaluate these SQL Server Comparison Tools; Read this tip: Ways to compare and find differences for SQL Server tables and data. Last Updated: 2017-04-18

Comments
  • You can try to use UPDATE instead of INSERT
  • Is the dash (-) character in the second sample standing in for NULL, or do you really have a dash there?
  • Hi.This value can be any value or may be null.What is important is that this common field receives the first table value.
  • @K.E Okay. I altered your sample data so that information is evident from the data. Now we can clearly see ID 175 already had an Image value in the new table, but the desired result used the value from the old table.
  • If the first table and the second table should be the same as the values of the first table and the values of the first table should be the same.