Generate script for data

Question

Generate script for data

Adam Sandler

SSCrazy

Points: 2697
More actions
April 25, 2007 at 4:13 am

#172096

Is it possible to generate a script to populate a table

Ie if I go generate script of a table - I get a script which creates the table as is - can I get a script which populates it as is - or is there a glaringly obvious differant solution that I'm missing.

Thanks

Viewing 15 posts - 1 through 15 (of 17 total)

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

You could use the DTS Import/Export Wizard in Enterprise Manager to copy table data. Right-click on the tablename in the list in EM and choose All Tasks > Export Data. The wizard will then take you through copying.

If you need more info on this or were looking for a T-sql based solution post back.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Adam Sandler SSCrazy Points: 2697 More actions · Answer 2

Thanks for the answer -

What I am trying to do is basically populate a lookup table data on multiple dbs in differant locations. (With data I have in a table here)

I have a mechanism that runs scripts, which create views, sp etc for reports - I was hoping for a script to move the data in. Obviuosly I could right some code to do this - but would like to generate code if possible for all the multiple records, to stop it being a repetitive task.

So with this in mind I am not sure if the DTS will help?

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 3

With that, I'm not sure DTS is the best way to go...

Can I just check with you to make sure that I understand the situation fully:

You have a single lookup table in one database on one server and you want to copy that from it's location (executing a script on THAT server) to other databases on other servers?

You will possibly need to do this in the future for other table/server combinations?

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Adam Sandler SSCrazy Points: 2697 More actions · Answer 4

Almost -

I want a script to generate the table as I have it here - data too.

That script will then be used on the other dbs in other locations. (Once I have the script this is handled OK by others).

So what I need is the script. But since its many records I don't want to/can't manually do it.

Hope I make myself understood.

Cheers

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 5

Okay, understood. I'll get back to you!

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 6

Try this one

http://vyaskn.tripod.com/code/generate_inserts.txt

Credit to Narayana Vyas Kondreddi for the script.

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 7

That's probably a lot better than I would have done.

And quicker too!

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Adam Sandler SSCrazy Points: 2697 More actions · Answer 8

Thanks Mr Junkie - thats perfect. Thanks Adrian fro helping clarify the problem.

margalit walzer Newbie Points: 3 More actions · Answer 9

I discovered a great took that automate this process

try it out here http://www.dbmaestro.com/download.aspx?id=35

With a cllick of a button you can generate those types of reports.

they will let you use it for free for about a month so you can see it for yourself..

I have been using dbMaestro for about a year and I think it is a real timesavor.

just thought it might help you!

Adam Sandler SSCrazy Points: 2697 More actions · Answer 10

Adam Sandler

SSCrazy

Points: 2697

April 25, 2007 at 7:28 am

#702372

Thanks here too!!

Omri Bahat-433962 SSC Eights! Points: 901 More actions · Answer 11

Just wanted to plug in the following:

You can also use the SELECT INTO clause in order to create an exact copy of a source table to a target table. This is easy if all databases are on the same server; a little more difficult (requires linked servers or such) if the tables are in different databases.

For example, if the databases are on the same server, you can do:

SELECT * INTO MyNewTableOwner.MyNewTableName

FROM MySourceDB.MySourceTableOwner.MySourceTableName

This will carry over the exact DDL definition from the source table to the target table, including all the data.

Last comment-

Margalit- you may want to identify yourself as the Product Manager for DBMaestro. It is a good sync tool like many others (x-sql.com, free tools from Quest Software, features in VS for DB Pros, red-gate's compare/sync, etc.).

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

>>Margalit- you may want to identify yourself as the Product Manager for DBMaestro.

It DID sound like a shameless plug... now I know why... almost as bad as spam but lower.

--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)

Omri Bahat-433962 SSC Eights! Points: 901 More actions · Answer 13

"It DID sound like a shameless plug... "

All is fair in love and war... (Francais Edward Smedley)

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 14

"All is fair in love and war... "

Sadly this is neither; just, as mentioned, shameless.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]