Sfrgttk

Is layered encryption more secure than long passwords?

How to assess the susceptibility of a U.S. company to go bankrupt?

Taking an academic pseudonym?

Is there a technology capable of disabling the whole of Earth's satellitle network?

Discouraging missile alpha strikes

Someone wants me to use my credit card at a card-only gas/petrol pump in return for cash

Workplace intimidation due to child's chronic health condition

Why would you use 2 alternate layout buttons instead of 1, when only one can be selected at once

What prevents people from lying about where they live in order to reduce state income taxes?

Tikz - highlight text in an image

How can a kingdom keep the secret of a missing monarchy from the public?

Suggestions on how to improve logo

Why did Shae (falsely) implicate Sansa?

Bitcoin automatically diverted to bech32 address

A dragon's soul trapped in a ring of mind shielding wants a new body; what magic could enable her to do so?

"Cheaper by the dozen" phrase origin?

Contribution form

How to know if I am a 'Real Developer'

What happens when the last remaining players refuse to kill each other?

Is it possible to detect 100% of SQLi with a simple regex?

Pictures from Mars

How to explain one side of Super Earth is smoother than the other side?

How to draw these kind of adjacent ovals with arrows in latex?

Why did Tywin never remarry?

Compare two columns in two databases to insert the content of a third column into a forth one

ON DUPLICATE KEY UPDATE questionIs there a performance hit by breaking out tables into different schemas in SQL Server?Import from one table in two tables where I need the auto-generated primary key in the second tableStored procedure to compare two columns from different tables and make the insertHow can I compare the schema of two databases?Updating a table from another databaseHow to compare two different tables with different number of entries and not same columnMERGE results with NULL valuesQuery to compare two column of table 1 with two column of table 2 and update the third column in table 1 as 'y' if the record matchesCompare all 70 columns from two tables in same database

2

I have two tables in different databases:

b.dbo.table1

ID

Code

w.dbo.table2:

ID 

Code2 

ID2 

I am in need of code that helps me to compare (Code) in table1 with (Code2) in table2. If they are the same then insert (ID) from table1 into (ID2) in table2.

sql-server

share|improve this question

edited Jul 2 '18 at 14:06

Paul White♦

52.1k14278450

asked Jul 2 '18 at 13:08

IbrahimIbrahim

111

bumped to the homepage by Community♦ 1 hour ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

2

I have two tables in different databases:

b.dbo.table1

ID

Code

w.dbo.table2:

ID 

Code2 

ID2 

I am in need of code that helps me to compare (Code) in table1 with (Code2) in table2. If they are the same then insert (ID) from table1 into (ID2) in table2.

sql-server

share|improve this question

edited Jul 2 '18 at 14:06

Paul White♦

52.1k14278450

asked Jul 2 '18 at 13:08

IbrahimIbrahim

111

bumped to the homepage by Community♦ 1 hour ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

I have two tables in different databases:

b.dbo.table1

ID

Code

w.dbo.table2:

ID 

Code2 

ID2 

I am in need of code that helps me to compare (Code) in table1 with (Code2) in table2. If they are the same then insert (ID) from table1 into (ID2) in table2.

sql-server

share|improve this question

edited Jul 2 '18 at 14:06

Paul White♦

52.1k14278450

asked Jul 2 '18 at 13:08

IbrahimIbrahim

111

ID

Code

ID 

Code2 

ID2 

share|improve this question

edited Jul 2 '18 at 14:06

Paul White♦

52.1k14278450

asked Jul 2 '18 at 13:08

IbrahimIbrahim

111

share|improve this question

edited Jul 2 '18 at 14:06

Paul White♦

52.1k14278450

asked Jul 2 '18 at 13:08

IbrahimIbrahim

111

edited Jul 2 '18 at 14:06

Paul White♦

52.1k14278450

edited Jul 2 '18 at 14:06

Paul White♦

52.1k14278450

asked Jul 2 '18 at 13:08

IbrahimIbrahim

111

asked Jul 2 '18 at 13:08

IbrahimIbrahim

111

2 Answers
2

active

oldest

votes

0

Here's the code:

INSERT INTO w.dbo.table2 (ID2)

SELECT T1.ID

FROM b.dbo.table1 AS T1

