Data Transfer From One Database To Another

Ok, so thank you first off to Arnie for helping with my auto-increment problem. I'm not in the office now, but I can't wait to try out what he showed me to get this thing running.



My bigger problem that I'm hoping you guys can help me with though is this:

I have two servers, and two databases, lets call them dev, and staging. These are exactly the same, except in different physical locations, and changes are made on dev, and then the info is updated to staging. I am writing a C# script to automate the process of updating the information from the dev database on the dev server, to staging. I am profficient with C#, but this is the first time i've ever touched SQL.



So by using the INSERT and SELECT method, I can get the info over properly, I am assuming (as I said I haven't been in the office to try it out yet) but for each table on the database there are different sets of column names, so I can't do a simple

Loop{

SqlCommand del = new SqlCommand("Truncate table " +MyTable", connectionstring)

del.ExecuteNonQuery();

SqlCommand com = new SqlCommand("INSERT INTO MyTable (MyColumn)

SELECT MyOtherColumn FROM MyOtherTable", connectionstring);

com.ExecuteNonQuery();

}While(...) and change the MyTable/MyOtherTable values for each loop.



Is there a quick and easy way of calling a list of columns for each table, so that I can change the table value, get the list of columns, and assign those as a variable? That way it can run through every table on the database, and no matter what the number or names of the columns in each table, it will grab all the necessary data and pass it over.



Thanks in advance,

Aaron L