Dump several MS SQL rows to a file and insert them to another MS SQL db

Hello,

I am wondering if it is possible for me to select 14 rows a table and export them to a text file which i can then use to import the selected rows into a replica database on a different server?
drews1fAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
you can use export or bcp functinoality.


0
RiteshShahCommented:
0
drews1fAuthor Commented:
i presumed there was a way but ive been lookin in MSSM for ages and cant find export anywhere.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

RiteshShahCommented:
or you can create one temporary table with those 14 rows and export it to text file by the example given in above link. create table with your data like


select top 14 * into TempOriginalTable from youOriginalTable where field1>10

above query will create TempOriginalTable with 14 which you can export.
0
RiteshShahCommented:
if you directly want to export than right click on database->Task->Export
0
RiteshShahCommented:
create one file Test.txt in your C drive, go to database->Task->Export and follow attached screen shot in attached .zip file. this is the easiest way.
export.zip
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
drews1fAuthor Commented:
im trying:

BCP myo.dbo.client out temp.txt -c -U<administrator> -S<MYFINFILE\MYOFF>

and im getting the error:

The syntax of the command is incorrect.
0
RiteshShahCommented:
what about the zip file I gave you? it is the simplest way to do it.
0
RiteshShahCommented:
as long as your syntax concern, you have not provided Drive letter before temp.txt file, more over, you don't need to give < and > surrounded username and server name. if you are giving ID than where is password? I saw with windows authenticated trusted connection in my article. you can unzip my zip file and follow very simple steps.


0
Anthony PerkinsCommented:
Perhaps I am stating the obvious, but surely it would be easier to insert directly into the target database, without resorting to sending to an intermediary file.
0
RiteshShahCommented:
I am agree and have already explained in my previous post.

drews1f,

If you want to directly export to another SQL Server than again follow the same steps I gave you in .ZIP file but rather than selecting "Flat File" from destination window, select SQL Server and give its credential there.
0
Anthony PerkinsCommented:
That is not what I meant.  I meant:

Insert TargetTable(TargetCol1, TargetCol2, TargetCol3, ...)
Select SourceCol1, SourceCol2, SourceCol3, ...
From LinkedServer.DatabaseName.TableOwner.TableName
Where YourConditionGoesHere
0
RiteshShahCommented:
Well, if other database is in same server it would be easy to follow the way you told but if it is not, than have to create linked server so I would suggest export wizard in that case.

Isn't it easy to do so? if need to do it frequently than package can be saved as well.
0
Anthony PerkinsCommented:
RiteshShah,

Why don't we let the author decide? You are making way to many assumptions.
0
Anthony PerkinsCommented:
>>Well, if other database is in same server it would be easy to follow the way you told but if it is not, <<
And no, again that is not what I assumed. The whole point of a linked server is that they are not on the same server.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.