0

I needed to move out my database to another machine, but since I have very limited read access I can't leverage the backup/restore feature.

I created DDL scripts for the tables and ran them on my new VM. I then extracted the tables that I needed and have them sitting on text files. I need a way to import these files into their respective tables. Whenever I use the native import feature it creates an entirely new table with incorrectly defined value types/sizes. So I need to import directly into the tables I created with the DDL scripts.

Thanks in advance for your help.

midiman
  • 109
  • 1
  • 3
  • 17
  • 1
    Can the two machines see each other? If so I would use Data Import/Export SSIS utility built into SSMS. Otherwise you will need to use the import utility to import the file data into specific tables. – Sean Lange Mar 18 '15 at 19:30
  • @SeanLange unfortunately they can't see each other. Can you elaborate on the second point? – midiman Mar 18 '15 at 19:32
  • 1
    Right click the database -> tasks -> import data. You can specify where the data will be imported to. – Sean Lange Mar 18 '15 at 19:52
  • @SeanLange ahh yes, but you can only specify the database - not the table. or am i mistaken? – midiman Mar 18 '15 at 19:57
  • 1
    The first screen does in fact select the database. On the third screen of the wizard you would chose Copy Data from one or more tables or views. Then you can get as detailed as you want. Go try out the tool it does exactly what you need it to do. – Sean Lange Mar 18 '15 at 20:00
  • Did the trick, thank you :) shamefully didn't see that the source table can be modified. If you want to post as an answer I'll check :) – midiman Mar 19 '15 at 00:41

1 Answers1

1

Right click on your database node, select Tasks > Generate Scripts... . Hit Next, select the tables you want to extract then Next. Then click the Advanced button.

In here under General there is a line option item called Types of data to script, change this to either Data only or Schema and data, and whatever other output options works for you.

Ben Reyes
  • 11
  • 3
  • thank you for the suggestion Ben , I didn't actually know about this. Unfortunately this option is disabled for me. – midiman Mar 19 '15 at 00:40
  • I found this proc searching around: http://vyaskn.tripod.com/code/generate_inserts.txt I haven't used it but it sounds like what you're looking for. It came from this thread. http://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table – Ben Reyes Mar 20 '15 at 16:23