backup/restore singe table (Export table?)

Question

backup/restore singe table (Export table?)

Edvard Korsbæk

SSCertifiable

Points: 5991
More actions
February 24, 2017 at 3:30 am

#335491

Hi all!
I have this pretty simple table in two different DB's, lets call them DB1 and DB2, places on two different servers.
CREATE TABLE [dbo].[sadocs](
    [SADOCSID] [INT] NOT NULL,
    [ARKIVID] [CHAR](20) NULL,
    [DOCID] [INT] NULL,
    [PARENTID] [INT] NULL,
    [MEDIAID] [INT] NULL,
    [DOCCOLLECTION] [INT] NULL,
    [SUBFOLDER] [INT] NULL,
    [ORGFILENAME] [CHAR](1024) NULL,
CONSTRAINT [PK_SADOCSID] PRIMARY KEY CLUSTERED
(
    [SADOCSID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
IN db1 I have app 600 records
IN DB2 i have none, and want to export the records from DB1 to DB2 without changing anything else in DB2.
There must be a tool out there, that does this fast and easy....
Best wishes
Edvard Korsbæk

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

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

andreas.kreuzberg SSCertifiable Points: 6183 More actions · Answer 1

Hi,
would you like to copy the data once, or regularly?
If you just wan't to copy the data once, just use the SSMS.
Select TASK => Export Data and choose you source and target.
Kind regards,
Andreas

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Edvard

There is indeed! In SSMS, right-click on DB1 in Object Explorer, then choose Tasks and Export Data, and let the wizard guide you through the steps.

John

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 3

Can you not just create a linked server between the two and script out the INSERT INTO......SELECT * FROM??

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Yes, you can do it like that. I wouldn't recommend creating a permanent server object, though, if, as it seems, this is a one-off task.

John

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

Something like this happens fairly often.
e.g. I have table of ZIP codes with app. 1500 records.
Of cause i could make something for it with a server and so on, but what i would love to have fast and easy is something like 'Rightclick on the table, script rows as insert', done.

Change to new DB
Run script
I found the possibility in SSMS - Its neither fast or easy, but that's how I will end, I think.
Best wishes

Edvard Korsbæk

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 6

If you need to do this regularly, building out scripts, a Powershell app, or even SQL Server Integration Services (SSIS), would be the best way to get it done. Using the GUI for repeated processing is the worst choice. Also, if this is regularly maintained lookup data and isn't very large, I'd put it into source control so that you can version it just like your code.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 7

write a simple BCP script for the table you need to export, schedule as required.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 8

Steve Jones - SSC Editor

SSC Guru

Points: 734449

February 24, 2017 at 9:57 am

#1930206

PoSh or BCP, either work well. Keep it simple.

Edvard Korsbæk SSCertifiable Points: 5991 More actions · Answer 9

PoSh or BCP, either work well. Keep it simple.
?????

Sorry - I am new to this.

OK, found something:

https://msdn.microsoft.com/en-us/library/ms162802.aspx

Still Best Wishes

Edvard Korsbæk

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 10

Edvard KorsbÃ¦k - Friday, February 24, 2017 11:37 AM
PoSh or BCP, either work well. Keep it simple.
?????
Sorry - I am new to this.
OK, found something:
https://msdn.microsoft.com/en-us/library/ms162802.aspx
Still Best Wishes
Edvard KorsbÃ¦k

This should get you started:

bcp AdventureWorks2012.Sales.Currency out "Currency Types.dat" -T -c

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle