Copy data from Table A to Table B

Question

I need to Copy data FROM [DB1] [dbo.Table A.Field1, Field 2, Field 3]

TO  [DB2]

AND INSERT Field1 AND Field2  

TO [DB2.dbo.TableA]

AND INSERT Field3

TO [DB2.dbo.TableB]

DB1, Table A has the [id] as the PK and this needs to be copied to both DB2. Table A & B so i can link them together.

?? Please help

 

Answer 1

May be:

begin transaction

insert into [DB2].dbo.TableA (ID, Field1, Field2) SELECT ID, Field1, Field2 from [DB1].dbo.[TableA]

insert into [DB2].dbo.TableB (ID, Field3) SELECT ID, Field3 from [DB1].dbo.[TableA]

commit transaction

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Could this be done with a verify statement on it? Thanks

Answer 3

What do you mean by verify statements? Instead of COMMIT TRANSACTION you can use ROLLBACK TRANSACTION first and also add

SELECT * FROM [DB2].dbo.TableA

SELECT * FROM [DB2].dbo.TableB

---------------

If they look OK, you can comment out these two select statements and change ROLLBACK to COMMIT.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

Sorry, my last comment wasnt very informative. Yes just a bit of code to check all the information is the same and correct. As obviously we are splitting the data from one table into a new database into different tables.

Answer 5

How big are your tables? If they are less than 10K rows each, then what I wrote above should work fine for you. If the tables are much bigger, then it makes sense to write batch inserts.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

I currently data used in the whole DB is 6888kb per row around and I have around 35,000 rows.

I used your statement you wrote lastnight and it didnt work, im going to try it again and get back to you.