I have two databases with exactly the same schema. I want to export data from one and replicate it on the other. I don't wish to drop and recreate tables, just zap all the data in the target db and replace it with the data exported from the source database.
I assume this is possible? The target database is on a client environment with very limited permissions.
You can use SQL Server Import and Export Wizard to import the data. The SQL Server Import and Export Wizard can copy data to and from database.
You could use an SSIS package for it, and there are products out there that will do it for you. I've used both a specific product for it (SQL Data Compare by Red Gate) and a couple of versions of source control (although for the latter, really only for "static" look up data).
Even those options are usually only doing the scripting out of INSERTS, etc. for you.
You specifically use the word "replicate"; have you looked into doing actual snapshot replication between your databases:
http://technet.microsoft.com/en-us/library/ms151734%28v=sql.105%29.aspx
That's also not perfect per your question, though, I'm afraid.
You have options, but sadly none as simple as just doing RESTORE DATABASE
I'm not sure in sql. But you can use 3rd application like php etc to perform data transfer. Fist colect all data from the table. For each data in table, do lookup in target table. If the target table contain data that same with current source data, do update. Else, create new one. Maybe something like this in PHP:
$source = mysql_query("SELECT * FROM source_table");
while ($row = mysql_fetch_assoc($source)) {
$target = mysql_query("SELECT * FROM target_table WHERE id='" . $row["id"] . "'");
$tr_row = mysql_num_rows($target);
if ($tr_row > 0) {
$result = mysql_query("UPDATE target_table SET name='" . $row["name"] . "' age='" . $row["age"] ."' WHERE id='" . $row["id"] . "'");
} else if ($tr_row <= 0) {
$result = mysql_query("INSERT INTO target_table (name,age) VALUES ('" . $row["name"] . "','" . $row["age"] . "'");
}
}
This is just sample code in PHP and MySQL. Hope this help. :)
If you can link two database servers then you can do this from SSMS using INSERT with SELECT statements.
Here is an article that describes linked servers in more detail.
