Creating a loop

Hi

I need to create an SQL table and automatically populate it with 100,000 records (just one column).

How can I achieve this? The create table part is straight forward enough but how can I get all those rows in there using a single script?

I imagine the statement will require Loop and While.

This is for testing purposes.declare @.i int
set @.i=100000
while @.i>0
begin
insert into table_name values (@.i)
set @.i=@.i-1
end|||Much appreciated!
I'm running this against a table I've already created and I'm getting..
"Server: Msg 213, Level 16, State 4, Line 5
Insert Error: Column name or number of supplied values does not match table definition." :confused:|||can u paste the ddl for the table? how many columns does the table have?
replace the insert with:
insert into table_name (column_name) values (@.i)|||No worries - I got it to work in the end - I created a new table with just one column and everything is fine.

Thanks for your help amigo/amiga..|||hi Harshal
I'm hoping you can help with a problem leading on from this. The purpose of this exercise was to measure how long two scenarios take to create the tables and insert records.

Scenario A : Stand alone desktop
Scenario B : Server with dual processor Xeon

The database on each is identical - yet the Desktop took 01:07 to insert compared to the server's 06:48 !! Nearly 7 minutes!

Any ideas what could be causing this??

Cheers

Samsara

Creating a DTS package

I've created a DTS package and now I need to distribute it to
different servers.

I've been looking for a way to automatically/programatically create a
DTS package, but have not found anything definite.

From the DTS package itself, I see where I can save the dts package as
a structured file, with the name XXXXX.dts. Once I have that DTS
file, how dow I turn it back into a dts package in Enterprise manager?
I don't want to have to manually create the package for 300+
servers...

Thanks,
JenniferYou can load a DTS structured storage file from EM by right-clicking on
the Data Transformation Services folder and selecting Open Package. You
can then select Package --> Save As in order to save it locally.

This can be a bit tedious if you have a lot of servers. The VBScript
example below will load your DTS structured storage file and save it to
multiple servers using a trusted connection. See the Books Online for
details.

Dim DtsPackage
Set DtsPackage = CreateObject("DTS.Package2")
DtsPackage.LoadFromStorageFile "C:\MyDtsPackages\MyDtsPackage.dts", ""
DtsPackage.SaveToSqlServer "MyServer1", , , 256
DtsPackage.SaveToSqlServer "MyServer2", , , 256
DtsPackage.SaveToSqlServer "MyServer3", , , 256

Note that you can execute a DTS package directly from a structured
storage file using DTSRUN. If your servers have access to a shared
network location, you could execute the package with a UNC path rather
than saving the package locally on each server.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Jennifer" <jennifer1970@.hotmail.com> wrote in message
news:3358f49d.0311041237.15641d35@.posting.google.c om...
> I've created a DTS package and now I need to distribute it to
> different servers.
> I've been looking for a way to automatically/programatically create a
> DTS package, but have not found anything definite.
> From the DTS package itself, I see where I can save the dts package as
> a structured file, with the name XXXXX.dts. Once I have that DTS
> file, how dow I turn it back into a dts package in Enterprise manager?
> I don't want to have to manually create the package for 300+
> servers...
> Thanks,
> Jennifer

Creating a diagram automatically through Enterprise Manager

Hi all,
I'm wondering, is there any way to have SQL Server automatically generate
a diagram via Enterprise Manager?
The situation is, I have created a database by executing a big as script
of SQL. The script created the tables and views and added the primary key
constraints etc. However there isnt a diagram available to help me understan
d
the schema a bit easier.
Is there some way to have Enterprise Manager interpret the schema and the
constraints and reverse engineer a pretty diagram for me?
Thanks to anyone who can advise
Kindest Regards
tce
Kindest Regards
tceIsn't it as easy as creating a new Diagram and adding all the tables?
Not to belittle the task, but the wizard is pretty good? It isn't the
automated approach but works none the less.
Clint Hill
H3O Software
http://www.h3osoftware.com
thechaosengine wrote:
> Hi all,
> I'm wondering, is there any way to have SQL Server automatically
> generate a diagram via Enterprise Manager?
> The situation is, I have created a database by executing a big as script
> of SQL. The script created the tables and views and added the primary
> key constraints etc. However there isnt a diagram available to help me
> understand the schema a bit easier.
> Is there some way to have Enterprise Manager interpret the schema and
> the constraints and reverse engineer a pretty diagram for me?
> Thanks to anyone who can advise
> Kindest Regards
> tce
> Kindest Regards
> tce
>|||Hi Clint
Thanks for your reply. You are quite right, the wizard does generate the
diagram! I must confess to being a bit stupid here. I normally use Access
connected to SQL Server to develop the database and access doesn't have quit
e
the same functionality in this regard. I actually assumed that the diagramin
g
features were identical. They certainly seem very similar.
Thanks for your help!
Kindest Regards
tce
[vbcol=seagreen]
> Isn't it as easy as creating a new Diagram and adding all the tables?
> Not to belittle the task, but the wizard is pretty good? It isn't the
> automated approach but works none the less.
> Clint Hill
> H3O Software
> http://www.h3osoftware.com
> thechaosengine wrote:
>

Creating a diagram automatically through Enterprise Manager

