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 Boolean EvalExpression comparing dates

To the experts in the field:

There is probably a very simple solution that is avoiding my grasp.

I have a For Loop which I want to execute as long as a variable called BeforeRunDt = CurrentDate. Both are DateTime data types and I am using the following expression:

@.BeforeRunDt==@.CurrenDate

I get an error stating "Cannot convert expression value to propeerty type"

I understand that the result of the expression should be a boolean value but am just struggling on how to create it.

Thanks!

I believe that the following will work:

@.BeforeRunDt == @.CurrentDate ? True : False

Edit: You would set the variable to type boolean, and evaluate as expression to true with the above as the expression...

|||

Wow! Now I feel really stupid. After all the fuss I figured that I needed a For Each Loop instead and so I am back at square one trying to learn how to use that transform.

Thank you very much for taking the time to respond to my question!

|||

desibull wrote:

Wow! Now I feel really stupid. After all the fuss I figured that I needed a For Each Loop instead and so I am back at square one trying to learn how to use that transform.

Thank you very much for taking the time to respond to my question!

I would still have expected the expression in your first post to work.

Try replacing it with

(DT_BOOL)(@.BeforeRunDt==@.CurrenDate)

and see if it works.

cheers

Jamie

|||I was slightly surprised that his first expression wouldn't work as well... but if it isn't cast to the proper type that kind of makes sense I suppose...|||

EWisdahl wrote:

I was slightly surprised that his first expression wouldn't work as well... but if it isn't cast to the proper type that kind of makes sense I suppose...

Yeah. it could be similar to the issue talked about here:

NULLs in expressions gotcha

(http://blogs.conchango.com/jamiethomson/archive/2006/10/12/SSIS_3A00_-NULLs-in-expressions-gotcha.aspx)

-Jamie

createing new items based off a select?

I know this has to be possible with out using a cursor to loop through
this..
say I have tables like this...
Table A
==========
ItemID INT
Item TEXT
Table B
==========
PersonID int
ItemID int (from table A)
Table C
============
PersonID
Item
ItemID
Description
I want to do a select on table A get all items with the Item ID the person
in Table B hase and insert the result into Table C.
So if I have 2 items in A, and my Info in B, I want to do a query and have
records for each of them inserted into C with their info where it matches
together... I could easily do this with a cursor by looping through table A
looking for the ItemID of the current person then doing an Insert into table
C with the persons item information and the persons info... is there a way
to do this WITOUT a cursor and just a query? thanks!You're thinking in procedural language terms.
In T-SQL, it would go something like this...
insert into tablec (personid, item, itemid, description)
select b.personid, a.item, a.itemid, null
from tablea a
join tableb b on (a.itemid = b.itemid)
No idea where description is coming from so I nulled it.
"Brian Henry" <nospam@.nospam.com> wrote in message
news:e4UYeT%23WFHA.1148@.tk2msftngp13.phx.gbl...
> I know this has to be possible with out using a cursor to loop through
> this..
> say I have tables like this...
> Table A
> ==========
> ItemID INT
> Item TEXT
> Table B
> ==========
> PersonID int
> ItemID int (from table A)
>
> Table C
> ============
> PersonID
> Item
> ItemID
> Description
>
> I want to do a select on table A get all items with the Item ID the person
> in Table B hase and insert the result into Table C.
> So if I have 2 items in A, and my Info in B, I want to do a query and have
> records for each of them inserted into C with their info where it matches
> together... I could easily do this with a cursor by looping through table
A
> looking for the ItemID of the current person then doing an Insert into
table
> C with the persons item information and the persons info... is there a way
> to do this WITOUT a cursor and just a query? thanks!
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. If you had followed minimal netiquette, would your
pseudo-code look like this?
CREATE TABLE Items
(item _id INTEGER NOT NULL PRIMARY KEY,
Item_descrp VARCHAR(100) NOT NULL);
An item is not an attribute of a person; it is an entity, so we need to
fix your design.
CREATE TABLE People
(person_id INTEGER NOT NULL PRIMARY KEY,
. );
CREATE TABLE Purchases
(person_id INTEGER NOT NULL
REFERENCES People(person_id),
item_id INTEGER NOT NULL
REFERENCES Items(item_id),
PRIMARY KEY (person_id, item_id));
and have records [sic] for each of them inserted into C with their info
where it matches together... I could easily do this with a cursor by
looping through table A looking for the ItemID of the current person
then doing an Insert into table C with the persons item information and
the persons info... is there a way to do this WITOUT a cursor and just
a query <<
Your tables are not normalized. Rows are not records; no wonder you
think of procedural code and cursors instead of a query. Do not
materialize a new table, as if you were allocating a scratch tape in a
file system.
CREATE VIEW PurchaseReport (..)
AS
SELECT P.*, B.*
FROM Purchases AS P, People AS B, Items AS I
WHERE I.item_id = P.item_id
AND B.person_id = P.person_id;
The VIEW will always be current, unlike a new, redundant base table.|||Try,
insert into tablec (personid, item, itemid)
select b.personid, a.item, a.itemid
from tableb as b inner join tablea as a
on b.itemid = a.itemid
AMB
"Brian Henry" wrote:

> I know this has to be possible with out using a cursor to loop through
> this..
> say I have tables like this...
> Table A
> ==========
> ItemID INT
> Item TEXT
> Table B
> ==========
> PersonID int
> ItemID int (from table A)
>
> Table C
> ============
> PersonID
> Item
> ItemID
> Description
>
> I want to do a select on table A get all items with the Item ID the person
> in Table B hase and insert the result into Table C.
> So if I have 2 items in A, and my Info in B, I want to do a query and have
> records for each of them inserted into C with their info where it matches
> together... I could easily do this with a cursor by looping through table
A
> looking for the ItemID of the current person then doing an Insert into tab
le
> C with the persons item information and the persons info... is there a way
> to do this WITOUT a cursor and just a query? thanks!
>
>|||thats what I was trying to remember right there.. thanks!
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:uGYGQc%23WFHA.2420@.TK2MSFTNGP12.phx.gbl...
> You're thinking in procedural language terms.
> In T-SQL, it would go something like this...
> insert into tablec (personid, item, itemid, description)
> select b.personid, a.item, a.itemid, null
> from tablea a
> join tableb b on (a.itemid = b.itemid)
> No idea where description is coming from so I nulled it.
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:e4UYeT%23WFHA.1148@.tk2msftngp13.phx.gbl...
> A
> table
>