Exporting data from a SELECT Statement

Question

I'm using SSMS 2018, and i want to export specific data from a table as INSERT statement in a sql file.

I tried the generate Script option but it exports all the data from the table, what I need is to export a very specific data returned from a select query.

ex: EXPORT [Select * From Table WHERE CONDITION] >> script.sql

Quickest way would be to `SELECT INTO` a new table, then script that. If you don't actually need it as queries but just for importing/exporting somewhere else, consider that SSMS also has a data import/export wizard if you can reach both servers. If not, there's `bcp` for bulk copying in a format that's far more efficient than statements. — Jeroen Mostert, Apr 19 '21 at 16:15
This is aimed at showing users how to post their data in well formatted DDL and DML statements for asking a question, but it's pretty applicable here too: [How to post a T-SQL question on a public forum](https://www.sqlservercentral.com/blogs/how-to-post-a-t-sql-question-on-a-public-forum) — Thom A, Apr 19 '21 at 16:58

score 0 · Answer 1 · answered Apr 20 '21 at 10:32

0

you can use something like this

SELECT CONCAT(
'INSERT INTO table_name (col1,col2,col3) VALUES (', col1 , ',', col2 , ',', col3 , ');'
)
FROM table_name
WHERE CONDITION

answered Apr 20 '21 at 10:32

Marian Nasry

821
9
22

score 0 · Answer 2 · answered Apr 26 '21 at 08:48

try this:

SELECT * INTO #x FROM TableName where CONDITION
EXEC ConvertQueryToInsert '#x', 'TableName'

first create ConvertQueryToInsert sp in your database

CREATE PROCEDURE dbo.ConvertQueryToInsert (@input NVARCHAR(max), @target NVARCHAR(max)) AS BEGIN

    DECLARE @fields NVARCHAR(max);
    DECLARE @select NVARCHAR(max);

    -- Get the defintion from sys.columns and assemble a string with the fields/transformations for the dynamic query
    SELECT
        @fields = COALESCE(@fields + ', ', '') + '[' + name +']', 
        @select = COALESCE(@select + ', ', '') + ''''''' + ISNULL(CAST([' + name + '] AS NVARCHAR(max)), ''NULL'')+'''''''
    FROM tempdb.sys.columns 
    WHERE [object_id] = OBJECT_ID(N'tempdb..'+@input);

    -- Run the a dynamic query with the fields from @select into a new temp table
    CREATE TABLE #ConvertQueryToInsertTemp (strings nvarchar(max))
    DECLARE @stmt NVARCHAR(max) = 'INSERT INTO #ConvertQueryToInsertTemp SELECT '''+ @select + ''' AS [strings] FROM '+@input
    exec sp_executesql @stmt

    -- Output the final insert statement 
    SELECT 'INSERT INTO ' + @target + ' (' + @fields + ') VALUES (' + REPLACE(strings, '''NULL''', 'NULL') +')' FROM #ConvertQueryToInsertTemp

    -- Clean up temp tables
    DROP TABLE #ConvertQueryToInsertTemp
    SET @stmt = 'DROP TABLE ' + @input
    exec sp_executesql @stmt
END

Exporting data from a SELECT Statement

2 Answers2