Insert data from db to another db

Question

I want to take values from my old database tables to new database tables.

Old db structure:

Table I: Country

• CountryId
• CountryName

New db structure

Table II: Countries

• Id
• Name

I used the following insert query like,

select 'insert into Countries (Id, Name) select ', countryid, countryname from Country

But I have the result like,

• insert into Countries(Id,Name) select 1 India
• insert into Countries(Id,Name) select 2 Any Country

like that.

but I need the result like,

insert into Countries (Id, Name) values (1, 'India')

To achieve this, what is the query? help me...

Answer 1

If there is a lot of data to transfer and multiple tables, I would suggest using Import/Export wizard provided by SQL Server Management Studio.

http://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/

Edit: However, if there is not lot of data and the two systems are not connected - and you need to generate script to transfer data, your query should look like this:

SELECT 'INSERT INTO Countries (Id, Name) VALUES (' + CAST(countryid AS VARCHAR(50)) + ', ''' + countryname + ''')' from Country