INNER JOIN w.dbo.table2 AS T2

    ON T1.Code = T2.Code2

Here's the breakdown:

I am in need to a code that helps me to compare (Code) in table1 with
(Code2) in table2

You can do this by joining the tables together. There are other methods that also exist, such as subqueries, where clauses, and others. I'm keeping it simple with a join to start as that is where you will typically see people perform this operation. As you progress in SQL development, you'll learn other tricks and tools. It's at this point when you begin to measure the results of the ways you do things, how much faster was it, how many resources did it use, was it optimally using indexes, etc. For now though, basic syntax and vanilla practice.

We are SELECTing the ID column from T1. T1 is the ALIAS I have given b.dbo.table1. The INNER JOIN is matching the values from T1 and T2 (T2 is the ALIAS I gave w.dbo.table2) and when it finds a match between them, it will return the result set specified in the SELECT clause. The ON piece is where we are stating the Code column in T1 must match exactly on T2 in the Code2 column.

and if they are the same then insert (ID) from teable1 into (ID2) in
table 2.

The last piece I'll cover is the INSERT. I'm putting it ahead of the other query and telling it I want to INSERT into w.dbo.table2, specifically in the ID2 column. This is matched to the SELECT of the query below. (Which is bringing the ID from T1 where code from T1 matches code2 from T2.)

Jeremy Kadlec has a good MSSQLTips post on Inserts.

He also has one on joins.

share|improve this answer

answered Jul 2 '18 at 13:31

ShaulinatorShaulinator

2,4781622

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you very much for the help but for some reason it's generating repetitions. I tried to change it several times but the result is the same.
  
  – Ibrahim
  Jul 16 '18 at 8:06
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Ibrahim Try adding a DISTINCT to your SELECT statement. If that does not help you, can you provide a table creation statement, some data insert statements, and the expected output? I would be able to see more clearly with a well defined example set and could give you a correct answer without guessing.
  
  – Shaulinator
  Jul 16 '18 at 17:06
  

  
  
  
  

add a comment | 

0

It's not clear what's the link between your 2 tables.

If the link between your tables is through Code and Code2:

UPDATE T2 SET

    ID2 = T1.ID

FROM

    b.dbo.table1 AS T1

    INNER JOIN w.dbo.table2 AS T2 ON T1.Code = T2.Code2

If the link is through ID and the comparison by Code:

UPDATE T2 SET

    ID2 = T1.ID

FROM

    b.dbo.table1 AS T1

    INNER JOIN w.dbo.table2 AS T2 ON T1.ID = T2.ID

WHERE

    T1.Code = T2.Code2

I'm assuming that you meant an UPDATE of the column ID2 when the codes match and not creating a new row just with the ID2 value (which would require an INSERT operation).

share|improve this answer

edited Jul 2 '18 at 13:34

answered Jul 2 '18 at 13:20

EzLoEzLo

2,1571420

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f211135%2fcompare-two-columns-in-two-databases-to-insert-the-content-of-a-third-column-int%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Here's the code:

INSERT INTO w.dbo.table2 (ID2)

SELECT T1.ID

FROM b.dbo.table1 AS T1

INNER JOIN w.dbo.table2 AS T2

    ON T1.Code = T2.Code2

Here's the breakdown:

I am in need to a code that helps me to compare (Code) in table1 with
(Code2) in table2

You can do this by joining the tables together. There are other methods that also exist, such as subqueries, where clauses, and others. I'm keeping it simple with a join to start as that is where you will typically see people perform this operation. As you progress in SQL development, you'll learn other tricks and tools. It's at this point when you begin to measure the results of the ways you do things, how much faster was it, how many resources did it use, was it optimally using indexes, etc. For now though, basic syntax and vanilla practice.

We are SELECTing the ID column from T1. T1 is the ALIAS I have given b.dbo.table1. The INNER JOIN is matching the values from T1 and T2 (T2 is the ALIAS I gave w.dbo.table2) and when it finds a match between them, it will return the result set specified in the SELECT clause. The ON piece is where we are stating the Code column in T1 must match exactly on T2 in the Code2 column.

and if they are the same then insert (ID) from teable1 into (ID2) in
table 2.

The last piece I'll cover is the INSERT. I'm putting it ahead of the other query and telling it I want to INSERT into w.dbo.table2, specifically in the ID2 column. This is matched to the SELECT of the query below. (Which is bringing the ID from T1 where code from T1 matches code2 from T2.)

