how to add 100 columns at a time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to add 100 columns at a time

Author

Topic

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-22 : 03:36:00

Hi all

i want to create a table in which i want 100 columns i,e column1, column2, column3, .......column100 how can i get it
thanks in advance

Sachin.Nand

2937 Posts

Posted - 2010-10-22 : 04:07:23

It is one of the worst design one can come up with.Please read about normalisation.Especially 1NF form.

PBUH

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-10-22 : 04:37:18

No. Read up on database design.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-22 : 04:52:01

Please specify your requirement to design such kind of table.
so that anybody can suggest you something.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-22 : 05:40:18

i want to create a table in which it should have 100 columns and the sequence of the column's are like column1,column2,column3,.....column100 its a small task asked by my coligue so kindly help me

With Regards
Kashyap M

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-22 : 05:41:57

quote:
Originally posted by Sachin.Nand

It is one of the worst design one can come up with.Please read about normalisation.Especially 1NF form.
PBUH

quote:

No. Read up on database design

can you provide any link for that which can help me for my requirement

With Regards
Kashyap M

Sachin.Nand

2937 Posts

Posted - 2010-10-22 : 06:20:34

http://en.wikipedia.org/wiki/1NF

PBUH

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-22 : 12:35:00

[code]DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'CREATE TABLE Foo ('
;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9),
Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)

SELECT TOP (100)
@SQL = @SQL + 'Column' + CAST(N AS VARCHAR(3)) + ' INT, '
FROM Tally;

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + ')'

SELECT @SQL[/code]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-10-22 : 13:10:21

DECLARE @SQL VARCHAR(MAX)
set @sql = ''
SELECT @SQL = @sql +', Col'+convert(varchar(3),number) +' INT'
FROM
master..spt_values
where type = 'p' and number between 0 and 100
select 'CREATE TABLE foo ('+ substring(@sql,2,5000) + ')'

Jim

Everyday I learn something that somebody else already knew

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-23 : 00:36:23

that's worth a ton thanks lamprey and jimf for your help

With Regards
Kashyap M

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-23 : 03:51:26

I have a database where I use SPARSE columns intentionally.
There are at this moment about 8,000 columns. The reason I decided for this design is to avoid EAV model.

There are about 25 million records and each record/customer utilize about 100-150 columns each. So there are many columns with NULL so SPARSE columns does it job greatly.

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-23 : 04:02:19

That would be a good option if you're on 2008 or later

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-23 : 06:00:00

i am using sql 2005

With Regards
Kashyap M

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-27 : 13:43:59

i really cant understand reason for this requirement though

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-28 : 02:50:12

quote:
Originally posted by visakh16

i really cant understand reason for this requirement though

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

OP already stated the reason

quote:
its a small task asked by my coligue

Madhivanan

Failing to plan is Planning to fail

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-28 : 03:07:55

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

i really cant understand reason for this requirement though

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

OP already stated the reason

quote:
its a small task asked by my coligue

Madhivanan

Failing to plan is Planning to fail

yup Madhivanan

With Regards
Kashyap M

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-10-29 : 23:49:40

quote:
Originally posted by Peso

I have a database where I use SPARSE columns intentionally.
There are at this moment about 8,000 columns. The reason I decided for this design is to avoid EAV model.

There are about 25 million records and each record/customer utilize about 100-150 columns each. So there are many columns with NULL so SPARSE columns does it job greatly.

N 56°04'39.26"
E 12°55'05.63"

yikes! does SSMS barf if you do this:

select top 10 * from sparseTable

would be a lot of right scrolling!

elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-30 : 03:59:48

You can't do "SELECT *" on sparse columns...

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-30 : 04:02:01

What really happens is that there is one über-column (xml) which holds all columns/elements.
SQL Server exposes the named elements as columns for you. That is basically what sparse columns do.

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-01 : 11:57:19

more details here

http://visakhm.blogspot.com/2010/03/sparse-columns-and-column-sets-in-sql.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-01 : 16:29:12

Visakh, you forgot the third alternative

INSERT Product (AttributeConsolidated)
VALUES ('<ProductAttrib1>1</ProductAttrib1>
<ProductAttrib2>2</ProductAttrib2>
<ProductAttrib3>3</ProductAttrib3>
<ProductAttrib4>4</ProductAttrib4>')

N 56°04'39.26"
E 12°55'05.63"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources