Copy records from one DB to another

Question

Post reply

Copy records from one DB to another

Edvard Korsbæk

SSCertifiable

Points: 5994
More actions
September 11, 2017 at 10:21 pm

#328249

Hi all!
I have this pretty simple table:
CREATE TABLE [dbo].[vacation](
    [TYPECODE] [TINYINT] NULL,
    [PERSONALID] [INT] NULL,
    [START] [INT] NULL,
    [FLD_END] [INT] NULL,
    [COST] [REAL] NULL,
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [Wish_Vacation] [TINYINT] NULL,
    [vacation_model] [TINYINT] NULL,
CONSTRAINT [PK_dbo_PK_VACATION] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I have lost app 500 records in it.
I have them in a backup.
The backup is here.
The DB is remote, and the only item i can send is scripts, i.e. i cant just copy directly.
To make app 500 insert commands manually is a bit time consuming.
Can it be done in some way automatically, i.e. how to script 'Insert all coloums from DB A that does not exist in DB B?
Best regards
Edvard Korsbæk

Viewing 8 posts - 1 through 7 (of 7 total)

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

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 1

Hi.

Are you just after some INSERT scripts? This can be done in SQL Server Management Studio:

Right-click on the database in Object Explorer
Click on Tasks > Generate Scripts.
Click Next.
Select the table that you want to script using the check boxes.
Click Next.
click on Advanced.
Scroll down to the bottom of the General category to find "Types of data to script".
Of the three options you are probably after "Data Only". This will create "INSERT" statements. Select your preferred options and click on OK.
Choose where to save the generated script to.
Click Next.
Click Next to generate....

ta,
Bevan

Edvard Korsbæk SSCertifiable Points: 5994 More actions · Answer 2

That was it!
Now i only need to clean up in the ones i do not need
Wonder about how much more i just do not know anything about.

Best regards

Edvard Korsbæk

Edvard Korsbæk SSCertifiable Points: 5994 More actions · Answer 3

I will make the script in the 'Working' DB
Same in the backup
Then Use Ultra Compare to delete the surplus lines from the script from the backup.
Less than 30 minutes I belive.

Best regards

Edvard Korsbæk

GroverVivek SSCommitted Points: 1674 More actions · Answer 4

Is it possible there to create linked servers there and using that to connect both db(s) residing in different instances?

Regards
VG

Edvard Korsbæk SSCertifiable Points: 5994 More actions · Answer 5

The DB is placed inside the government, and I am (hopefully) totally unable to reach it.

I have to work with copies, and send scripts.

Best regards

Edvard Korsbæk

GroverVivek SSCommitted Points: 1674 More actions · Answer 6

Edvard KorsbÃ¦k - Monday, September 11, 2017 11:32 PM
The DB is placed inside the government, and I am (hopefully) totally unable to reach it.
I have to work with copies, and send scripts.
Best regards
Edvard KorsbÃ¦k

Fine. Comparing the scripts seems fine solution.

Regards
VG

Phil Parkin SSC Guru Points: 246962 More actions · Answer 7

Another option which may be less painless than comparing scripts is to create two identical temp tables and perform the inserts to them, one from script 1 and one from script 2.
Once done, a simple query will identify the missing rows (and this query can then easily be turned into an INSERT).

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin