Need SQL to shift entries from one table to another

Heres the situation. I have 2 tables here of the schema:

ID | COMPANY_NAME | DESC | CONTACT

ID | COMPANY_ID | X_COORDINATE | Y_COORDINATE

The first tabel contains a list of companies and the second contacts coordinates of the companies as mentioned.

The thing is that I want to merge the data in this table with the data in another set of tables which already have data. The other tables have similar structure but are already propopulated with data. The IDs are autoincremental.

SO if we have lets say companies marked 1-1000 in table1 and com开发者_Go百科panies marked 1-500 in table 2. We need it merged such that ID number 1 in table 2 becomes ID 1001 when migrated to the other table. And side by side we would also want to migrated the entries in the coordinates table as well in such a way that they map with the new ids of the table. Can this be done in SQL or do I need to resort to using a script here for this kind of work.

i`m not sure i understand how many tables are there and who is table 1 ,2, but the problem is pretty clear. i think the easy way is:

back up all your database before you start this process

add a column to the destination table that will contain the original id.

insert all the records you want to merge (source) into the destination table, putting the original id in the column you added.

now you can update the geo X,Y data using the old ID

after all is done and good you can remove the original id column.

EDIT: in reply to your comment , i`ll add teh code here, since its more readable.

adapted from SQL Books Online: insert rows from another table

INSERT INTO MyNewTable  (TheOriginalID, Desc)
       SELECT ID, Desc
       FROM OldTable;

Then you can do an update to the new table based on values from the old table like so:

UPDATE MyNewTable SET X = oldTable.X , Y = oldTable.Y where
FROM MYNewTable inner JOIN OldTable ON MYNewTable.TheOriginalID = OldTable.ID