Migrate some SQL server tables to new database

Question

0.00/5 (No votes)

See more:

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.

Posted 29-Apr-18 5:11am

Christopher Fernandes

Updated 29-Apr-18 5:19am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2018-04-29T05:20:00

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[^]