Solved

how to extract table data into a script .sql file

Posted on 2008-10-30
4
2,290 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
Comment Utility
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
Comment Utility
0
 
LVL 1

Accepted Solution

by:
tomtown earned 250 total points
Comment Utility
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
Comment Utility
thanks
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard 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.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now