Create table using loop 1

[jmj]

I need to create a table.
The field names are sequential a1, a2, a3, a4, a5....to a100. The datatype is the same for each field.

I have other fields that need to be in the same table, but I only need them to loop to 10 (in1 to in 10 or com1 to com10)

Needless to say I don't want to write the same thing over/over if there's an easier way.
How would I use a loop to do this?
Thanks for any help!

 

[jby1]

I don't have SQL Server handy, so I cannot give you a coded example ... however

If I was faced with this problem, I would go into Query Analyzer and build up the create table statement using a loop. Then I would select the statement string and copy the output from the Output Window. This would then be the create table statement

Does this make sense?

 

[jbenson001]

Use jby1's suggestion. Run this in QA, then cut and paste the results in another QA window and run.

set nocount on
declare @i int
declare @sql varchar(8000)

set @i = 1
set @sql = 'CREATE TABLE test ' + char(13) + char(10) +'('

while @i <= 100
   begin
      set @sql = @sql +
      case 
         when @i < 100
            then  'a' + convert(varchar(3),@i) + ' varchar(10),' + char(13) + char(10)
      else
          'a' + convert(varchar(3),@i) + ' varchar(10)' + char(13) + char(10)
      end
      set @i = @i + 1
   end

select @sql = @sql + ' )'

select @sql

Jim

 

[jmj]

Thanks for the code- that worked great! I am definitely putting it in my code sample file!
J

 

[jbenson001]

Great.. glad it worked for you...

 

[earthandfire]

As an aside, you don't need to copy the output into another QA window just replace

select @sql

with

exec (@sql)




Hope this helps.

 

[jbenson001]

Good point... just make sure you check the syntax first