0

I have been referring to this post: Copy tables from one database to another in SQL Server

and I can get the data from one database table to the other using code like this:

INSERT INTO bar..tblFoobar( *fieldlist* )
SELECT *fieldlist* FROM foo..tblFoobar

but it doesn't insert the data into the existing rows, it creates new rows. I want to update the already existing rows.

EDIT:

I am thinking I may have to use a cursor to iterate through the original database table column and then update the corresponding column in the new database.

Other notes... these tables have identical structure, one is just for test. I have data now in the test version that I need moved into the production database.

The table currently has about 90 records. Using the INSERT INTO code mentioned above it doubles the number of records exactly.

Community
  • 1
  • 1
Garrett Thompson
  • 25
  • 6
  • you can do Identity insert on, but I'm not sure what you are trying to accomplish here? are you trying to do data migrations? If so why does the Identity field matter so much? – ZeRaTuL_jF Jul 08 '15 at 17:30
  • Can you please clarify if you are trying to just maintain the relationship of the data (i.e keep the Ids intact with the rest of the data) or if you are trying to order the data in a certain way? –  Jul 08 '15 at 17:33
  • 1
    Database tables are unordered by nature... – Zohar Peled Jul 08 '15 at 17:37
  • I am just trying to bring a column from a test database into that same column in a production database. But it does not put the correct values with their correct rows. – Garrett Thompson Jul 08 '15 at 17:42
  • Have you considered using an `UPDATE` instead of `INSERT`? – Samizdat Jul 08 '15 at 17:57
  • @Samizdat what would be the comparable UPDATE statement? – Garrett Thompson Jul 08 '15 at 18:00
  • I have figured out the INSERT statement I used from above just added rows to my table. I am wanting to change the value of an already existing row based on it's column name. Could I do something like this? `UPDATE bar..tblFoobar( *fieldlist* ) SET column_name = (SELECT column_name FROM foo..tblFoobar( *fieldlist* ) ) WHERE foo..tblFoobar(name) = bar..tblFoobar(name)` – Garrett Thompson Jul 08 '15 at 18:51

2 Answers2

0

If you are trying to order the data (i.e least to greatest or by a field) you can create an Index and sort by column (s) based on that. If you are looking to insert and maintain Id relationships, I would suggest you keep the original ID field as a column and create another column for the ID field, that way you can reference it when needed, even when the new Id changes.

0

Looks like you're trying to update a value in a column. When you use INSERT all you're doing is adding new rows to a table... not updating the values in the table. In order to update values, you would use UPDATE.

UPDATE bar..tblFoobar
SET bar..tblFoobar.column_name = foo..tblFoobar.column_name
FROM foo..tblFoobar
  join bar..tblFoobar
    on foo..tblFoobar.name = bar..tblFoobar.name

In the example above I've joined the corrected table (foo..tblFoobar) to the table that needs to be updated (bar..tblFoobar) on a key field (name). Now that the two tables are joined on the appropriate field, then it's just a matter of setting the incorrect column to the corrected value in the other table.

Samizdat
  • 138
  • 8
  • Awesome, I will try this. It looks like exactly what I need. Thanks in advance. – Garrett Thompson Jul 09 '15 at 11:17
  • 1
    this worked! I just had to reference `foo..tblFoobar` with an `AS` statement and call it something different. It was throwing a correlation name error, I assume because both tables shared the same name. Thanks for the help! – Garrett Thompson Jul 09 '15 at 12:48