SQL Query Question - Inserting new records?

SQL Query Question - Inserting new records?

Post by Walter Ega » Tue, 04 Jun 1996 04:00:00



Hello,
        I am using MSSQL server 6.5 and am trying to learn how to use
More complicated SQL queries other than just select,from,where,into.
Here is my situation, I have a table with one record in it.  That record
has a char field called 'accountid'. Currently that field holds the char
exp '1000'. I want to add 10,000 records to this table, incrementing
'accountid' each time, so when I'm done 'accoundid' should be '11,000'.
 Can I do this from ISQL using only transact-sql commands.
        I found how to use arithmitic in my queries:
                select convert(int,accountid) +1,

but I couldn't find a way to "Loop" in an sql query and insert the new
record each time.  If anyone out there can help I would greatly
apprechiate it.

Walt Egan
Texas A&M College of Veterinary Medicine

 
 
 

SQL Query Question - Inserting new records?

Post by Joseph A. Snid » Tue, 04 Jun 1996 04:00:00


Walter,

        There is always the ability to double the number of rows by using an
insert statement on the same set of records, but adding a number to
them.   Start with 1 row - inserts 1 row.   2 rows - 4 rows.  4 rows -
8 rows.    2 to the power of x = 10,000 is the question of how many
inserts with different addition numbers you need to write.
1-2-4-8-16-32-64-128-256-512-1024-2048-4096-8192-16384 (14 inserts).

        This is a brute force method, but it should work fine.

        Thanks,
        Joe Snider


>Hello,
>    I am using MSSQL server 6.5 and am trying to learn how to use
>More complicated SQL queries other than just select,from,where,into.
>Here is my situation, I have a table with one record in it.  That record
>has a char field called 'accountid'. Currently that field holds the char
>exp '1000'. I want to add 10,000 records to this table, incrementing
>'accountid' each time, so when I'm done 'accoundid' should be '11,000'.
> Can I do this from ISQL using only transact-sql commands.
>    I found how to use arithmitic in my queries:
>            select convert(int,accountid) +1,

>but I couldn't find a way to "Loop" in an sql query and insert the new
>record each time.  If anyone out there can help I would greatly
>apprechiate it.
>Walt Egan
>Texas A&M College of Veterinary Medicine



 
 
 

SQL Query Question - Inserting new records?

Post by Ramesh Iy » Wed, 05 Jun 1996 04:00:00


Give this a try and see if this is what you were looking  for:

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
drop table table1    /* Drop table just incase */
go
create table table1 (accountid char(5)) /*create new table with an
account id field */
go
insert into table1 values ('1000') /* Add first record */
go


account id */




  begin                                      /* loop as many times you
want */



  end
go
select 'Total Accounts' = count (*) from table1  /* Check the total
rows inserted */
go
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Good luck


>Hello,
>    I am using MSSQL server 6.5 and am trying to learn how to use
>More complicated SQL queries other than just select,from,where,into.
>Here is my situation, I have a table with one record in it.  That record
>has a char field called 'accountid'. Currently that field holds the char
>exp '1000'. I want to add 10,000 records to this table, incrementing
>'accountid' each time, so when I'm done 'accoundid' should be '11,000'.
> Can I do this from ISQL using only transact-sql commands.
>    I found how to use arithmitic in my queries:
>            select convert(int,accountid) +1,

>but I couldn't find a way to "Loop" in an sql query and insert the new
>record each time.  If anyone out there can help I would greatly
>apprechiate it.
>Walt Egan
>Texas A&M College of Veterinary Medicine


 
 
 

SQL Query Question - Inserting new records?

Post by Yocca_at_PPG_dot_co » Wed, 05 Jun 1996 04:00:00



> Hello,
>         I am using MSSQL server 6.5 and am trying to learn how to use
> More complicated SQL queries other than just select,from,where,into.
> Here is my situation, I have a table with one record in it.  That record
> has a char field called 'accountid'. Currently that field holds the char
> exp '1000'. I want to add 10,000 records to this table, incrementing
> 'accountid' each time, so when I'm done 'accoundid' should be '11,000'.
>  Can I do this from ISQL using only transact-sql commands.
>         I found how to use arithmitic in my queries:
>                 select convert(int,accountid) +1,

> but I couldn't find a way to "Loop" in an sql query and insert the new
> record each time.  If anyone out there can help I would greatly
> apprechiate it.

> Walt Egan
> Texas A&M College of Veterinary Medicine





begin


end

Michael Yocca
Data Analyst
PPG Industries, Inc.

-- still thinking about a fitting tag line --

 
 
 

SQL Query Question - Inserting new records?

Post by Peter J. Row » Thu, 06 Jun 1996 04:00:00



> Hello,
>         I am using MSSQL server 6.5 and am trying to learn how to use
> More complicated SQL queries other than just select,from,where,into.
> Here is my situation, I have a table with one record in it.  That record
> has a char field called 'accountid'. Currently that field holds the char
> exp '1000'. I want to add 10,000 records to this table, incrementing
> 'accountid' each time, so when I'm done 'accoundid' should be '11,000'.
>  Can I do this from ISQL using only transact-sql commands.
>         I found how to use arithmitic in my queries:
>                 select convert(int,accountid) +1,

> but I couldn't find a way to "Loop" in an sql query and insert the new
> record each time.  If anyone out there can help I would greatly
> apprechiate it.

> Walt Egan
> Texas A&M College of Veterinary Medicine


Walt,

I'm not sure about SQLServer 6.5 but in 4.21a you
can use the WHILE Break. The following is the quote
from the TRANSACT-SQL book page 144. "The WHILE
keyword is used to set a condition for the repeated
execution of a statement or statement block".

The only problem is their are major differences
between 4.21 and 6.5 SQL syntax. Microsoft moved to
a 'more' ANSI standard approach to their SQL
language.

Give it a try and let me know.

Regards, Pete

 
 
 

SQL Query Question - Inserting new records?

Post by Tom Vande-Stouw » Sat, 08 Jun 1996 04:00:00


Walt,

The easiest way would be to create an identical database to the one you
have now. Set up a field using the identity field, set the start to 1001
and the increment to 1. Import the records. They will have the field set
to identity from 1001 to 11000. Then copy the records to the existing
database converting the int field to a char field in the new database.

Tom Vande-Stouwe
ConPro, Inc.
http://www.conpro.org/

> > Hello,
> >         I am using MSSQL server 6.5 and am trying to learn how to use
> > More complicated SQL queries other than just select,from,where,into.
> > Here is my situation, I have a table with one record in it.  That
record
> > has a char field called 'accountid'. Currently that field holds the
char
> > exp '1000'. I want to add 10,000 records to this table, incrementing
> > 'accountid' each time, so when I'm done 'accoundid' should be
'11,000'.
> >  Can I do this from ISQL using only transact-sql commands.
> >         I found how to use arithmitic in my queries:
> >                 select convert(int,accountid) +1,

> > but I couldn't find a way to "Loop" in an sql query and insert the new
> > record each time.  If anyone out there can help I would greatly
> > apprechiate it.

> > Walt Egan
> > Texas A&M College of Veterinary Medicine