Generate insert script for selected records?
SqlDatabaseSql Server-2008Sql Problem Overview
I have a table with the following data:
Pk_Id ProductName Fk_CompanyId Price
------------------------------------------------------
1 AMX 1 10.00
2 ABC 1 11.00
3 APEX 1 12.00
4 AMX 1 10.00
5 ABC 1 11.00
6 APEX 1 12.00
7 AMX 2 10.00
8 ABC 2 11.00
9 APEX 2 12.00
I want to generate Insert script for migrating records whose Fk_CompanyId is 1.
There is an insert script option to generate script for all records but I want to filter some records to migrate to another database.
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
If possible use Visual Studio. The Microsoft SQL Server Data Tools (SSDT) bring a built in functionality for this since the March 2014 release:
- Open Visual Studio
- Open "View" → "SQL Server Object Explorer"
- Add a connection to your Server
- Expand the relevant database
- Expand the "Tables" folder
- Right click on relevant table
- Select "View Data" from context menu
- In the new window, viewing the data use the "Sort and filter dataset" functionality in the tool bar to apply your filter. Note that this functionality is limited and you can't write explicit SQL queries.
- After you have applied your filter and see only the data you want, click on "Script" or "Script to file" in the tool bar
- Voilà - Here you have your insert script for your filtered data
Note: Be careful, the "View Data" window is just like SSMS "Edit Top 200 Rows"- you can edit data right away
(Tested with Visual Studio 2015 with Microsoft SQL Server Data Tools (SSDT) Version 14.0.60812.0 and Microsoft SQL Server 2012)
Solution 3 - Sql
CREATE PROCEDURE sp_generate_insertscripts
(
@TABLENAME VARCHAR(MAX),
@FILTER_CONDITION VARCHAR(MAX)='' -- where TableId = 5 or some value
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TABLE_NAME VARCHAR(MAX),
@CSV_COLUMN VARCHAR(MAX),
@QUOTED_DATA VARCHAR(MAX),
@TEXT VARCHAR(MAX),
@FILTER VARCHAR(MAX)
SET @TABLE_NAME=@TABLENAME
SELECT @FILTER=@FILTER_CONDITION
SELECT @CSV_COLUMN=STUFF
(
(
SELECT ',['+ NAME +']' FROM sys.all_columns
WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND
is_identity!=1 FOR XML PATH('')
),1,1,''
)
SELECT @QUOTED_DATA=STUFF
(
(
SELECT ' ISNULL(QUOTENAME('+NAME+','+QUOTENAME('''','''''')+'),'+'''NULL'''+')+'','''+'+' FROM sys.all_columns
WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND
is_identity!=1 FOR XML PATH('')
),1,1,''
)
SELECT @TEXT='SELECT ''INSERT INTO '+@TABLE_NAME+'('+@CSV_COLUMN+')VALUES('''+'+'+SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)+'+'+''')'''+' Insert_Scripts FROM '+@TABLE_NAME + @FILTER
--SELECT @CSV_COLUMN AS CSV_COLUMN,@QUOTED_DATA AS QUOTED_DATA,@TEXT TEXT
EXECUTE (@TEXT)
SET NOCOUNT OFF
END
Solution 4 - Sql
SELECT 'INSERT SomeOtherDB.dbo.table(column1,column2,etc.)
SELECT ' + CONVERT(VARCHAR(12), Pk_Id) + ','
+ '''' + REPLACE(ProductName, '''', '''''') + ''','
+ CONVERT(VARCHAR(12), Fk_CompanyId) + ','
+ CONVERT(VARCHAR(12), Price) + ';'
FROM dbo.unspecified_table_name
WHERE Fk_CompanyId = 1;
Solution 5 - Sql
I was actually able to accomplish this in SSMS without add ins or procs in just a few steps. 1) If you don't want the whole table, select the columns and records needed into a new table in the database: SELECT col1, col2 ... INTO TempRecs 2) Use the generate scripts feature of SSMS to generate the INSERT statements by right clicking the database (Tasks > Generate Scripts > Choose Objects > Select the temp table > Click Advanced button > Change "Types of data to script" to "Data only" > Finish). 3) Do a find and replace for the resultant INSERT statements on the temp table name to the desired import table name.
Solution 6 - Sql
In SSMS execute your sql query. From the result window select all cells and copy the values. Goto [below website][1] and there you can paste the copied data and generate sql scripts. You can also save results of query from SSMS as CSV file and import the csv file in [this][2] website.
http://www.convertcsv.com/csv-to-sql.htm
[1]: http://www.convertcsv.com/csv-to-sql.htm "below website" [2]: http://www.convertcsv.com/csv-to-sql.htm "this"
Solution 7 - Sql
You could create a view with your criteria and then export the view?
Solution 8 - Sql
With the DBeaver client (which supports SQL Server) you can do a SELECT query with the records that you want and then select the resulting rows, right click and Copy as SQL, and you'll have the INSERT statement in your clipboard:
HeidiSQL also supports connecting to SQL Server and exporting selected rows (you can filter the rows in the SQL query itself, or retrieve all rows and select them in the data grid):
Solution 9 - Sql
I created the following procedure:
if object_id('tool.create_insert', 'P') is null
begin
exec('create procedure tool.create_insert as');
end;
go
alter procedure tool.create_insert(@schema varchar(200) = 'dbo',
@table varchar(200),
@where varchar(max) = null,
@top int = null,
@insert varchar(max) output)
as
begin
declare @insert_fields varchar(max),
@select varchar(max),
@error varchar(500),
@query varchar(max);
declare @values table(description varchar(max));
set nocount on;
-- Get columns
select @insert_fields = isnull(@insert_fields + ', ', '') + c.name,
@select = case type_name(c.system_type_id)
when 'varchar' then isnull(@select + ' + '', '' + ', '') + ' isnull('''''''' + cast(' + c.name + ' as varchar) + '''''''', ''null'')'
when 'datetime' then isnull(@select + ' + '', '' + ', '') + ' isnull('''''''' + convert(varchar, ' + c.name + ', 121) + '''''''', ''null'')'
else isnull(@select + ' + '', '' + ', '') + 'isnull(cast(' + c.name + ' as varchar), ''null'')'
end
from sys.columns c with(nolock)
inner join sys.tables t with(nolock) on t.object_id = c.object_id
inner join sys.schemas s with(nolock) on s.schema_id = t.schema_id
where s.name = @schema
and t.name = @table;
-- If there's no columns...
if @insert_fields is null or @select is null
begin
set @error = 'There''s no ' + @schema + '.' + @table + ' inside the target database.';
raiserror(@error, 16, 1);
return;
end;
set @insert_fields = 'insert into ' + @schema + '.' + @table + '(' + @insert_fields + ')';
if isnull(@where, '') <> '' and charindex('where', ltrim(rtrim(@where))) < 1
begin
set @where = 'where ' + @where;
end
else
begin
set @where = '';
end;
set @query = 'select ' + isnull('top(' + cast(@top as varchar) + ')', '') + @select + ' from ' + @schema + '.' + @table + ' with (nolock) ' + @where;
insert into @values(description)
exec(@query);
set @insert = isnull(@insert + char(10), '') + '--' + upper(@schema + '.' + @table);
select @insert = @insert + char(10) + @insert_fields + char(10) + 'values(' + v.description + ');' + char(10) + 'go' + char(10)
from @values v
where isnull(v.description, '') <> '';
end;
go
Then you can use it that way:
declare @insert varchar(max),
@part varchar(max),
@start int,
@end int;
set @start = 1;
exec tool.create_insert @schema = 'dbo',
@table = 'myTable',
@where = 'Fk_CompanyId = 1',
@insert = @insert output;
-- Print one line to avoid the maximum 8000 characters problem
while len(@insert) > 0
begin
set @end = charindex(char(10), @insert);
if @end = 0
begin
set @end = len(@insert) + 1;
end;
print substring(@insert, @start, @end - 1);
set @insert = substring(@insert, @end + 1, len(@insert) - @end + 1);
end;
The output would be something like that:
--DBO.MYTABLE
insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(1, 'AMX', 1, 10.00);
go
insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(2, 'ABC', 1, 11.00);
go
insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(3, 'APEX', 1, 12.00);
go
insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(4, 'AMX', 1, 10.00);
go
insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(5, 'ABC', 1, 11.00);
go
insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(6, 'APEX', 1, 12.00);
go
insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(7, 'AMX', 2, 10.00);
go
insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(8, 'ABC', 2, 11.00);
go
insert into dbo.myTable(Pk_Id, ProductName, Fk_CompanyId, Price)
values(9, 'APEX', 2, 12.00);
go
If you just want to get a range of rows, use the @top parameter as bellow:
declare @insert varchar(max),
@part varchar(max),
@start int,
@end int;
set @start = 1;
exec tool.create_insert @schema = 'dbo',
@table = 'myTable',
@top = 100,
@insert = @insert output;
-- Print one line to avoid the maximum 8000 characters problem
while len(@insert) > 0
begin
set @end = charindex(char(10), @insert);
if @end = 0
begin
set @end = len(@insert) + 1;
end;
print substring(@insert, @start, @end - 1);
set @insert = substring(@insert, @end + 1, len(@insert) - @end + 1);
end;
Solution 10 - Sql
If you are using Oracle SQL Developer then it would be
select /*insert*/ * from TABLE_NAME where COLUMN_NAME = 'VALUE';
Run this as a script
Solution 11 - Sql
If you are using MySql and MySql workbench. Here is a nice option.
- Write your select query and execute it
- You'll see the Export button
- Click on it and give a filename and in "Save as Type" you'll see "SQL Insert Statements"
This will give you insert statements
Solution 12 - Sql
Use Navicat
- Filter your data in table or write your query
- Select row and right click it > Copy AS > Select "Insert Statement" or "Update Statement"
- Paste in New Query in MS SQL Server Management Studio or other IDE
