SQL Server Management Studio 2012 - Export/Import Data From/To Table

SQL Server Management Studio 2012 - Export/Import data from/to table

I am not quite sure if I understand your requirements (I don't know if you need to export your data to excel or you want to make some kind of backup).

In order to export data from single tables, you could use Bulk Copy Tool which allows you to export data from single tables and exporting/Importing it to files. You can also use a custom Query to export the data.

It is important that this does not generate a Excel file, but another format. You could use this to move data from one database to another (must be MS SQL in both cases).

Examples:
Create a format file:

Bcp [TABLE_TO_EXPORT] format "[EXPORT_FILE]" -n -f "[ FORMAT_FILE]" -S [SERVER] -E -T -a 65535

Export all Data from a table:

bcp [TABLE_TO_EXPORT] out "[EXPORT_FILE]" -f "[FORMAT_FILE]" -S [SERVER] -E -T -a 65535

Import the previously exported data:

bcp [TABLE_TO_EXPORT] in [EXPORT_FILE]" -f "[FORMAT_FILE] " -S [SERVER] -E -T -a 65535

I redirect the output from hte export/import operations to a logfile (by appending "> mylogfile.log" ad the end of the commands) - this helps if you are exporting a lot of data.

SQL Server Management Studio 2012 - Export all tables of database as csv

The export wizard allows only one at a time. I used the powershell script to export all my tables into csv. Please try this if it helps you.

$server = "SERVERNAME\INSTANCE"
$database = "DATABASE_NAME"
$tablequery = "SELECT schemas.name as schemaName, tables.name as tableName from sys.tables inner join sys.schemas ON tables.schema_id = schemas.schema_id"

#Delcare Connection Variables
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $tablequery
$command.Connection = $connection

#Load up the Tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

# Loop through all tables and export a CSV of the Table Data
foreach ($Row in $DataSet.Tables[0].Rows)
{
    $queryData = "SELECT * FROM [$($Row[0])].[$($Row[1])]"

    #Specify the output location of your dump file
    $extractFile = "C:\mssql\export\$($Row[0])_$($Row[1]).csv"

    $command.CommandText = $queryData
    $command.Connection = $connection

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $command
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $connection.Close()

    $DataSet.Tables[0]  | Export-Csv $extractFile -NoTypeInformation
}

Thanks

How to export all data from table to an insertable sql format?

Quick and Easy way:

1. Right click database
2. Point to tasks In SSMS 2017 you need to ignore step 2 - the generate scripts options is at the top level of the context menu Thanks to Daniel for the comment to update.
3. Select generate scripts
4. Click next
5. Choose tables
6. Click next
7. Click advanced
8. Scroll to Types of data to script - Called types of data to script in SMSS 2014 Thanks to Ellesedil for commenting
9. Select data only
10. Click on 'Ok' to close the advanced script options window
11. Click next and generate your script

I usually in cases like this generate to a new query editor window and then just do any modifications where needed.

Use Import / Export from SQL Server Management Studio to reduce database size

Whether 30 Gb is large or small obviously depends on your specific business context, so I'm not sure what Preben is trying to say there. Maybe just having a bit of a boast?

However, I agree that using the wizard to spin up a new copy of the database is an odd approach. It would be much faster and easier to backup the existing production database, and restore it as a new copy.

The target database will be smaller because it will have no fragmentation, and possibly because the recovery setting is different. As the first database is constantly in use over a long period of time, SQL has to find somewhere to put all the new data being created.

As an analogy, think about all the things you have in your home. Things are on shelves, things are in cabinets, some things are just on the floor. Now you go and buy something new, and you have to figure out where to put it. Maybe you have to move some of your old things to find space for the new things. Everything gets spread out.

Then one day you decide to move house. You take all your things, put them in boxes, and move the boxes to the new house. Everything in your new home is organised into a small amount of space, inside the boxes, because you haven't yet started using any of it.

Once you start actually using those things you will have to spread them all around your new home, on shelves, in cabinets, and so on, and they will suddenly be taking up more space. The more things you need to use, the more boxes get unpacked and spread around the room.

Get .sql file from SQL Server 2012 database

If you just want to generate a .sql script you can do this by right-clicking the database in Object Explorer and choosing Tasks > Generate Scripts:

Then you can select database and all objects, or you can select just the table you want:

Then select a location, and be sure to go into Advanced before continuing:

Under advanced, at the very least, change types of data to script to "Schema and Data" (if the reason you are doing this rather than a proper backup is because you need to import the data into a database earlier than SQL Server 2012, you should probably change the target server version option to whatever version you're targeting):

If you don't have that option in Management Studio, you are probably still running the RTM version of the client tools. Full functionality has finally been made free as of SP1. Download the most recent version - SP2 - here:

http://www.microsoft.com/en-us/download/details.aspx?id=43351

(You want one of the SQLManagementStudio files.)

Import / Export database with SQL Server Server Management Studio

Right click the database itself, Tasks -> Generate Scripts...

Then follow the wizard.

For SSMS2008+, if you want to also export the data, on the "Set Scripting Options" step, select the "Advanced" button and change "Types of data to script" from "Schema Only" to "Data Only" or "Schema and Data".

MS Microsoft SQL Server Management Studio - Import/Export-Database with Default Value or Binding

Try to a create a "create script" for your source table and use that script to create your target structure. Then you can copy the data using the import wizard.

SQL Server Management Studio Import/Export Data crashes on launch with The data is invalid

Finally figured it out. A few days before this started I was messing with Winaero Tweaker, and followed the instructions here to remove the "Share with Skype" item from Explorer's context menu. I undid that change to the registry key and DTSWizard.exe works again.

These are the steps from the Winaero site that broke it:

• Go to the following Registry key: HKEY_CLASSES_ROOT\PackagedCom\ClassIndex{776DBC8D-7347-478C-8D71-791E12EF49D8}.
• On the left, right-click on the {776DBC8D-7347-478C-8D71-791E12EF49D8} folder, and select Rename from the context menu.Rename Key Comand In Regsitry
• Specify -{776DBC8D-7347-478C-8D71-791E12EF49D8} as the new key name. (just add the minus sign to the folder name).

Export tables in SSMS and import them onto a different server

Until there is a better answer, I will just leave what we did here.

We have created a set of instructions and a script that will get the client to create a new database, then use the script to transfer the data over to the new database, and then back up this newly created database.

The script (query) effectively creates a loop to go through the tables and create the sql with:

SET @sql = 'SELECT * INTO [' + @toDB + '].' + @currTable + ' FROM [' + @fromDB + '].' + @currTable

which takes the current table name (@currTable) and moves it from their main database (@fromDB) into the newly created database (@toDB).

This is not ideal, but for now seems to be the simplest option for large amounts of data. What would be great is if they had an option when doing a backup of choosing which tables to include.

For reference if others need to do something like this, here is the script:

--before you run this script, check that the 2 variables at the top are set correctly
--the @toDB variable should be a database you have just created to temporarily store exported data
DECLARE @fromDB VARCHAR(max) = 'main_database' --this should be set to the name of the database you are copying from
DECLARE @toDB VARCHAR(max) = 'main_database_export' --this should be set to the name of the database you are copying to (the temporary one)

/* ------------------------------------------
---------Do not edit from here down---------
------------------------------------------- */
--declare variables to be used in different parts of the script
DECLARE @sql VARCHAR(max)
DECLARE @currPos INT = 1
DECLARE @currTable VARCHAR(max)
DECLARE @tableNames TABLE(id INT, name varchar(max))
--create a list of files that we want top copy to the new database, the id must be sequential and start at 1)
INSERT INTO @tableNames VALUES
    (1, '[dbo].[table1]'),
    (2, '[dbo].[table2]'),
    (3, '[dbo].[table3]'),
    (4, '[dbo].[table4]')

DECLARE @totalTables INT = 4 --this should always be the number of the last table to be copied, if you add more or take any away, update this

--loop through the tables and copy them across
WHILE (@currPos <= @totalTables)
BEGIN

  --get the table name of the table we are up to
    SELECT @currTable = name FROM @tableNames WHERE id = @currPos

  --create the sql that will copy from the old table into the new table (including the table structure), this table must not exist yet
    SET @sql = 'SELECT * INTO [' + @toDB + '].' + @currTable + ' FROM [' + @fromDB + '].' + @currTable

  --run the sql statement we just created, this will create the table and copy the content (and leave a message to say how many rows were copied)
    EXECUTE (@sql)

  --set the counter up one so we move onto the next table
    SET @currPos = @currPos+1

  --output the name of the table that was just processed (note that no messages will show until the entire script finishes)
    PRINT @currTable + ' Copied.'

END

Note that this script is designed to give to the client, the "Do not edit from here down" is an instruction for them (you will need to edit the table names you are copying and the variable holding the total number of tables).

We then send this with a set of instructions on how to create the new database, run this script and then back up the new database etc.

---

Related Topics