Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to extract table data into a script .sql file

Posted on 2008-10-30
4
Medium Priority
?
2,306 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 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

885 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