Transfer Database Data

Question

Transfer Database Data

Messi

Old Hand

Points: 378
More actions
September 24, 2017 at 1:33 am

#349227

hello community.
i have two databases. one is old version and other is new version ..
in new version i have some new fields in all the tables old version doesn't has those fields.
now there is a lot of data in old database i want to put that data in new database.
how can i do it ? after taking data script and run on new database not works due to different few fields.
how can i transfer all the data into new database kindly suggest me a way of doing this.
December 5, 2017 at 4:48 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99137 More actions · Answer 1

At a guess I assume that you're not declaring your column names when doing the insert, or your new columns aren't nullable, and so require a value. You'll need to ensure you're columns that you aren't inserting into are nullable, and declare you're columns as part of your INSERT statement, or provided values for your non-nullable columns. As a small example:

USE TestDB;
GO
CREATE TABLE OldTable (CustomerName varchar(20) NOT NULL);
CREATE TABLE NewTable (CustomerName varchar(20) NOT NULL, CustomerAge int NOT NULL);
GO
INSERT INTO OldTable VALUES ('Steve'),('Harry'),('Jane'),('Jasmine');
GO
--This will fail because there are less columns than the target table
INSERT INTO NewTable
SELECT *
FROM OldTable;
GO
--This will fail, because CustomerAge is NOT Nullable. Thus requires a value.
INSERT INTO NewTable(CustomerName)
SELECT CustomerName
FROM OldTable;
GO
--Let's just check there's nothing there though
SELECT *
FROM NewTable;
GO
--One option would be to provide a value for the age, let's use 0, as we don't know it.
INSERT INTO NewTable (CustomerName, CustomerAge)
SELECT CustomerName, 0
FROM OldTable;
GO
SELECT *
FROM NewTable;
--Hazaar it worked!
GO
--Clear it for alternate option.
DELETE FROM NewTable;
--Check the data is gone
SELECT *
FROM NewTable;
GO
--Other method is to set age to be nullable.
ALTER TABLE NewTable ALTER COLUMN CustomerAge int NULL;
GO
--This will work this time
INSERT INTO NewTable (CustomerName)
SELECT CustomerName
FROM OldTable;
GO
--Check the INSERT worked (should have NULL for age now)
SELECT *
FROM NewTable;
GO
--Clean up
DROP TABLE OldTable;
DROP TABLE NewTable;
GO

Hmm VS Code x Ubuntu does not play well with SSC.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 247125 More actions · Answer 2

Messi - Sunday, September 24, 2017 1:33 AM
--
how can i do it ? after taking data script and run on new database not works due to different few fields.
--

How did you create this 'data script'?

Messi Old Hand Points: 378 More actions · Answer 3

Phil Parkin - Sunday, September 24, 2017 6:12 AM
Messi - Sunday, September 24, 2017 1:33 AM
--
how can i do it ? after taking data script and run on new database not works due to different few fields.
--
How did you create this 'data script'?

from here after setting type of data script to Data Only

Phil Parkin SSC Guru Points: 247125 More actions · Answer 4

OK, did you consider adding the new columns to the old DB and then doing the scripting?

Thom A SSC Guru Points: 99137 More actions · Answer 5

Phil Parkin - Sunday, September 24, 2017 6:23 AM
OK, did you consider adding the new columns to the old DB and then doing the scripting?

Didn'teeven think about that as an option. :p

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Messi Old Hand Points: 378 More actions · Answer 6

Phil Parkin - Sunday, September 24, 2017 6:23 AM
OK, did you consider adding the new columns to the old DB and then doing the scripting?

you mean if i have new NOT NULLABLE columns in new database put those as NULLABLE in old database then take the data script and run in new database ?

Thom A SSC Guru Points: 99137 More actions · Answer 7

Messi - Sunday, September 24, 2017 11:07 AM
Phil Parkin - Sunday, September 24, 2017 6:23 AM
OK, did you consider adding the new columns to the old DB and then doing the scripting?
you mean if i have new NOT NULLABLE columns in new database put those as NULLABLE in old database then take the data script and run in new database ?

If they're not nullable in the new database, then have nullable columns with NULL values in your old database won't help you. If they're not nullable you need to provide a value for the new columns or make them nullable.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Messi Old Hand Points: 378 More actions · Answer 8

Thom A - Sunday, September 24, 2017 11:19 AM
If they're not nullable in the new database, then have nullable columns with NULL values in your old database won't help you. If they're not nullable you need to provide a value for the new columns or make them nullable.

yes. that's right. i will surely have to do some manually data entry for it !

Phil Parkin SSC Guru Points: 247125 More actions · Answer 9

Messi - Sunday, September 24, 2017 11:24 AM
yes. that's right. i will surely have to do some manually data entry for it !

Or give them default values in the old DB.

Transfer Database Data

Cookies on SQLServerCentral