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...

Nenad Zivkovic · Accepted Answer · 2013-03-27T08:31:01.720

11

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

edited Mar 27 '13 at 08:31

answered Mar 27 '13 at 08:17

Nenad Zivkovic

18,221
6
42
55

Wow, I never tried the Import/Export wizad before. Thanks Mister @NenadZivkovic – Jeancarlo Fontalvo Jan 07 '17 at 06:15

score 6 · Answer 2 · answered Mar 27 '13 at 08:12

6

If both databases are on one server, you can just do like this:

insert into [db1name].[dbo].[Countries] (Id, Name)
select CountryId, CountryName
from [db2name].[dbo].[Countries]
where _your where clause_

Hope this helps

answered Mar 27 '13 at 08:12

serejja

22,901
6
64
72

Aleksandr Fedorenko · Answer 3 · 2013-03-27T08:21:29.593

5

Use simple INSERT statement (database_name.[schema_name].table)

INSERT [NewDB].[your_schema].[Countries](Id,Name)
SELECT CountryId, CountryName
FROM [OldDB].[your_schema].[Country]

edited Mar 27 '13 at 08:21

answered Mar 27 '13 at 08:10

Aleksandr Fedorenko

16,594
6
37
44

score 3 · Answer 4 · answered Mar 27 '13 at 08:24

To be honest I do not really get the queries that you wrote. Are you trying to build strings from your queries that you then pass again to your database?

You can just pass your values from one database to the other in one query:

/*
    maybe you need to switch off identity on your target table
    to get your original id values into the target table like this:
    (without comment ofc ;))
*/
--SET IDENTITY_INSERT TargetDatabase.dbo.Countries ON

INSERT INTO TargetDatabase.dbo.Countries (Id, Name)
    SELECT
            CountryId, CountryName
        FROM SourceDatabase.dbo.Country

--SET IDENTITY_INSERT TargetDatabase.dbo.Countries OFF

Or you can use a temporary table and switch the database connection after retrieving your original values.

USE SourceDatabase

DECLARE @TempTable TABLE (CountryId INT PRIMARY KEY, CountryName NVARCHAR(MAX))

INSERT INTO @TempTable (CountryId, CountryName)
    SELECT
            CountryId, CountryName
        FROM Country

USE TargetDatabase

/*
    maybe you need to switch off identity on your target table
    to get your original id values into the target table like this:
    (without comment ofc ;))
*/
--SET IDENTITY_INSERT Countries ON

INSERT INTO Countries (Id, Name)
    SELECT
            CountryId, CountryName
        FROM @TempTable

--SET IDENTITY_INSERT Countries OFF

EDIT: as a previous poster mentioned, for this to work you need both databases on the same server, since you did not say anything about that i just assumed that that was the case? :D

Insert data from db to another db

4 Answers4

Linked