How can I select data in one DB, and use it to insert rows in another DB in T-SQL?

Question

I'm trying to not write an app to do this, and improve my SQL mojo at the same time...

Say I have data in one database table Database1.dbo.MyTable with the following columns:

• ObjectType
• ObjectKeyID

There are thousands of these rows.

In Database2.dbo.MyOtherTable I have a slightly different schema, let's say:

• MyKey
• MyValue

I want to take the data from Database1.dbo.MyTable, and use each row's data as an INSERT into Database2.dbo.MyOtherTable.

My guess is that I have to establish a cursor in a while loop, but not sure of the exact syntax to do that, or if there is a better way. What's the best technique/syntax to use for this?

EDIT: Here's what I ended up using (fields changed for this example), worked great in addition to making sure the keys existed before inserting:

INSERT INTO Database2.dbo.MyOtherTable (MyKey, MyValue)
SELECT ObjectType, ObjectKeyID FROM Database1.dbo.MyTable
WHERE ObjectType LIKE 'Widget' AND ObjectKeyID > 0 AND ObjectKey IN (SELECT UserAccountID FROM MyUsers)

Answer 1

You can (and should if possible) avoid using a cursor:

INSERT INTO Database2.dbo.MyOtherTable (MyKey, MyValue)
SELECT ObjectKeyID, ObjectType FROM Database1.dbo.MyTable