Solved

how to extract table data into a script .sql file

Posted on 2008-10-30
4
2,297 Views
Last Modified: 2012-05-05
Hi,
I am hoping to find a free utility that allows me to select a table form my 2005 db and then extract the data to a .sql file with the complete INSERT statement for each row.

thanks
0
Comment
Question by:CraigLazar
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22839212
there are some tools like this
i once wrote a script to do it
it needs a little work but you can try it
you just set the table name and run the query
you will get another query as a result
run it and you're done
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 22839251
0
 
LVL 1

Accepted Solution

by:
tomtown earned 250 total points
ID: 22839396
You need to create a stored procedure...
Here's how to do:

Execute the attached code on your DB:

To use the stored proc simply call it passing the Table Name
e.g.
    Exec dbo.ScriptInsertStatements 'MyTableName'

 
http://anastasiosyal.com/archive/2007/04/25/5.aspx
CREATE Proc dbo.ScriptInsertStatements(
      @TableName varchar(128),
      @WhereClause varchar(400) = Null
 
)
AS
 
DECLARE @sql varchar(Max)
DECLARE @sqlColumns varchar(Max)
DECLARE @SqlColumnValues varchar(Max)
 
SELECT @sqlColumns = Coalesce(@SqlColumns + ',','') + Column_Name from Information_Schema.Columns where Table_name = @TableName
 
SELECT @SqlColumnValues = Coalesce(@SqlColumnValues + '+  '','' + ','') +
      CASE
            WHEN Data_Type in ('varchar','nvarchar', 'char', 'nchar', 'datetime' ) THEN  'QuoteName(' + Column_Name + ', '''''''')'
            ELSE 'Cast(' + Column_Name + ' as varchar(MAX))'
      END
FROM
      Information_Schema.Columns where Table_name = @TableName
 
 
SELECT @sql = 'Select ''Insert Into ' + @TableName + ' (' + @SqlColumns + ') Values ('' + ' + @SqlColumnValues +' + '')'' FROM ' + @TableName + IsNull(' WHERE ' + @WhereClause, '')
 
Exec(@sql)

Open in new window

0
 
LVL 4

Author Closing Comment

by:CraigLazar
ID: 31511584
thanks
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Union 2 queries to a cte (temp table perhaps) 9 41
SQL Server 2012 rs - Field1 and Field2 add 1 to Total 4 21
export sql results to csv 6 41
Webservices in T-SQL 3 34
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question