I have Two Database with same table structure.
One is in local SQL on my computer and other is on Live SQL Database.
Now i want to insert Live Database Table data to local database table.
Any Idea?
4
-
2Linked servers? OpenRowset? these are the first 2 options that pops to my mindZohar Peled– Zohar Peled2017-04-28 16:34:45 +00:00Commented Apr 28, 2017 at 16:34
-
1You can use the data import/export tool or setup a link server and use an insert statement. Or even OpenRowsetSean Lange– Sean Lange2017-04-28 16:34:46 +00:00Commented Apr 28, 2017 at 16:34
-
i dont have VPS. i used my godaddy server.Manish Vadher– Manish Vadher2017-04-28 16:40:27 +00:00Commented Apr 28, 2017 at 16:40
-
If they're on the same physical SQL Server, just copy from one database to another. If they are on a separate instance of SQL Server, just link the servers, and then copy from one server.database to another.pmbAustin– pmbAustin2017-04-28 16:43:03 +00:00Commented Apr 28, 2017 at 16:43
Add a comment
|
1 Answer
Of course, you can use the DATA IMPORT functionality. However, there is another fairly easy way to do it, provided you're not talking millions of rows.
Basically you run a SELECT query on the table you want to pull from that creates an INSERT statement for each row:
SELECT 'INSERT INTO MyTable
(col1, col2, col3)
VALUES (
''' + CAST(col1 AS VARCHAR(25)) + ''',
''' + CAST(col2 AS VARCHAR(25)) + ''',
''' + CAST(col3 AS VARCHAR(25)) + ''''
+ ')'
FROM MyTable
The result set will look like this:
INSERT INTO MyTable (col1, col2, col3, col4) VALUES ('val1','val2','val3')
INSERT INTO MyTable (col1, col2, col3, col4) VALUES ('val1','val2','val3')
INSERT INTO MyTable (col1, col2, col3, col4) VALUES ('val1','val2','val3')
You can add IDENTITY INSERT as well, of course.
So, just copy the result set into a QUERY window for the test server and execute it. I've done this many times myself when I'm copying from multiple tables.
I will add that sometimes this is easier (for smaller datasets) than trying to figure out how to link the servers.