Hi all,
I'm wondering, is there any way to have SQL Server automatically generate
a diagram via Enterprise Manager?
The situation is, I have created a database by executing a big as script
of SQL. The script created the tables and views and added the primary key
constraints etc. However there isnt a diagram available to help me understand
the schema a bit easier.
Is there some way to have Enterprise Manager interpret the schema and the
constraints and reverse engineer a pretty diagram for me?
Thanks to anyone who can advise
Kindest Regards
tce
Kindest Regards
tce
Isn't it as easy as creating a new Diagram and adding all the tables?
Not to belittle the task, but the wizard is pretty good? It isn't the
automated approach but works none the less.
Clint Hill
H3O Software
http://www.h3osoftware.com
thechaosengine wrote:
> Hi all,
> I'm wondering, is there any way to have SQL Server automatically
> generate a diagram via Enterprise Manager?
> The situation is, I have created a database by executing a big as script
> of SQL. The script created the tables and views and added the primary
> key constraints etc. However there isnt a diagram available to help me
> understand the schema a bit easier.
> Is there some way to have Enterprise Manager interpret the schema and
> the constraints and reverse engineer a pretty diagram for me?
> Thanks to anyone who can advise
> Kindest Regards
> tce
> Kindest Regards
> tce
>
|||Hi Clint
Thanks for your reply. You are quite right, the wizard does generate the
diagram! I must confess to being a bit stupid here. I normally use Access
connected to SQL Server to develop the database and access doesn't have quite
the same functionality in this regard. I actually assumed that the diagraming
features were identical. They certainly seem very similar.
Thanks for your help!
Kindest Regards
tce
[vbcol=seagreen]
> Isn't it as easy as creating a new Diagram and adding all the tables?
> Not to belittle the task, but the wizard is pretty good? It isn't the
> automated approach but works none the less.
> Clint Hill
> H3O Software
> http://www.h3osoftware.com
> thechaosengine wrote:

Creating a diagram automatically through Enterprise Manager

Hi all,
I'm wondering, is there any way to have SQL Server automatically generate
a diagram via Enterprise Manager?
The situation is, I have created a database by executing a big as script
of SQL. The script created the tables and views and added the primary key
constraints etc. However there isnt a diagram available to help me understand
the schema a bit easier.
Is there some way to have Enterprise Manager interpret the schema and the
constraints and reverse engineer a pretty diagram for me?
Thanks to anyone who can advise
Kindest Regards
tce
Kindest Regards
tceIsn't it as easy as creating a new Diagram and adding all the tables?
Not to belittle the task, but the wizard is pretty good? It isn't the
automated approach but works none the less.
Clint Hill
H3O Software
http://www.h3osoftware.com
thechaosengine wrote:
> Hi all,
> I'm wondering, is there any way to have SQL Server automatically
> generate a diagram via Enterprise Manager?
> The situation is, I have created a database by executing a big as script
> of SQL. The script created the tables and views and added the primary
> key constraints etc. However there isnt a diagram available to help me
> understand the schema a bit easier.
> Is there some way to have Enterprise Manager interpret the schema and
> the constraints and reverse engineer a pretty diagram for me?
> Thanks to anyone who can advise
> Kindest Regards
> tce
> Kindest Regards
> tce
>

Creating a Auto Increment which contains Numbers & Text

Hi,
Please can you let me know the best solution for creating a primary key which automatically increments by 1 each time a record is added. My current Primary key is of type "Int" which increments by 1 each time, but I would like my primary key to contain "ABC" before the 1. So each time a record is added I would like to see:-
ABC000001
ABC000002
ABC000003
Etc, Etc
I am using SQL Server 2000 and creating an ASP.Net application, will I need to write code in a Stored Procedure to do this?
Regards,
BrettI would just create a view that exposes the alpha you want plus the IDENTITY column, formatted as you wish.|||

Douglas' solution is a horrible hack. It is not scalable (what happens when you want "BCD" and "ABC") and requires having two keys -- the "real" key and the "identity" key.
You were on the right track with a stored procedure. I don't know what you're trying to model ... so I'm going with Accounts.
Your proc should look like:
PROCEDURE Create_New_Account ( @.Account_Number CHAR(8) OUT, @.Salesperson_Number CHAR(5), (... other required fields) )
I'd recommend pre-creating your identifiers, and doing a SELECT / DELETE out of the Account_Numbers table. If that won't fly, just SELECT MAX(Account_Number) from your Accounts table, parse out the string and increment the number part.

|||

I would disagree with the "Horrible hack" characterization. If there was a column [Prefix] and a column with the identity, the [Prefix] could be whatever you want. I saw a reference to Auto Increment and was thinking about Access rather than SQL Server (yes, of course I realize the post was on the SQL Server forum).

If you can ensure all access to inserting data is through the stored procedures, then of course use that. method.

What is being attempted (adding meaning of some sort to an identifier) is not often a wonderful thing.

|||You're right; it's ideal to have to have both parts of the key in the table instead of combining it into one.
But I won't concede to "Horrible Hack" being a mischaraterization. Everytime I've seen this done ... it's a horrible hack ... here's my favorite example ...
Order_Numbers were defined something as : (Order_Date + Order_Seq) + XOR Parity Shift. This was to ensure order_nums were not predictable (from end users) and were verifiable.
The programmer who implemented this requirement used this schema:
Orders_Base = TABLE (Order_ID, Order_Date, Order_Seq, ...)
Orders = VIEW( SELECT complex_generator_fn( Order_Date, Order_Seq) as Order_Num, Order_Date, ... )
Guess how fun that got when they actually used the system to place orders. And tried to query on the non-indexed Order_Num.
They first implemented decode logic in the Middle tier to get Seq + Date. Surprise surprise, that didn't quite work out so well either.
Final iteration (and still as it exists today):
Orders_Base = TABLE( Order_ID, Order_Date, Order_Seq )
Order_Numbers = TABLE( Order_ID, Order_Number )
Oh, and warehousing and analytics is a COMPLETE nightmare.