How to dump a table from a Prod database to a dev database

Question

Post reply

How to dump a table from a Prod database to a dev database

sunny.tjk

SSCertifiable

Points: 5041
More actions
September 20, 2010 at 2:49 pm

#148702

Instead of using SQL Import and Export wizard, how do I dump a table from a Prod database to a dev database?
Thanks everyone.

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

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Besides the easy way?

SSIS package and manually ran job.

Stored procedure with drop/create on the target table and then a linked server to bring the data down.

Restore the production DB locally to dev and do the same as above without a linked server.

Print it off and hand it to a user to retype. :Whistling:

What are you looking to do that Import/export is not an option?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

george sibbald SSC Guru Points: 104210 More actions · Answer 2

good old BCP utility.

---------------------------------------------------------------------

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

SQLBuddy123

One Orange Chip

Points: 28294

September 20, 2010 at 3:48 pm

#1224420

Also... DTS

Thanks,

SQLBuddy

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

sqlbuddy123 (9/20/2010)
Also... DTS

Deprecated in SQL 2005, replaced by SSIS, should not be used for new development, will be removed in a future version of SQL Server.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

Deprecated in SQL 2005, replaced by SSIS, should not be used for new development, will be removed in a future version of SQL Server.
________________________________________
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass

Please note that there is a difference between -- Which tool can be used to do a task ? and Whether to use the tool is a good option or not ?

I just mentioned that it's one of the options. Nothing more.

There are many factors which makes users to use DTS. What if the DTS is being used in their environment since long time or if SSIS is not installed in their environment or if the user has no knowledge to use SSIS.

Thank You,

Best Regards,

SQLBuddy.

shahm10 SSCrazy Points: 2217 More actions · Answer 6

SELECT * INTO DevTable

FROM ProdTable ----with the use of OPENROWSET()

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

sqlbuddy123 (9/20/2010)
Deprecated in SQL 2005, replaced by SSIS, should not be used for new development, will be removed in a future version of SQL Server.
________________________________________
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
Please note that there is a difference between -- Which tool can be used to do a task ? and Whether to use the tool is a good option or not ?
I just mentioned that it's one of the options. Nothing more.
There are many factors which makes users to use DTS. What if the DTS is being used in their environment since long time or if SSIS is not installed in their environment or if the user has no knowledge to use SSIS.
Thank You,
Best Regards,
SQLBuddy.

This is posted in a SQL 2005 forum so normally 2005 solutions are the best ones to suggest. 2005 implies that they likely don't have DTS.

Another option is to use Schema and data comparison tools to create and transfer the data. Like RedGate's SQL Compare and SQL DataCompare.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 8

sqlbuddy123 (9/20/2010)
Deprecated in SQL 2005, replaced by SSIS, should not be used for new development, will be removed in a future version of SQL Server.
________________________________________
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
Please note that there is a difference between -- Which tool can be used to do a task ? and Whether to use the tool is a good option or not ?
I just mentioned that it's one of the options. Nothing more.
There are many factors which makes users to use DTS. What if the DTS is being used in their environment since long time or if SSIS is not installed in their environment or if the user has no knowledge to use SSIS.
Thank You,
Best Regards,
SQLBuddy.

Please note that if you don't tell people these things, they end up screwing things up for the future. How do I know this? They're the ones asking the questions. 😉

My recommendation would be to not tell folks to use deprecated methods in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)