Solved

how to extract table data into a script .sql file

Posted on 2008-10-30
4
2,295 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL - How to use like '%%' operator to search an integer 2 25
SQL Help 27 43
sql 2014,  lock limit 5 30
Linked Server Issue with SQL2012 3 24
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

785 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