Exporting data In SQL Server as INSERT INTO
In SSMS in the Object Explorer, right click on the database, right-click and pick "Tasks" and then "Generate Scripts".
This will allow you to generate scripts for a single or all tables, and one of the options is "Script Data". If you set that to TRUE, the wizard will generate a script with INSERT INTO () statement for your data.
If using 2008 R2 or 2012 it is called something else, see screenshot below this one
2008 R2 or later eg 2012
Select "Types of Data to Script" which can be "Data Only", "Schema and Data" or "Schema Only" - the default).
And then there's a "SSMS Addin" Package on Codeplex (including source) which promises pretty much the same functionality and a few more (like quick find etc.)
How to export all data from table to an insertable sql format?
Quick and Easy way:
- Right click database
- 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. - Select
generate scripts
- Click next
- Choose tables
- Click next
- Click advanced
- Scroll to
Types of data to script
- Calledtypes of data to script
in SMSS 2014 Thanks to Ellesedil for commenting - Select
data only
- Click on 'Ok' to close the advanced script options window
- 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.
How do I export a table's data into INSERT statements?
Check out the SSMS Tool Pack - it's a great, FREE add-on for SQL Server Management Studio which does a lot of things - among other it can generate INSERT statements from a given table.
Generate insert script for selected records?
If you are using the SQL Management Studio, you can right click your DB name and select
Tasks > Import/Export data and follow the wizard.
one of the steps is called "Specify Table Copy or Query" where there is an option to write a query to specify the data to transfer, so you can simply specify the following query:
select * from [Table] where Fk_CompanyId = 1
Tool to export result set from SQL to Insert statements?
take a look at the SSMS Tools Pack add in for SSMS which allows you to do just what you need.
Generate insert script for exporting SPECIFIC data
- As I was writing this question, a collegue of mine got me the answer. Since I didnt find this on here before I'm still going to ask the question and answer it myself.
This simple query did the trick for me:
select 'insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (' + RTRIM(column1) + ', ' + RTRIM(column2) + ', ' + '''' + RTRIM(varCharColumn3) + '''' + ', ' + '''' + RTRIM(varCharColumn4) + '''' + ', ' + RTRIM(column5) + ');'
from table_name
where
column5 = 4
and column2 = 4
Some thing's that might be unclear:
- at some points in the
select-part
there's four'
in a row. That is because the data in the column is a varchar and since the escape character for'
is'
it forces me to write''''
to specify'
around my values.
The output of above query looks like this:
insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (1, 4, 'varCharValue1', 'varCharValue2', 4);
insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (2, 4, 'varCharValue1', 'varCharValue2', 4);
insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (3, 4, 'varCharValue1', 'varCharValue2', 4);
insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (4, 4, 'varCharValue1', 'varCharValue2', 4);
insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (5, 4, 'varCharValue1', 'varCharValue2', 4);
insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (6, 4, 'varCharValue1', 'varCharValue2', 4);
insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (7, 4, 'varCharValue1', 'varCharValue2', 4);
insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (8, 4, 'varCharValue1', 'varCharValue2', 4);
Azure SQL DB - Export data from a DB and insert into another DB?
There is no way to do this in only SSMS. If this is an ad-hoc project, I would query the records, copy and paste them into Excel, configure them in Excel for an insert statement, then paste them into an insert statement against DB-1.
If this is something that will need to be sustainable, I'd recommend looking into Azure Data Factory.
Related Topics
Constraint Defined Deferrable Initially Immediate Is Still Deferred
A Beginner'S Guide to SQL Database Design
MySQL Search and Replace Some Text in a Field
Difference Between Natural Join and Inner Join
Updating Table Rows in Postgres Using Subquery
Storing Money in a Decimal Column - What Precision and Scale
How to Store a List in a Column of a Database Table
SQL Query to Insert Datetime in SQL Server
Simple Random Samples from a SQL Database
What's the Best Way to Join on the Same Table Twice
Parameterized Queries with Rodbc
How to Create a Cron Job to Run an Postgres SQL Function
Line Count with in the Text Files Having Multiple Lines and Single Lines
Union Query With Codeigniter'S Active Record Pattern
How to Use (Install) Dblink in Postgresql
Group by to Combine/Concat a Column