You have couple of options :
Option A : Script out database in compatibility mode using Generate script option :
Note : If you script out database with schema and data, depending on your data size, the script will be massive and wont be handled by SSMS, sqlcmd or osql (might be in GB as well).
First script out tables first with all Indexes, FK’s, etc and create blank tables in the destination database – option with SCHEMA ONLY (No data).
Use BCP to insert data
- bcp out the data using below script. set SSMS in Text Mode and copy the output generated by below script in a bat file.
-- save below output in a bat file by executing below in SSMS in TEXT mode -- clean up: create a bat file with this command --> del D:\BCP\*.dat select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" ' /* path to BCP.exe */ + QUOTENAME(DB_NAME())+ '.' /* Current Database */ + QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.' + QUOTENAME(name) + ' out D:\BCP\' /* Path where BCP out files will be stored */ + REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' + REPLACE(name,' ','') + '.dat -T -E -SServerName\Instance -n' /* ServerName, -E will take care of Identity, -n is for Native Format */ from sys.tables where is_ms_shipped = 0 and name <> 'sysdiagrams' /* sysdiagrams is classified my MS as UserTable and we dont want it */ /*and schema_name(schema_id) <> 'unwantedschema' */ /* Optional to exclude any schema */ order by schema_name(schema_id)
- Run the bat file that will generate the .dat files in the folder that you have specified.
- Run below script on the destination server with SSMS in text mode again.
--- Execute this on the destination server.database from SSMS. --- Make sure the change the @Destdbname and the bcp out path as per your environment. declare @Destdbname sysname set @Destdbname = 'destinationDB' /* Destination Database Name where you want to Bulk Insert in */ select 'BULK INSERT ' /*Remember Tables must be present on destination database */ + QUOTENAME(@Destdbname) + '.' + QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) + '.' + QUOTENAME(name) + ' from ''D:\BCP\' /* Change here for bcp out path */ + REPLACE(SCHEMA_NAME(schema_id), ' ', '') + '_' + REPLACE(name, ' ', '') + '.dat'' with ( KEEPIDENTITY, DATAFILETYPE = ''native'', TABLOCK )' + char(10) + 'print ''Bulk insert for ' + REPLACE(SCHEMA_NAME(schema_id), ' ', '') + '_' + REPLACE(name, ' ', '') + ' is done... ''' + char(10) + 'go' from sys.tables where is_ms_shipped = 0 and name <> 'sysdiagrams' /* sysdiagrams is classified my MS as UserTable and we dont want it */ and schema_name(schema_id) <> 'unwantedschema' /* Optional to exclude any schema */ order by schema_name(schema_id)
- Run the output using SSMS to insert data back in the tables.
This is very fast bcp method as it uses Native mode.
- Right click on your database and select Tasks -> Generate Scripts
- Click Next to move past the intro screen and onto the next page, then select the objects you want to script. Note that you can script all SQL objects here, not just tables. This is because this wizard allows you to script schema and data. Importantly, schema only is the default, which you’ll need to change on the next page. Click Next.
- Now you’ll be presented with a set of options mainly relating to how the script is going to be output, however there is an unassuming button labelled Advanced. Clicking this button will give you a dialog with a list of options. The last option under the General section is “Types of data to script”, which you should change from “Schema only” to “Data only”, or “Schema and data”.
- Finally, decide where you want the script to do and you should be able to generate your INSERT statements.
Generating INSERT statements in SQL Server 2012
September 30, 2012 by Muhammad Imran
Recently, I wrote an article on “Generating Insert statements, data & schema using SQL Server Publishing Wizard”. It is a very nice tool to generate insert into statement from table data.
In SQL server 2012, the feature of this tool plus additional features are provided. Lets proceed step by step to generate insert into statement from table data in SQL server 2012.
Step 1 :
In the first step, you need to right click on Databases >> Tasks >> Generate Scripts…
Step 2 :
This is the welcome screen. If you don’t need this screen again and again you can check on “Do not show this page again”.
Step 3 :
In this step, you need to generate the script for one object or all objects available in the database. You can further drill down if you need to generate any table. For example :
Setup 4 :
The next step is to define the script location, clip board or in a new window. In the same step, if you click on Advanced button, it will give you further control on scripting.
This is the advance screen, in this screen you can choose whether you need data, schema or data and schema scripts.’
But if you need only Insert into statement from table data, you must select data only. By default it is schema only.
It also allows you to select the SQL version.
Step 5 :
In this step it will display the summary of your selection. If you need you can go back and change the settings.
Step 6 :
In this step, it will display the result.