2

In SQL Server 2008, you can right-click on a database and select "Generate Scripts...". You can then choose a particular table and choose "Data only" under the "Types of data to script". When you do this, it automatically inserts a line in the script like this every 100 records:

print 'Processed 100 total records'

Basically, it "batches" the records being inserted into sections of 100 and adds these PRINT statments at the end of each batch. While this is fine when you are inserting a couple thousand records, it becomes useless when you are importing a million records.

Is there a way to change this "batch" size to another value, like "1000"? I couldn't find this in the list of options when creating the script. Would changing this value affect performance when the script is run or are there other issues?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Loki70
  • 613
  • 4
  • 15
  • 36

1 Answers1

0

Find and replace should fix your immediate issue, but generating an INSERT script is an unusual way to copy millions of records from one database to another; is there some specific reason you want to do it this way?

A more typical method would be bcp.exe or SSIS (if you have more complex needs) because they are specifically intended for working with large amounts of data. Or if both databases are on the same server, then INSERT...SELECT... will be the fastest way of all. The documentation explains the various options in pure TSQL and gives advice on performance; SSIS is a larger topic.

Pondlife
  • 15,992
  • 6
  • 37
  • 51
  • Good points. I should have described the scenario leading up to this more in detail. I have a client who's company will not let ANYONE from the "outside" access to its servers. I have developed an application and database on my machine, which is SQL 2008. Unfortunately, the target machine at the client is SQL 2005. After much research, I have found that you can't just use the usual methods to transfer a whole database while "downgrading" like this. Therefore, I have to create scripts to handle everything - including data population. – Loki70 Jul 15 '11 at 18:05
  • So someone inside the company has to deploy your application? Have you considered installing SQL 2005, preparing the database and then sending them a backup file to restore? You should at least develop on the same database version as your client anyway. – Pondlife Jul 18 '11 at 07:17
  • Good point, but again, it is more complicated than that. Part of my project was to migrate their existing MS Access database to SQL. Without going into to details, the Access database was COMPLICATED and getting it into SQL (via an SSIS package I wrote) was challenging, to say the least. Because I don't want to try and rewrite this SSIS package in 2005, I am stuck trying to convert my 2008 database into a 2005 one. – Loki70 Jul 18 '11 at 15:29
  • So can you use the SSIS 2008 package to populate a 2005 database? Presumably you have all your DDL under source control, so it should be fairly simple to create an empty 2005 database (you can't be using any 2008-specific features because the target is 2005 anyway). Then use your existing SSIS package but just change the destination. Of course, this may still be unrealistic; only you know your situation well enough to say what can work. – Pondlife Jul 19 '11 at 06:45