Creating Dynamic column names - help

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)

Creating Dynamic column names - help

Author

Topic

endsabruptly
Starting Member

12 Posts

Posted - 2004-10-26 : 12:55:44

I have written an application that reads an excel spreadsheet and uploads the data to a sql server table. Upon further study, I have decided that I should make the table and column names dynamic, as I am designing this for different excel document with multiple formats. Everything up to the point of the database INSERT works great. Is it possible to create a table and dynamically create the column names? The code sample is something I have been working on just to create the table and column names, I havent gotten to the point of inserting data yet. Any suggestions, or am I going about this incorrectly?

create procedure dbo.sp_EFM_CreateTable
(
	@HeaderList varchar(500)
)
AS
	SET NOCOUNT ON
	declare @Header varchar(50),
	declare @Pos1 int(3),
	declare @HeaderID varchar(3)

	SET @Header = LTRIM(RTRIM(@HeaderList))+ ','
	SET @Pos1 = CHARINDEX(',', @Header, 1)	

	CREATE TABLE ONEOK_EMF_Data
	(
	IF REPLACE(@Header, ',', '') <> ''
	BEGIN
		WHILE @Pos1 > 0
		BEGIN
			SET @HeaderID = LTRIM(RTRIM(LEFT(@HeaderList, @Pos1 - 1)))
			IF @HeaderID <> ''
			BEGIN
				@HeaderID varchar(50)
			END
		END
	END	
	)
GO

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-26 : 14:39:39

Build the whole create table in a string, then execute the whole create table.
sp_executesql @myCreateTableSql

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */