将一些SQL Server表迁移到新数据库 [英] Migrate some SQL server tables to new database

问题描述

我在SQL Server 2008 EXPRESS Advanced中有一个名为CommonApps的数据库。目前，两个应用程序将其数据存储在此数据库中。我这样做是因为我不想为所有用户创建2个配置文件来访问这2个应用程序。现在解决任何问题已经变得很忙，因为两个应用程序都会在两个应用程序出现任何问题时脱机。每个应用程序在各个表中都有接近50万条记录。

CommonApps DB中的一些imp表列表（每个表也有历史记录表）

1.）EmployeeDetails（今天有744名员工）

2.）EmployeeLoginDetails

3.）DailyTask

4.）LeavesTracker

5.）DailyTaskMaster

6.）DailyTaskMasterMetric





我要复制来自表格（1和2）的数据将第二个应用程序的表格（3,5和6）移动到名为CrossPoint的新数据库，而不会遇到IDENTITY列问题。



我应该如何继续这个？

一个是Intranet IIS服务器上的ASP.Net 3.5 Web应用程序

其他是Winforms仅在网络上运行的应用程序， 不在它之外。



我的尝试：



我试图通过从Common使用无数据脚本来创建从CommonApps DB到CrossPoint DB的表结构应用程序，然后尝试运行select * into查询，但它失败，因为表中有50万条记录没有3.

I have a database named CommonApps in SQL Server 2008 EXPRESS Advanced. Currently, two apps store their data in this DB. I did this because I did not want to create 2 profiles for all users for accessing these 2 apps. Now it has become hectic to troubleshoot any issues as both apps go offline in case of any issues that arise in both apps. Each of the apps has close to half a million records in the individual tables.
Some imp Tables List in the CommonApps DB(each table has history log table as well)
1.) EmployeeDetails (744 employees as on today)
2.) EmployeeLoginDetails
3.) DailyTask
4.) LeavesTracker
5.) DailyTaskMaster
6.) DailyTaskMasterMetric


I want to copy the data from tables(1 & 2) move the tables(3,5 & 6) of the second app to a new DB named CrossPoint without running into IDENTITY column issues.

How should I go ahead with this?
One is ASP.Net 3.5 Web App on the Intranet IIS Server
Other is Winforms Apps that only runs on the network, not outside it.

What I have tried:

I tried to create the table structure from the CommonApps DB to the CrossPoint DB by taking a no-data script from the CommonApps and then tried running the select * into query but it fails as there a half a million records in the table no 3.

推荐答案

一种简单的方法是使用INSERT INTO ... SELECT FROM查询将相关数据从数据库A复制到数据库B.成功复制后，只需删除源行。



为了避免身份问题使用 SET IDENTITY_INSERT（Transact-SQL）| Microsoft Docs [ ^ ]



此外，您可能需要在新版本中重新标记身份值数据库使用 DBCC CHECKIDENT（Transact-SQL）| Microsoft Docs [ ^ ]

A simple way would be to use INSERT INTO...SELECT FROM query to copy the relevant data from database A to database B. After a successful copy, just delete the source rows.

In order to avoid identity problems use SET IDENTITY_INSERT (Transact-SQL) | Microsoft Docs[^]

Also you probably need to re-seed the identity values in the new database using DBCC CHECKIDENT (Transact-SQL) | Microsoft Docs[^]

这篇关于将一些SQL Server表迁移到新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！