CODEWITHSUNDEEP
sqlsql-server
Manish Vadher
Manish Vadher

Reputation: 1546

Insert data from one database table to other database table in sql server

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?

Upvotes: 1

Views: 115

Answers (1)

John Pasquet
John Pasquet

Reputation: 1842

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.

Upvotes: 1

Related Questions