Script to Populate DB with Sample Data?

All:
Is there a tool out there that will construct DB creation scripts that
include taking the source data from the tables can carrying the info
over to the script? The goal here is for developers to be able to check
out the DB creation script (the DDL that creates tables, stored proc,
etc.) AND have their DB filled with sample data.
The tools that come w/ Enterprise Manager, etc. create the DDL scripts
fine - but how can I help my guys get the proper sample data?
Thanks,
Johnhttp://vyaskn.tripod.com/code/generate_inserts.txt
David Portas
SQL Server MVP
--
<jpuopolo@.mvisiontechnology.com> wrote in message
news:1126468738.427835.243580@.z14g2000cwz.googlegroups.com...
> All:
> Is there a tool out there that will construct DB creation scripts that
> include taking the source data from the tables can carrying the info
> over to the script? The goal here is for developers to be able to check
> out the DB creation script (the DDL that creates tables, stored proc,
> etc.) AND have their DB filled with sample data.
> The tools that come w/ Enterprise Manager, etc. create the DDL scripts
> fine - but how can I help my guys get the proper sample data?
> Thanks,
> John
>|||Hi
Another (quicker) approach would be to build a template database and
populate it with static data (which should really be also held in your
source code control system (see David's link) ) and then give the
developers a backup (which you may want to hold in your source code control
system).
John
<jpuopolo@.mvisiontechnology.com> wrote in message
news:1126468738.427835.243580@.z14g2000cwz.googlegroups.com...
> All:
> Is there a tool out there that will construct DB creation scripts that
> include taking the source data from the tables can carrying the info
> over to the script? The goal here is for developers to be able to check
> out the DB creation script (the DDL that creates tables, stored proc,
> etc.) AND have their DB filled with sample data.
> The tools that come w/ Enterprise Manager, etc. create the DDL scripts
> fine - but how can I help my guys get the proper sample data?
> Thanks,
> John
>|||Wow - Thanks!
John

script to combine multiple rows into a single row

Hi everyone,

I really appreciate if anyone could help me with this tricky problem
that I'm having. I'm looking for a sample script to combine data in
multiple rows into one row. I'm using sqlserver. This is how data is
stored in the table.

ID Color
111 Blue
111 Yellow
111 Pink
111 Green

This is the result that I would like to have.

ID Color
111 Blue, Yellow, Pink, Green

There is no definite number of colors per ID. I have to use ID to
group these colors into one row. Therefore, ID becomes a unique key
in the table.

Appreciate your help and time. Thank you in advancelie_valerie@.yahoo.com (Valerie) wrote in message news:<f4825946.0308141505.4c29ff89@.posting.google.com>...
> Hi everyone,
> I really appreciate if anyone could help me with this tricky problem
> that I'm having. I'm looking for a sample script to combine data in
> multiple rows into one row. I'm using sqlserver. This is how data is
> stored in the table.
> ID Color
> 111 Blue
> 111 Yellow
> 111 Pink
> 111 Green
> This is the result that I would like to have.
> ID Color
> 111 Blue, Yellow, Pink, Green
> There is no definite number of colors per ID. I have to use ID to
> group these colors into one row. Therefore, ID becomes a unique key
> in the table.
> Appreciate your help and time. Thank you in advance

The short answer is that you should consider doing this in your client
application. For a longer answer, have a look at the thread "newbie
sql query question" in this group from a day or two ago.

Simonsql

Script Component as Source

I want to use Script Component as Source, but I don't know how to code the output rows. Someone can give me some clue or some sample codes? Thanks in advance.

Here's a sample

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/22/writing-a-resultset-to-a-flat-file.aspx

|||Thanks jwelch, that's very helpful.

But, I have a one more question which is the following:
Actually I have a many tables want to do data transfer from one database into another database. So my programming logical is use Foreach loop to read each table name and send to a variable, inside the loop, I use a data flow which contain two script components, one for source which get output rows from table variable, one for destination which insert data into another database. What do you think about my solution? Do you have another better one?

Thanks for your big help.

|||

jwelch wrote:

Here's a sample

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/22/writing-a-resultset-to-a-flat-file.aspx

Sort of... He's doing some other stuff in there than a basic script component source...

For a basic sample:
http://www.ssistalk.com/2007/04/04/ssis-using-a-script-component-as-a-source/|||

SSIS_NewMan wrote:

Thanks jwelch, that's very helpful.

But, I have a one more question which is the following:
Actually I have a many tables want to do data transfer from one database into another database. So my programming logical is use Foreach loop to read each table name and send to a variable, inside the loop, I use a data flow which contain two script components, one for source which get output rows from table variable, one for destination which insert data into another database. What do you think about my solution? Do you have another better one?

Thanks for your big help.

The control flow has a transfer objects task. Does that not work for you?|||But I only need to transfer some of tables not all, and before insert into another database I also need to do some data conversion. Do you think the Transfer Objects task can help me to do these? Thanks a lot.
|||

SSIS_NewMan wrote:

But I only need to transfer some of tables not all, and before insert into another database I also need to do some data conversion. Do you think the Transfer Objects task can help me to do these? Thanks a lot.

Well, you should be using the OLE DB source and destinations, not scripts. But either way you have a big problem. That is, using one data flow to handle this won't likely work too well unless all of your source tables are exactly the same format and are going to tables that have exactly the same format (among ALL of them)

If SourceTableA has 3 columns, all varchars, and SourceTableB has 4 columns, all integers, SSIS will not be able to handle that.

Changing metadata is not within the scope of SSIS' ability to handle.|||Yes, you are right. That is really a problem which I am trying to fig out. So as your experience, what I should to do? If now I have 10 tables or views (of course, all of the source table's format are different) need to transfer, do I need to create 10 SSIS for each source table? What is your best solution for this problem?

Thanks for your any advice and help.
|||Hi Phil Brammer,
Could you give me some advice about this problem?

Thanks,
|||

SSIS_NewMan wrote:

If now I have 10 tables or views (of course, all of the source table's format are different) need to transfer, do I need to create 10 SSIS for each source table?

I am afraid the short answer is YES. There have been some success reported on this forum about building the packages pragmatically. You may want to research on that

|||

SSIS_NewMan wrote:

Yes, you are right. That is really a problem which I am trying to fig out. So as your experience, what I should to do? If now I have 10 tables or views (of course, all of the source table's format are different) need to transfer, do I need to create 10 SSIS for each source table? What is your best solution for this problem?

Thanks for your any advice and help.

To echo Rafael, if you're performing data checks/edits when transferring the data, then yes, I believe your best option is to build a data flow for each source table.|||Thanks for your reply. I will try to get the best solution.

Script

Hi all,
I need to put together a script that would let me know when the space is running low on each drive. Do you have a sample code? Thanks.Use xp_fixeddrives. The rest should be easy enough.|||I vote for WMI. Plenty of samples for you to choose from at this site. (http://www.microsoft.com/technet/scriptcenter/default.mspx).

xp_fixxeddrives may be easier, but you should also be monitoring your backup resources and other locations as well. Anyway, it's easy to learn and a usefull secondary skill to have.

Regards,

hmscott