Export Inserted Table Data to .Txt File in SQL Server

Export values from SQL Server to txt file

Use a query to collect the variables you want to export. Something like this:

DECLARE @var1 INTEGER
DECLARE @var2 INTEGER

SELECT @var1 = 10
SELECT @var2 = 22

SELECT 'variable 1' AS VarName, @var1 AS VarValue
UNION
SELECT 'variable 2' AS VarName, @var2 AS VarValue

Use this query statement in the following command. Use queryout and replace [querystatement] with the statement above, or use a variable for the query string.

EXEC master..XP_CMDSHELL 'bcp "[querystatement]" queryout "c:\spt_values.dat"'

If the variable needs to be declared outside the statement:

DECLARE @cmd varchar(1000)
DECLARE @sql varchar(8000) 
DECLARE @var1 int
SELECT @var1 = 10
SET @cmd='"select '+CAST(@var1 AS VARCHAR(10))+'"' 
SELECT @sql = 'bcp '+@cmd+' queryout I:\File\mytest.txt -c -t -T -S YAMUNA\SQLEXPRESS';
exec xp_cmdshell @sql;

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 create a .txt file and insert data into it in SQL?

CREATE EVENT createFile 
ON SCHEDULE 
    EVERY 1 DAY
    STARTS CURRENT_DATE + INTERVAL '11:30' HOUR_MINUTE
ENABLE
DO
SELECT *
    INTO OUTFILE 'drive:\\folder\\filename.ext'
    FROM updated_movie
    WHERE created_at >= NOW() - INTERVAL 1 DAY;

Modify conditions, output expressions, add export specifications if needed.

Check secure_file_priv setting and related ones, and justify destination forder accordingly. FILE privilege needed.

PS. BEGIN-END, DELIMITER, etc. - are excess.

---

Related Topics