Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

create MSSQL-2005 table from field names in text file

Posted on 2007-08-01
5
Medium Priority
?
284 Views
Last Modified: 2010-03-19
Want to create a SQL2005 table from field names in a comma delimeted text file.  At this point, they all can be of type nvarchar - and i can go back and edit them ... i just have several tables to build each with a bunch of fields that i do NOT want to have to retype if necessary.

How do i do that?

SQL: MSSQL Server 2005
table name should be: RES
Data Type: nvarchar(50)

Thanks,

Gp.




called RES.  The field names live in a comman delimeted text file called fields.txt
0
Comment
Question by:timbersnow
  • 3
  • 2
5 Comments
 
LVL 4

Expert Comment

by:bamboo7431
ID: 19612723
/*
This is assuming fields.txt has 1 field name per line:
--START fields.txt---
Field1
Field2
Field3
--END fields.txt---
*/
DROP TABLE #FieldNames
--create a temp table to store field names
CREATE TABLE #FieldNames(fieldName VARCHAR(255))
GO
DECLARE @fieldName VARCHAR(255), @sql NVARCHAR(2000)
--read the field names from the file
BULK INSERT #FieldNames
      FROM 'c:\fields.txt'
--start building the dynamic sql string
SET @sql = 'CREATE TABLE RES('
DECLARE FieldNames CURSOR FAST_FORWARD FOR
      SELECT fieldName FROM #FieldNames
OPEN FieldNames
FETCH NEXT FROM FieldNames INTO @fieldName
WHILE (@@FETCH_STATUS = 0) BEGIN
      --append the field name and type
      SET @sql = @sql + @fieldName + ' VARCHAR(255),'
      FETCH NEXT FROM FieldNames INTO @fieldName
END
CLOSE FieldNames
DEALLOCATE FieldNames
--chop off the last comma and add the closing paren
SET @sql = Left(@sql, Len(@sql)-1) + ')'
--print the statement so that you will see what this is about to do
PRINT @sql
--execute dynamic SQL
execute sp_executesql @sql
0
 

Author Comment

by:timbersnow
ID: 19612798
you're FREAKIN' kidding me ... DUDE - thanks!  I'm gonna try it right now ... i'll be back.

Thanks ... so far!

Cheers,

Gp.
0
 
LVL 4

Accepted Solution

by:
bamboo7431 earned 2000 total points
ID: 19612801
/*
This is assuming fields.txt has 1 line with field names:
-START fields.txt---
Field1,Field2,Field3
-END fields.txt---
*/
DROP TABLE #FieldNames
CREATE TABLE #FieldNames(fieldName VARCHAR(2000))
GO
DECLARE @fieldName VARCHAR(255), @fieldNames VARCHAR(2000), @oldPos smallint, @Pos smallint, @sql NVARCHAR(2000)
BULK INSERT #FieldNames      FROM 'c:\fields.txt'
--should be only 1 record, but just in case...
SELECT TOP 1 @fieldNames=fieldName FROM #FieldNames
SET @sql = 'CREATE TABLE RES('
SELECT @oldPos = 1, @Pos = 1
WHILE @Pos >0 BEGIN
      SET @Pos = CharIndex(',', @fieldNames, @oldPos)      
      IF @Pos > 0
            SET @fieldName = Substring(@fieldNames, @oldPos, @Pos-@oldPos)
      ELSE
            SET @fieldName = Substring(@fieldNames, @oldPos, Len(@fieldNames))
      SET @sql = @sql + @fieldName + ' VARCHAR(255),'
      SET @oldPos = @Pos+1
END
SET @sql = Left(@sql, Len(@sql)-1) + ')'
PRINT @sql
execute sp_executesql @sql
0
 
LVL 4

Expert Comment

by:bamboo7431
ID: 19617541
Did it help?
0
 

Author Comment

by:timbersnow
ID: 19617826
It ABSOLUTELY did and i appreciate it.

I awarded you the points for a great job.  

Cheers,

Gp.  
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question