Writting Column names dynamically

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

Writting Column names dynamically

Author

Topic

majolamp
Starting Member

15 Posts

Posted - 2008-02-28 : 07:31:48

Can someone please help I'm writting the following query.
SELECT
(SELECT c.column_name FROM information_schema.tables T
JOIN information_schema.columns C
ON t.table_name = c.table_name
WHERE t.table_type = 'base table' and t.table_name like 'L_%' )
INTO #TempTable FROM TableA A LEFT JOIN [Server-Name].DB_Name.dbo.TableB B ON A.ID = B.ID

I'm trying to put commas between column names. How do I go about doing that?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-28 : 07:36:04

What is your expected result?

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-28 : 07:41:14

You need to write a function which will output a CSV list of columns.

Check this for example of such function: http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

majolamp
Starting Member

15 Posts

Posted - 2008-02-28 : 08:01:36

quote:
Originally posted by madhivanan

What is your expected result?

Madhivanan

Failing to plan is Planning to fail

I'm trying to get column names from the first query separated by comma insert to the temptable. I've manage to put the comma. I'm now trying to remove the last comma before into.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-28 : 08:03:45

left(col,1,len(col)-1)

But dont know what you are trying with the output

Madhivanan

Failing to plan is Planning to fail

majolamp
Starting Member

15 Posts

Posted - 2008-02-28 : 08:43:58

quote:
Originally posted by madhivanan

left(col,1,len(col)-1)

But dont know what you are trying with the output

Madhivanan

Failing to plan is Planning to fail

Thanks for your response. Pls read below
Select
ID,
broker_name,
broker_code,
use_automation,
address1, I'M TRYING TO REMOVE THIS COMMA
INTO #TempTable FROM Table

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-28 : 09:08:30

it looks like you must be using dynamic sql to create this column list. if so, you should concatenate not c.column_name, but quotename(c.column_name). Otherwise you will break if any columns are named strangely, such as like this:

create table bad ([--this will break you] int not null)

with even more bizarre names, someone could even do damage to your server.

this is a remote possibility, but i am paranoid.

elsasoft.org

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-28 : 09:13:35

Anyway this won't work since you can not access temp table created in D-sql outside of EXEC.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

majolamp
Starting Member

15 Posts

Posted - 2008-02-28 : 09:34:11

quote:
Originally posted by jezemine

it looks like you must be using dynamic sql to create this column list. if so, you should concatenate not c.column_name, but quotename(c.column_name). Otherwise you will break if any columns are named strangely, such as like this:

create table bad ([--this will break you] int not null)

with even more bizarre names, someone could even do damage to your server.

this is a remote possibility, but i am paranoid.

Thanks a lot for your input.
Yes i'm using dynamic sql. But how do I remove that last comma before INTO

elsasoft.org

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-28 : 10:28:45

[code]
use pubs

declare @TABLE_NAME sysname
declare @TABLE_SCHEMA sysname
declare @sql nvarchar(4000)
declare @table_alias sysname

set @TABLE_NAME = 'authors'
set @TABLE_SCHEMA = 'dbo'
set @table_alias = 'a'
set @sql = ''

select
@sql = @sql+
case
when a.ORDINAL_POSITION = 1
then 'select'+char(13)+Char(10)
else ''
end+
char(9)+
@table_alias+'.'+
quotename(a.COLUMN_NAME)+
case
when a.ORDINAL_POSITION = b.LAST_ORDINAL_POSITION
then ''
else ',' end+
char(13)+Char(10)+
case
when a.ORDINAL_POSITION = b.LAST_ORDINAL_POSITION
then 'into'+char(13)+Char(10)+char(9)+'#temp'+char(13)+Char(10)+
'from'+char(13)+Char(10)+char(9)+
quotename(@TABLE_SCHEMA)+'.'+quotename(@TABLE_NAME)+' '+@table_alias
else ''
end
from
INFORMATION_SCHEMA.COLUMNS a
cross join
(
select
LAST_ORDINAL_POSITION = max(bb.ORDINAL_POSITION)
from
INFORMATION_SCHEMA.COLUMNS bb
where
bb.TABLE_SCHEMA = @TABLE_SCHEMA and
bb.TABLE_NAME = @TABLE_NAME
) b
where
a.TABLE_SCHEMA = @TABLE_SCHEMA and
a.TABLE_NAME = @TABLE_NAME
order by
a.ORDINAL_POSITION