Jeremy Kadlec has a good MSSQLTips post on Inserts.

He also has one on joins.

share|improve this answer

answered Jul 2 '18 at 13:31

ShaulinatorShaulinator

2,4781622

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you very much for the help but for some reason it's generating repetitions. I tried to change it several times but the result is the same.
  
  – Ibrahim
  Jul 16 '18 at 8:06
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Ibrahim Try adding a DISTINCT to your SELECT statement. If that does not help you, can you provide a table creation statement, some data insert statements, and the expected output? I would be able to see more clearly with a well defined example set and could give you a correct answer without guessing.
  
  – Shaulinator
  Jul 16 '18 at 17:06
  

  
  
  
  

add a comment | 

0

Here's the code:

INSERT INTO w.dbo.table2 (ID2)

SELECT T1.ID

FROM b.dbo.table1 AS T1

INNER JOIN w.dbo.table2 AS T2

    ON T1.Code = T2.Code2

Here's the breakdown:

I am in need to a code that helps me to compare (Code) in table1 with
(Code2) in table2

You can do this by joining the tables together. There are other methods that also exist, such as subqueries, where clauses, and others. I'm keeping it simple with a join to start as that is where you will typically see people perform this operation. As you progress in SQL development, you'll learn other tricks and tools. It's at this point when you begin to measure the results of the ways you do things, how much faster was it, how many resources did it use, was it optimally using indexes, etc. For now though, basic syntax and vanilla practice.

We are SELECTing the ID column from T1. T1 is the ALIAS I have given b.dbo.table1. The INNER JOIN is matching the values from T1 and T2 (T2 is the ALIAS I gave w.dbo.table2) and when it finds a match between them, it will return the result set specified in the SELECT clause. The ON piece is where we are stating the Code column in T1 must match exactly on T2 in the Code2 column.

and if they are the same then insert (ID) from teable1 into (ID2) in
table 2.

The last piece I'll cover is the INSERT. I'm putting it ahead of the other query and telling it I want to INSERT into w.dbo.table2, specifically in the ID2 column. This is matched to the SELECT of the query below. (Which is bringing the ID from T1 where code from T1 matches code2 from T2.)

Jeremy Kadlec has a good MSSQLTips post on Inserts.

He also has one on joins.

share|improve this answer

answered Jul 2 '18 at 13:31

ShaulinatorShaulinator

2,4781622

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you very much for the help but for some reason it's generating repetitions. I tried to change it several times but the result is the same.
  
  – Ibrahim
  Jul 16 '18 at 8:06
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Ibrahim Try adding a DISTINCT to your SELECT statement. If that does not help you, can you provide a table creation statement, some data insert statements, and the expected output? I would be able to see more clearly with a well defined example set and could give you a correct answer without guessing.
  
  – Shaulinator
  Jul 16 '18 at 17:06
  

  
  
  
  

add a comment | 

Here's the code:

INSERT INTO w.dbo.table2 (ID2)

SELECT T1.ID

FROM b.dbo.table1 AS T1

INNER JOIN w.dbo.table2 AS T2

    ON T1.Code = T2.Code2

Here's the breakdown:

I am in need to a code that helps me to compare (Code) in table1 with
(Code2) in table2

You can do this by joining the tables together. There are other methods that also exist, such as subqueries, where clauses, and others. I'm keeping it simple with a join to start as that is where you will typically see people perform this operation. As you progress in SQL development, you'll learn other tricks and tools. It's at this point when you begin to measure the results of the ways you do things, how much faster was it, how many resources did it use, was it optimally using indexes, etc. For now though, basic syntax and vanilla practice.

We are SELECTing the ID column from T1. T1 is the ALIAS I have given b.dbo.table1. The INNER JOIN is matching the values from T1 and T2 (T2 is the ALIAS I gave w.dbo.table2) and when it finds a match between them, it will return the result set specified in the SELECT clause. The ON piece is where we are stating the Code column in T1 must match exactly on T2 in the Code2 column.

and if they are the same then insert (ID) from teable1 into (ID2) in
table 2.

