Transfer Data from One Database to Another Database

Transfer data from one database to another database

There are several ways to do this, below are two options:

Option 1
- Right click on the database you want to copy

• Choose 'Tasks' > 'Generate scripts'

• 'Select specific database objects'

• Check 'Tables'

• Mark 'Save to new query window'

• Click 'Advanced'

• Set 'Types of data to script' to 'Schema and data'

• Next, Next

You can now run the generated query on the new database.

Option 2

• Right click on the database you want to copy

• 'Tasks' > 'Export Data'

• Next, Next

• Choose the database to copy the tables to

• Mark 'Copy data from one or more tables or views'

• Choose the tables you want to copy

• Finish

Transfer data from one database to another database with different schema

I'll take a stab at it even if I am far from an expert on SQLServer - here is a general procedure (you will have to repeat it for all tables where you have to replace INT with UID, of course...).
I will use Table A to refer to the parent (Firm, if I understand your example clearly) and Table B to refer to the child (Client, I believe).

1. Delete the relations pointing to Table A
2. Remove the identity from the id column of Table A
3. Create a new column with Uniqueidentifier on Table A
4. Generate values for the Uniqueidentifier column
5. Add the new Uniqueidentifier column in all the child tables (Table B)
6. Use the OLD id column to map your child record & update the new Uniqueidentifier value from your parent table.
7. Drop all the id columns
8. Recreate the relations

Having said that, I just want to add a warning to you: converting to UID is, according to some, a very bad idea. But if you really need to do that, you can script (and test) the above mentioned procedure.

Copy Data from a table in one Database to another separate database

SELECT ... INTO creates a new table. You'll need to use INSERT. Also, you have the database and owner names reversed.

INSERT INTO DB1.dbo.TempTable
SELECT * FROM DB2.dbo.TempTable

Query to transfer data from one database to another

Guessing at your names:

INSERT DB2.dbo.MyTBL2
 (DataColumn)
SELECT DataColumn
 from DB1.dbo.MyTBL1
EXCEPT SELECT DataColumn
 from DB2.dbo.MyTBL2

SELECT EXCEPT can be very powerful. More info at https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-ver15

How to transfer data from one database to another in Django?

I was able to do some minor tricks in order to solve my problem because there is not a straightforward functionality that allows you to transfer all your data from two sqlite databases in Django. Here is the trick I did:

1. Download the sqlite db browser to explore and export the contents of your old database in a .csv file. Open you database with sqlite db browser and hit on the tables option and you will see all your tables, then do a right click on any of those and hit the export as a csv file option to generate the csv file (name_of_your_csv_file.csv). The other alternative is to use the sqlite3.exe to open your database in cmd or powershell and then doing the export with:

   .tables #this lets you explore your available tables 
   headers on
   mode csv
   output name_of_your_csv_file.csv
   

2.There are two choices up to this point: You can either insert all the records at once to your new database or you can drop your existing tables from the new database and then recreate them and import the .csv file. I went for the drop option because there were more than 100 records to migrate.

# sqlite3
# check the structure of your table so you can re-create it

.schema <table_name> 
#the result might be something like CREATE TABLE IF NOT EXISTS "web_app_navigator_table" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "ticket" varchar(120) NOT NULL);

#drop the table 
drop table web_app_navigator_table

#re-create the table
create table web_app_navigator_table(id integer not null primary key autoincrement, ticket varchar(120) not null);

#do the import of the csv file
.import C:/Users/a/PycharmProjects/apps/navigator/name_of_your_csv_file.csv table_name_goes_here

You might see an error such as csv:1: INSERT failed datatype mismatch but this indicates that the first row of your csv file was not inserted because it contains the headers of the exported data from your old database.

---

Related Topics