Create table structure using data from another table?

[sfn6]

Hello.

I have a table (Columns) with three colums: Name, Length, Type that contains the info needed to create a new table.

I know how to create a table in Query analyzer, but how do I programatically loop through the columns table to create the columns in the new table?

I would do this manually, but there are 571 columns that need to be created.

Thanks in advance for any help!

 

[sfn6]

Never mind! I just wrote a quick routine in Access/VBA to loop and do it. I was hoping I could learn to do it in T-SQL, but time is $$ .

Here's the code if anyone's interested:

Private Sub Command1_Click()
Dim cnNow As ADODB.Connection
Dim rsNow As ADODB.Recordset
Dim strSQLcreate As String
Dim strSQLget As String
Dim cName As String
Dim cLen As String

Set cnNow = CurrentProject.Connection

Set rsNow = New ADODB.Recordset

strSQLcreate = "CREATE TABLE dbo.PubSchools ("

strSQLget = "SELECT Name, Length FROM dbo.Columns"

rsNow.Open strSQLget, cnNow, adOpenDynamic, adLockReadOnly


rsNow.MoveFirst
cName = rsNow!Name
cLen = rsNow!Length
strSQLcreate = strSQLcreate & cName & " nvarchar(" & cLen & &quot"
rsNow.MoveNext

Do Until rsNow.EOF
cName = rsNow!Name
cLen = rsNow!Length

strSQLcreate = strSQLcreate & "," & cName & " nvarchar(" & cLen & &quot"
rsNow.MoveNext
Loop

strSQLcreate = strSQLcreate + &quot"

rsNow.Close
Set rsNow = Nothing

cnNow.Execute strSQLcreate

cnNow.Close
Set cnNow = Nothing

Debug.Print strSQLcreate

End Sub