SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to add 100 columns at a time
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

kashyap_sql
Posting Yak Master

India
174 Posts

Posted - 10/22/2010 :  03:36:00  Show Profile  Visit kashyap_sql's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 10/22/2010 :  04:07:23  Show Profile  Reply with Quote
It is one of the worst design one can come up with.Please read about normalisation.Especially 1NF form.

PBUH

Go to Top of Page

michael.appleton
Posting Yak Master

United Kingdom
160 Posts

Posted - 10/22/2010 :  04:37:18  Show Profile  Click to see michael.appleton's MSN Messenger address  Reply with Quote
No. Read up on database design.

Edited by - michael.appleton on 10/22/2010 04:38:17
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

India
837 Posts

Posted - 10/22/2010 :  04:52:01  Show Profile  Reply with Quote
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
Go to Top of Page

kashyap_sql
Posting Yak Master

India
174 Posts

Posted - 10/22/2010 :  05:40:18  Show Profile  Visit kashyap_sql's Homepage  Reply with Quote
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
Go to Top of Page

kashyap_sql
Posting Yak Master

India
174 Posts

Posted - 10/22/2010 :  05:41:57  Show Profile  Visit kashyap_sql's Homepage  Reply with Quote
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
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 10/22/2010 :  06:20:34  Show Profile  Reply with Quote
http://en.wikipedia.org/wiki/1NF

PBUH

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4361 Posts

Posted - 10/22/2010 :  12:35:00  Show Profile  Reply with Quote
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

Edited by - Lamprey on 10/22/2010 12:36:07
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 10/22/2010 :  13:10:21  Show Profile  Reply with Quote
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
Go to Top of Page

kashyap_sql
Posting Yak Master

India
174 Posts

Posted - 10/23/2010 :  00:36:23  Show Profile  Visit kashyap_sql's Homepage  Reply with Quote
that's worth a ton thanks lamprey and jimf for your help

With Regards
Kashyap M
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/23/2010 :  03:51:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/23/2010 :  04:02:19  Show Profile  Reply with Quote
That would be a good option if you're on 2008 or later

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

Go to Top of Page

kashyap_sql
Posting Yak Master

India
174 Posts

Posted - 10/23/2010 :  06:00:00  Show Profile  Visit kashyap_sql's Homepage  Reply with Quote
i am using sql 2005

With Regards
Kashyap M
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/27/2010 :  13:43:59  Show Profile  Reply with Quote
i really cant understand reason for this requirement though

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

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 10/28/2010 :  02:50:12  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

kashyap_sql
Posting Yak Master

India
174 Posts

Posted - 10/28/2010 :  03:07:55  Show Profile  Visit kashyap_sql's Homepage  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 10/29/2010 :  23:49:40  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/30/2010 :  03:59:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You can't do "SELECT *" on sparse columns...


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/30/2010 :  04:02:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/01/2010 :  11:57:19  Show Profile  Reply with Quote
more details here

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

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

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 11/01/2010 :  16:29:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000