easy way of creating update commands?

[cj]

I have lots of tables to copy from one server to another. The new
tables have been created to match the old ones. I practiced with one
table. I created the select command (select * from tableA) and Visual
Basic .NET created an update command--very long as there are a lot of
fields.

Now there are more large tables to copy. Is there an easy way to have
Visual Basic create these update commands itself for the other tables
with out having to set up a new dataadapter for each table? Then I
could just assign it a new select and update command and use the same
dataadapter.

 

[Jeffrey Tan[MSFT]]

Hi,

Thanks for your post.

Yes, *DataAdapter class is just a set of data commands and a database
connection that are used to fill the DataSet and update a SQL Server
database. It contains *Command objects to do the actual update/add/delete
etc.. operations.

We can reuse one *DataAdapter class to do different database operations.
After doing one SQL command, we can change it to execute another operation
like this:
*DataAdapter.UpdateCommand.CommandText ="command text"
*DataAdapter.SelectCommand.CommandText ="command text"
*DataAdapter.DeleteCommand.CommandText ="command text"
*DataAdapter.InsertCommand .CommandText ="command text"

Hope this helps

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Cor Ligthert [MVP]]

CJ,

In addition to Jeffrey.

Although from the information that you have given the idea that your
solution will not work, does your question in my opinion directly apply the
commandbuilder.

http://msdn.microsoft.com/library/d...mdatasqlclientsqlcommandbuilderclasstopic.asp

Be aware that as every wizard it will only work with tables with less than
100 fields.

I hope this helps,

Cor

 

[cj]

Hi Jeffrey, Perhaps I could have done a better job of explaining my
wishes. I have identical tables in different databases on different
servers. I wish to move all the rows from TableA on Server 1 to TableA
on Server 2. Then I want to move all the rows from tables B-Z

I got the code written for TableA Adding hand coding connection to the
source and making a sqldataadapter with the wizards for the destination.
I choose to use the wizards for the destination because unlike a
select where the statement is short and sweet (select * from TableA) the
insert statement is very long as it list all the column names twice and
there are lots of columns.

My intention was to run this same code on tables B-Z as well but I
failed to take into consideration the insert command that was created
for tableA wouldn't work on tables B-Z. I want VB to make it easy on me
and figure out it's own insert statement for tables B-Z w/o me having to
add a seperate sqldataadapter for those tables.

Is there a way to do this?

 

[cj]

See my answer to Jeffrey, Perhaps command builder is something that
would help. I don't know anything about it. I'll look at your link.
While there are a lot of fields it isn't over 100.

 

[Guest]

You might be able to write a generalized function/sub that:

Fills a dataset from your old database
Creates a table in your new database using the schema from that dataset
Fill a new dataset from the new database (will have 0 rows)
Copy the rows from the old dataset into the new dataset
Update the new dataset

Probably is a better way to do it but the above should work.

Dennis in Houston

 

[cj]

It'll take me a little playing with because I haven't worked with
creating a table using the schema from a database but that sounds like a
good idea. I'd rather work on figuring out how to do this rather than
put my time into typing in all those insert statements.

 

[Cor Ligthert [MVP]]

CJ,

If this is a one time opration, than it is probably much better to use the
tools given for that by the database.

If you really want to do it, than don't forget this

http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.acceptchangesduringfill.aspx

I hope this helps,

Cor

 

[cj]

It's kinda a one time thing. Problem is I've been asked to do this to
combine a database that is kept by each of our companies sites
individually on their own servers into one SQL server. Unfortunately I
don't really have access to the "from" databases or the servers they are
on beyond being able to query them--office politics.

Oh, I'm glad you asked that because that just made me think of another
problem. I can't really let the program create the tables on the new
server. While that'd be great as I get the tables from the first site
when I go to do the second site I'll be adding those tables's data to
tables on the SQL sever that already contain data from previous sites.

Well, I still haven't had time to look at the command builder. I'll
look at it and if I can't figure it out or it doesn't seem to be helpful
I'll have to bit the bullet an start typing those long insert commands.

 

[Cor Ligthert [MVP]]

CJ,

Well, I still haven't had time to look at the command builder. I'll look
at it and if I can't figure it out or it doesn't seem to be helpful I'll
have to bit the bullet an start typing those long insert commands.

Your explanation can be an real one, however more difficult than the insert
is create the tables on the new database (which needs the exact same names
and valuetypes).

For the insert you can probably in this case use the commandbuilder. However
than as Dennis in fact wrote already you have to be sure that the receiving
databasetable has no conflictiong rows.

Than you have in my opinion got from us all the ingredients you need.

If you than have questions after a while, ask than again. In my opinion can
you use in this the commandbuilder.

Cor

 

[Jeffrey Tan[MSFT]]

Hi,

Thanks for your clarify.

For this task, because it is just one time task, I think the simplest way
to get this done is using database DTS to do the moving work. For more
information, you may consult in database newsgroup.

If you really want to do this programmatically by .Net, yes, we can use
CommandBuilder. With CommandBuilder, we only need a Sql select command, the
CommandBuilder will generate the corresponding insert, delete and other
commands text. Something like this:

daCustomers = New SqlClient.SqlDataAdapter("Select * From Customers",
dcNorthwind)
daCustomers.Fill(DsNorthwind1, "Customers")

Dim cb As SqlClient.SqlCommandBuilder
cb = New SqlClient.SqlCommandBuilder(daCustomers)

Please search SqlCommandBuilder in MSDN for more information.

Hope this helps

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.