May 28, 2009 at 11:27 am
I need to create INSERT scripts to get data from one table to another totally different database on a different server. I understand the INSERT INTO statement, but do I have to type all of the values? Seems like there would be a simpler way.
May 28, 2009 at 11:30 am
Take a look at both Insert...Select and Select...Into. You can select data from one table (even in another database or on another server), into another. That way, you don't have to type any of the values at all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 28, 2009 at 11:47 am
Hi, If the table is in a different instance or server, you first need to configure a linked server, and write the following statement:
Insert into dbo.LocalTable (LField1, LField2, LField3)
Select (RField4, RField5, RField6) from LNKSRV1.dbProd.dbo.RemoteTable
Where:
-LocalTable is the table in the local server.
-LNKSRV1 is the linked server. You can create the linked server exploring the Server Objects level in the Object Explorer.
-RemoteTable is the table located in the remote linked server.
Note that the fields are called different assuming the two tables has different structures. be careful with the datatype.
I hope this can help you.
Alberto
Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader
May 28, 2009 at 1:25 pm
Brad Allison (5/28/2009)
I need to create INSERT scripts to get data from one table to another totally different database on a different server. I understand the INSERT INTO statement, but do I have to type all of the values? Seems like there would be a simpler way.
1- Go to target server, the one where you will be inserting data
2- Link the second server
3- Try "insert into ... select * from..." syntax -inserting in local server while reading from second -linked server. If table structures are not the same use list of columns on select.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply