Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to extract table data into a script .sql file

Posted on 2008-10-30
4
Medium Priority
?
2,304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

721 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