how to build sql query for large tables

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

SQL Server 2000 Forums

SQL Server Development (2000)

how to build sql query for large tables

Author

Topic

meetas
Starting Member

6 Posts

Posted - 2003-11-11 : 12:48:04

What is the better way to "write" sql statement when table is large like with 40 fields or so.

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-11-11 : 12:55:18

40 columns is large?

Search the forums for "code generation", Merkin has a snippet of code that will query the INFORMATION_SCHEMA views and return all the column names, separated by commas. You can then copy and paste that into your query window. There are other tools mentioned that do the same thing.

meetas
Starting Member

6 Posts

Posted - 2003-11-11 : 13:27:41

Thanks for your answer.

I searched for "code generation" in Forum. It pulls up quite a few messages. Can I seach this article under any other topic?

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-11-11 : 15:18:35

You can look into CodeSmith http://www.ericjsmith.net/codesmith Absolutely amazing tool. Has saved us tons of time!

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-11 : 15:58:31

[code]

USE NorthWind

DECLARE @TBName sysname, @SQL varchar(8000)

SELECT @TBName = 'Cust', @SQL = ''

SELECT @SQL = @SQL + RTRIM(SQL) FROM (
--SELECT SQL FROM (
SELECT RTRIM(' SELECT ' + RTRIM(COLUMN_NAME)) As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @TBName+'%'
AND ORDINAL_POSITION = 1
UNION ALL
SELECT RTRIM(', ' + RTRIM(COLUMN_NAME)) As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @TBName+'%'
AND ORDINAL_POSITION <> 1
UNION ALL
SELECT RTRIM(' FROM [' + RTRIM(TABLE_NAME) + ']') As SQL, TABLE_NAME, 4 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @TBName+'%'
AND ORDINAL_POSITION = 1
UNION ALL
SELECT RTRIM(' GO ') As SQL, TABLE_NAME, 5 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @TBName+'%'
AND ORDINAL_POSITION = 1
) AS XXX
Order By TABLE_NAME, SQL_Group, Row_Order

SELECT @SQL

EXEC(@SQL)

[/code]

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-12 : 14:54:37

query analyzer lets you easily script out long lists of column names for SELECT's whcih you can use as well.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-12 : 15:25:59

quote:
Originally posted by jsmith8858

query analyzer lets you easily script out long lists of column names for SELECT's whcih you can use as well.

- Jeff

Yeah do sp_help tablename

in grid mode..then cut and paste...do it all the time...

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-12 : 16:56:16

you can also right-click on a table and choose "generate script" and then which kind, like INSERT or SELECT or whatever. it gets you started and lists all the columns in the table. can be quite handy for those that hate typing like me !

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources