export/import between DASP databases

Is there any way yet to export data from one DASP SQL Server 2008 database and import into another DASP 2008 database via stored procedures or asp.net code? (in other words, without having to manually intervene). Thanks for any help!

 

What type of export/import are we talking about here?
An entire database? Specific tables? Specific rows? Granular down to column level within rows?

All of the above are of course possible with custom code running in a web application hosted on the DASP platform or code executing remotely with a connection to the databases over the internet.

 

Specific rows. I have one database that holds our very detailed records. I want to summarize that data and export it to another database for reporting. I'd love any help I can get with this! Thanks Joe -

 

I could post a code sample here right now but I think it's probably going to be more useful for you if I post a relevant sample that uses your real source and target db schemas.

If you can, please let me know what the source tables and target tables look like, how you need the data transformed and I'll see what I can come up with. Some sample data would be useful too.

When I ask what the source and target tables actually look like, this is the kind of thing I'm asking for:

Code:

CREATE TABLE [dbo].[SourceTbl](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[SourceField1] [varchar](50) NOT NULL,
	[SourceField2] [varchar](50) NOT NULL,
	[SourceField3] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TargetTbl](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[TargetField1] [varchar](150) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[SourceTbl] (SourceField1, SourceField2, SourceField3) values ('Row1Field1', 'Row1Field3', 'Row1Field3')
INSERT INTO [dbo].[SourceTbl] (SourceField1, SourceField2, SourceField3) values ('Row2Field1', 'Row2Field3', 'Row2Field3')
INSERT INTO [dbo].[SourceTbl] (SourceField1, SourceField2, SourceField3) values ('Row3Field1', 'Row3Field3', 'Row3Field3')
INSERT INTO [dbo].[SourceTbl] (SourceField1, SourceField2, SourceField3) values ('Row4Field1', 'Row4Field3', 'Row4Field3')
INSERT INTO [dbo].[SourceTbl] (SourceField1, SourceField2, SourceField3) values ('Row5Field1', 'Row5Field3', 'Row5Field3')

 

The query I would need to use it pretty complicated, but most of the information comes from this table. This table holds charge information for phone bills. I have to match columns (foreign keys) in that table to other tables to get some other data like whose phone it is, what account it is billed to, etc... I need to be able to run that query and export the results to another database. I usually push the query results into a temp table. I basically need to take that temp table and export it to another database.

I probably just need your code samples, but if it helps you, below is the source (charge) table and below that the target table information. Thanks so much for any help!:

GO
/****** Object: Table [dbo].[tbCharge] Script Date: 03/16/2010 15:38:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbCharge](
[ChargeID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NULL,
[VendorID] [int] NULL,
[AccountID] [int] NULL,
[SubAccountID] [int] NULL,
[ThirdLevelID] [int] NULL,
[CircuitID] [int] NULL,
[BillMonth] [smalldatetime] NULL,
[ServiceCategory] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServiceDescription] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Quantity] [int] NULL,
[ItemRate] [money] NULL,
[Minutes] [float] NULL,
[KBUsed] [int] NULL,
[NumberOfCalls] [int] NULL,
[Discount] [money] NULL,
[ProposedAmount] [money] NULL,
[Notes] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COPYChargeID] [int] NULL,
[CreatedBy] [int] NULL,
[CreatedDT] [smalldatetime] NULL,
[UpdatedBy] [int] NULL,
[UpdatedDT] [smalldatetime] NULL,
CONSTRAINT [PK_tbCharge] PRIMARY KEY CLUSTERED
(
[ChargeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tbCharge] WITH CHECK ADD CONSTRAINT [FK_tbCharge_tbCircuit] FOREIGN KEY([CircuitID])
REFERENCES [dbo].[tbCircuit] ([CircuitID])
GO
ALTER TABLE [dbo].[tbCharge] CHECK CONSTRAINT [FK_tbCharge_tbCircuit]

Here is the target table:
GO
/****** Object: Table [dbo].[tbChargeDetail] Script Date: 03/16/2010 15:38:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbChargeDetail](
[ChargeID] [int] IDENTITY(1,1) NOT NULL,
[VendorName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AccountName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PhoneUserName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillMonth] [smalldatetime] NULL,
[ServiceCategory] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServiceDescription] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Quantity] [int] NULL,
[ItemRate] [money] NULL

 

Try this. I've tested it and it worked from my local database directly to my DASP hosted db. It will work either remotely over the internet or hosted in a web page depending on what you need to do.

Code:

        string sourceConnStr = "Data Source=(local);Initial Catalog=Test;Integrated Security=True";
        string destinationConnStr = "Data Source=tcp:esql2k801.discountasp.net;Initial Catalog=yourTargetDB;User ID=yourUID;Password=yourPWD;";

        void PerformExport()
        {
            using (SqlConnection sourceConn = new SqlConnection(sourceConnStr))
            {
                using (SqlConnection destinationConn = new SqlConnection(destinationConnStr))
                {
                    // Clean up the destination table if necessary
                    SqlCommand cmd = new SqlCommand("DELETE FROM tbChargeDetail", destinationConn);
                    destinationConn.Open();
                    cmd.ExecuteNonQuery();

                    // Select some data from source table(s) using whatever SQL joins you need 
                    //The only actual column that comes from the source table in the select statement below is the quantity column implemented as a test
                    //All of the other columns are simply hardcoded aliased values for the test
                    cmd = new SqlCommand("SELECT 0 ChargeId, 'A vendor' VendorName, 'MyAccount' AccountName, 'Me' PhoneUserName, GETDATE() BillMonth, 'A category' ServiceCategory, 'A description' ServiceDescription, Quantity, 0 ItemRate FROM tbCharge", sourceConn);
                    sourceConn.Open();
                    // Execute the select statement into the source reader
                    using (SqlDataReader sourceRdr = cmd.ExecuteReader())
                    {
                        if (sourceRdr.HasRows)
                        {
                            // Create a SqlBulkCopy targetting the destination table
                            using (SqlBulkCopy bulkData = new SqlBulkCopy(destinationConn))
                            {
                                // Set destination table name
                                bulkData.DestinationTableName = "tbChargeDetail";

                                try
                                {
                                    //write the source to the target
                                    bulkData.WriteToServer(sourceRdr);
                                }
                                finally
                                {
                                    sourceRdr.Close();
                                }
                            }
                        }
                    }
                }
            }
        }

 

Joe - you are the best It worked. I'm posting what I did in vb.net (just a little test) so that if someone else needs that code, they have it.

Protected Sub btnTransfer_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnTransfer.Click
Dim cmd As SqlCommand
Dim dr As SqlDataReader


Try
cmd = New SqlCommand()
With cmd
' Create a Connection object
.Connection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("DestinationConn").ConnectionString)
' Open the Connection
.Connection.Open()

' Fill in command text, set type
.CommandText = "DELETE FROM zTestTable;"

' Open the Connection
.ExecuteNonQuery()

.Dispose()



End With

cmd = New SqlCommand()
Dim destinationConnection As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("DestinationConn").ConnectionString)
destinationConnection.Open()

With cmd
' Create a Connection object
.Connection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("TEMSSCon").ConnectionString)

' Fill in command text, set type
.CommandText = "SELECT VendorName from tbVendor"

.CommandType = CommandType.Text

' Open the Connection
.Connection.Open()


End With
' Execute SQL to dataread
dr = cmd.ExecuteReader()
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(destinationConnection)
bulkCopy.DestinationTableName = "dbo.zTestTable"


' Write from the source to the destination.
bulkCopy.WriteToServer(dr)

End Using


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.
dr.Close()

End Try

End Sub

 

I am impressed.

 

Great, sorted.