CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-insert
PoliDev
PoliDev

Reputation: 1468

Insert data from db to another db

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

Old db structure:

Table I: Country

New db structure

Table II: Countries

I used the following insert query like,

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

But I have the result like,

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

Upvotes: 7

Views: 50962

Answers (4)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

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

Upvotes: 11

DrCopyPaste
DrCopyPaste

Reputation: 4117

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

Upvotes: 3

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

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

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

Upvotes: 5

serejja
serejja

Reputation: 23881

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

Upvotes: 6

Related Questions