The last piece I'll cover is the INSERT. I'm putting it ahead of the other query and telling it I want to INSERT into w.dbo.table2, specifically in the ID2 column. This is matched to the SELECT of the query below. (Which is bringing the ID from T1 where code from T1 matches code2 from T2.)

Jeremy Kadlec has a good MSSQLTips post on Inserts.

He also has one on joins.

share|improve this answer

answered Jul 2 '18 at 13:31

ShaulinatorShaulinator

2,4781622

INSERT INTO w.dbo.table2 (ID2)

SELECT T1.ID

FROM b.dbo.table1 AS T1

INNER JOIN w.dbo.table2 AS T2

    ON T1.Code = T2.Code2

share|improve this answer

answered Jul 2 '18 at 13:31

ShaulinatorShaulinator

2,4781622

answered Jul 2 '18 at 13:31

ShaulinatorShaulinator

2,4781622

answered Jul 2 '18 at 13:31

ShaulinatorShaulinator

2,4781622

0

It's not clear what's the link between your 2 tables.

If the link between your tables is through Code and Code2:

UPDATE T2 SET

    ID2 = T1.ID

FROM

    b.dbo.table1 AS T1

    INNER JOIN w.dbo.table2 AS T2 ON T1.Code = T2.Code2

If the link is through ID and the comparison by Code:

UPDATE T2 SET

    ID2 = T1.ID

FROM

    b.dbo.table1 AS T1

    INNER JOIN w.dbo.table2 AS T2 ON T1.ID = T2.ID

WHERE

    T1.Code = T2.Code2

I'm assuming that you meant an UPDATE of the column ID2 when the codes match and not creating a new row just with the ID2 value (which would require an INSERT operation).

share|improve this answer

edited Jul 2 '18 at 13:34

answered Jul 2 '18 at 13:20

EzLoEzLo

2,1571420

add a comment | 

0

It's not clear what's the link between your 2 tables.

If the link between your tables is through Code and Code2:

UPDATE T2 SET

    ID2 = T1.ID

FROM

    b.dbo.table1 AS T1

    INNER JOIN w.dbo.table2 AS T2 ON T1.Code = T2.Code2

If the link is through ID and the comparison by Code:

UPDATE T2 SET

    ID2 = T1.ID

FROM

    b.dbo.table1 AS T1

    INNER JOIN w.dbo.table2 AS T2 ON T1.ID = T2.ID

WHERE

    T1.Code = T2.Code2

I'm assuming that you meant an UPDATE of the column ID2 when the codes match and not creating a new row just with the ID2 value (which would require an INSERT operation).

share|improve this answer

edited Jul 2 '18 at 13:34

answered Jul 2 '18 at 13:20

EzLoEzLo

2,1571420

add a comment | 

It's not clear what's the link between your 2 tables.

If the link between your tables is through Code and Code2:

UPDATE T2 SET

    ID2 = T1.ID

FROM

    b.dbo.table1 AS T1

    INNER JOIN w.dbo.table2 AS T2 ON T1.Code = T2.Code2

If the link is through ID and the comparison by Code:

UPDATE T2 SET

    ID2 = T1.ID

FROM

    b.dbo.table1 AS T1

    INNER JOIN w.dbo.table2 AS T2 ON T1.ID = T2.ID

WHERE

    T1.Code = T2.Code2

I'm assuming that you meant an UPDATE of the column ID2 when the codes match and not creating a new row just with the ID2 value (which would require an INSERT operation).

share|improve this answer

edited Jul 2 '18 at 13:34

answered Jul 2 '18 at 13:20

EzLoEzLo

2,1571420

UPDATE T2 SET

    ID2 = T1.ID

FROM

    b.dbo.table1 AS T1

    INNER JOIN w.dbo.table2 AS T2 ON T1.Code = T2.Code2

UPDATE T2 SET

    ID2 = T1.ID

FROM

    b.dbo.table1 AS T1

    INNER JOIN w.dbo.table2 AS T2 ON T1.ID = T2.ID

WHERE

    T1.Code = T2.Code2

share|improve this answer

edited Jul 2 '18 at 13:34

answered Jul 2 '18 at 13:20

EzLoEzLo

2,1571420

answered Jul 2 '18 at 13:20

EzLoEzLo

2,1571420

answered Jul 2 '18 at 13:20

EzLoEzLo

2,1571420