Using Sql Server 2005 Express and Management Studio I need to createa SQL insert statement for the contents of a table (FullDocuments) sothat I can run the query on another server with that same table schema(FullDocuments) and the contents will automatically be inserted intothe new instance of the FullDocuments table.
In Management StudioI have used "Script Table as" for the create table query. Thesecond instance of FullDocuments has been created on the remoteserver. Now how do I generate an insert query for the contents ofFullDocuments so that the contents can be moved/inserted to the newinstance of the table?
Thanks for any help provided.
Why not just do something like :
insert into fulldocuments2
select * from server1.fulldocuments1
|||
Hi Partha. Thanks for the suggestion.
The problem is that the fulldocuments2 is on a web server whilefulldocuments1 is on my local machine which does not allow me to uploada table or db directly. I need to transfer the data by SQL querybut don't how to generate a sql insert statement that contains all thedata from fulldocuments1 without manually typing it. For smalldata inserts I can do that. But this time it involves thousandsof lines of text, so I am hoping for a simple automated way to createthe sql insert statement to move the data.
|||
Just export the data in fulldocuments1 to a flat file and import into fulldocuments2 using Export Import wizard
|||
I can't find the Export Import Wizard. I am using the Express version of SQL Server 2005 and Management Studio.
|||
use bcp to copy out the data and copy it back in the web server
http://msdn2.microsoft.com/en-us/library/ms162802.aspx
|||
The web server admin to which i need to import data only allows dataimport via Query Analyzer (hence sql insert statement) or via CSVfile. Since I am having no luck generating a sql insert statementthat contains the data, it looks like I need to focus on how to createa CSV file with the data content. I have been studying the BCPdocumentation from your link, but don't see any means for generating aCSV file using BCP.
What are my options for creating a CSV file containing the data from the source table?
|||
See this link for an example of bcp to create csv file
http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/
|||
Consider writing a very short app (20 lines or so) using SqlBulkCopy. It's incredibly easy and incredibly fast.
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
http://davidhayden.com/blog/dave/archive/2006/01/13/2692.aspx
|||
Hi Buddha. Thanks for the links. The first one uses VB so I have tried that for my app. I like theconcept of uploading directly to the web server db but am getting thiserror message when I click the button to upload the data:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Iam a novice at this and realize I am bumping up against a securityissue, but don't know how to solve it. Any help would beappreciated. Here is the VB code for my app:
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Dim connectionString1 As String = "Data Source=_x_connection string to local db_x_"
Dim connectionString2 As String = "Data Source=_x_connection string to web server db_x_"
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
' Open a connection to the source database.
Using sourceConnection As SqlConnection = _
New SqlConnection(connectionString1)
sourceConnection.Open()
' Perform an initial count on the destination table.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.Category;", _
sourceConnection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Get data from the source table as a SqlDataReader.
Dim commandSourceData As SqlCommand = New SqlCommand( _
"SELECT * " & _
"FROM Category;", sourceConnection)
Dim reader As SqlDataReader = commandSourceData.ExecuteReader
' Open the destination connection.
Using destinationConnection As SqlConnection = _
New SqlConnection(connectionString2)
destinationConnection.Open()
' Set up the bulk copy object.
' The column positions in the source data reader
' match the column positions in the destination table,
' so there is no need to map columns.
Using bulkCopy As SqlBulkCopy = _
New SqlBulkCopy(destinationConnection)
bulkCopy.DestinationTableName = _
"Category"
Try
' Write from the source to the destination.
bulkCopy.WriteToServer(reader)
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
' Close the SqlDataReader. The SqlBulkCopy
' object is automatically closed at the end
' of the Using block.
reader.Close()
End Try
End Using
' Perform a final count on the destination table
' to see how many rows were added.
Dim countEnd As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Ending row count = {0}", countEnd)
Console.WriteLine("{0} rows were added.", countEnd - countStart)
Console.WriteLine("Press Enter to finish.")
Console.ReadLine()
End Using
End Using
End Sub
End Class
|||
Although I would have liked to have gotten the app approach to work so that I could learn more, the practical solution to my problem is the free Database Publishing Wizard provided by Microsoft. Amongst the various options it provides is one that creates a sql file with complete insert query for all the data in the db, etc. Thanks for the input which has gotten me thinking about new possibilities for future projects.