select [@sql] = @sql

Results:

@sql
------------------------------------------
select
a.[au_id],
a.[au_lname],
a.[au_fname],
a.[phone],
a.[address],
a.[city],
a.[state],
a.[zip],
a.[contract]
into
#temp
from
[dbo].[authors] a

(1 row(s) affected)

[/code]

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-28 : 11:21:41

wow, it's even formatted all nice!

elsasoft.org

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-28 : 14:08:48

quote:
Originally posted by jezemine

wow, it's even formatted all nice!

elsasoft.org

Just a little something I had laying around.

A script like this is handy for generating statements because it saves a lot of typing so I made it look like I would write it. Notice that the script follows the same formatting pattern as the code it generates.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 16:38:10

Excellent!
If you don't care where the commas are, you can try this too

DECLARE	@SQL NVARCHAR(4000),
	@TableSchema SYSNAME,
	@TableName SYSNAME

SELECT	@TableSchema = 'Person',
	@TableName = 'Address'

SET	@SQL = ''

SELECT		@SQL = @SQL + CHAR(10) + CHAR(9) + ',' + QUOTENAME(COLUMN_NAME)
FROM		INFORMATION_SCHEMA.COLUMNS
WHERE		TABLE_SCHEMA = @TableSchema
		AND TABLE_NAME = @TableName
ORDER BY	ORDINAL_POSITION

SELECT	@SQL = 'SELECT' + CHAR(9) + ' ' + SUBSTRING(@SQL, 4, 4000) + CHAR(10),
	@SQL = @SQL + 'INTO' + CHAR(9) + '#Temp' + CHAR(10),
	@SQL = @SQL + 'FROM' + CHAR(9) + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName)

SELECT	@SQL AS [@SQL]

Result is

@SQL
--------------------------
SELECT	 [AddressID]
	,[AddressLine1]
	,[AddressLine2]
	,[City]
	,[StateProvinceID]
	,[PostalCode]
	,[rowguid]
	,[ModifiedDate]
INTO	#Temp
FROM	[Person].[Address]

(1 row(s) affected)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 16:42:31

[code]And in one string[code]DECLARE @SQL NVARCHAR(4000),
@TableSchema SYSNAME,
@TableName SYSNAME

SELECT @TableSchema = 'Person',
@TableName = 'Address'

SET @SQL = ''

SELECT @SQL = @SQL + ',' + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @TableSchema
AND TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION

SELECT @SQL = 'SELECT ' + SUBSTRING(@SQL, 2, 4000) + ' INTO #Temp FROM ',
@SQL = @SQL + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName)

SELECT @SQL AS [@SQL][/code]Result is[code]@SQL
--------------------------------------------------------------------------------------------------------------------------------------------------
SELECT [AddressID],[AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[rowguid],[ModifiedDate] INTO #Temp FROM [Person].[Address][/code]

E 12°55'05.25"
N 56°04'39.16"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-28 : 18:53:31

quote:
Originally posted by Michael Valentine Jones

Notice that the script follows the same formatting pattern as the code it generates.

I guess the next thing to do would be to write a quine that preserves it's own formatting then

elsasoft.org

majolamp
Starting Member

15 Posts

Posted - 2008-02-29 : 04:46:39

Thanks guys I've learned a lot. Just to add. I've found the following
SELECT @Line = @Line +'c.'+ QUOTENAME(c.Column_Name) + ' , '
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON t.Table_Name = c.Table_Name
WHERE t.Table_Type = 'Base Table' and t.Table_Name like 'L_%'
SELECT @Sql = SUBSTRING(@Line, 1, LEN(@Line) -1)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-29 : 05:34:48

quote:
Originally posted by majolamp

Thanks guys I've learned a lot. Just to add. I've found the following
SELECT @Line = @Line +'c.'+ QUOTENAME(c.Column_Name) + ' , '
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON t.Table_Name = c.Table_Name
WHERE t.Table_Type = 'Base Table' and t.Table_Name like 'L_%'
SELECT @Sql = SUBSTRING(@Line, 1, LEN(@Line) -1)

In my second reply I should have specified substring instead of left

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources