SQL 将数据从数据库插入到另一个数据库

Insert data from db to another db

提问by PoliDev

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

我想将旧数据库表中的值转换为新数据库表。

Old db structure:

旧的数据库结构：

Table I: Country

表一： Country

• CountryId
• CountryName

• 国家编号
• 国家的名字

New db structure

新的数据库结构

Table II: Countries

表二： Countries

• Id
• Name

• ID
• 姓名

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

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

为了实现这一点，查询是什么？帮我...

回答by Nenad Zivkovic

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.

如果要传输大量数据和多个表，我建议使用 SQL Server Management Studio 提供的导入/导出向导。

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

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

回答by Aleksandr Fedorenko

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

使用简单的 INSERT 语句（database_name.[schema_name].table）

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

回答by serejja

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

希望这可以帮助

回答by DrCopyPaste

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

编辑：正如之前的海报所提到的，为此，您需要在同一台服务器上使用两个数据库，因为您没有对此发表任何评论，我只是假设情况确实如此？:D