将数据从一个数据库表复制到另一个数据库表的SQL代码

I have messed up the data. I have been ammended ids and values to the point where i cannot remember what the originals were. (testing a few things).

我把数据搞错了。我已经修改了id和值，以至于我都记不起原来是什么了。(测试几件事)。

I have a table called query_string_interpretation and its part of the DB called, test1_db

我有一个名为query_string_interpretation的表，它的DB部分名为test1_db

I have a backup database which i have restored called, test2_db

我恢复了一个名为test2_db的备份数据库

How can I restore its contents from one database table to another?

如何将其内容从一个数据库表恢复到另一个数据库表?

4 个解决方案

#1

At first, you need to be sure that you have all your data in source table, or data is placed in both tables - source and destination. In first case you need to truncate the destination table:

首先，您需要确保您的所有数据都在源表中，或者数据都放在源表和目标表中。在第一种情况下，需要截断目标表:

TRUNCATE TABLE test1_db..query_string_interpretation

Second, you need to be sure that you will insert the right values into IDENTITY fields, if these fields exists. Use SET INDENITY_INSERT ON statement. Third, you need to insert the values:

其次，您需要确保在标识字段中插入正确的值，如果这些字段存在的话。使用SET INDENITY_INSERT ON语句。第三，需要插入值:

insert into test1_db..query_string_interpretation
select * from test2_db..query_string_interpretation

And don't forget to switch the INDENITY_INSERT to OFF, you you switched it to ON in the second step.

不要忘记将INDENITY_INSERT切换为OFF，您在第二步中将它切换为ON。

#2

Without more information on your part...generally you can use INSERT INTO/SELECT FROM to move data between tables.

没有关于你的更多信息……通常，可以使用INSERT INTO/SELECT FROM在表之间移动数据。

The syntax is roughly:

大致的语法是:

INSERT INTO
test1_db..query_string_interpretation ( <your columns here>)
select <your columns here>
FROM test2_db..query_string_interpretation

#3

You can use a SQL wizard to do this for you. In Management Studio, right click on either database, select Tasks then Import Data... or Export Data...

您可以使用SQL向导为您执行此操作。在Management Studio中，右键单击任何一个数据库，选择任务，然后导入数据……或出口数据…

#4

try this:

试试这个:

delete from test1_db..query_string_interpretation

insert into test1_db..query_string_interpretation
select * from test2_db..query_string_interpretation

if you have an identity field you may have to write the name of the columns (except for the identity one). if you need to keep the IDs, take a look at the SET IDENTITY_INSERT ON statement here

如果您有一个identity字段，那么您可能需要写入列的名称(除了identity字段)。如果需要保留id，请查看这里的SET IDENTITY_INSERT ON语句

#1