October 16th, 2006

How Can I Create a Table in a SQL Server Database?

Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I create a table in a SQL Server database?

— FD

SpacerHey, Scripting Guy! AnswerScript Center

Hey, FD. And thanks: you’ve saved the Scripting Guys!

Or, at any rate, you’ve saved the Scripting Guy who writes this column. As it turns out, this Scripting Guy is getting ready to leave town and attend his nephew’s wedding. Before he can leave, however, he needs to do two things: 1) he needs to write the next Hey, Scripting Guy! column; and 2) he needs to come up with a wedding gift for the bride and groom. Your question has solved both his problems, and in one fell swoop!

At any rate, FD, here’s a script that can create a table in a SQL Server database. We hope you like it. And Corey and Katie, congratulations; here’s a script that can create a table in a SQL Server database. We hope you like it, too:

strComputer = “atl-sql-01” 

Set objConnection = CreateObject(“ADODB.Connection”)

objConnection.Open _ “Provider=SQLOLEDB;Data Source=” & strComputer & “;” & _ “Trusted_Connection=Yes;Initial Catalog=Master”

objConnection.Execute “CREATE TABLE TestTable (UserName TEXT,TotalAmount INTEGER)”

OK, admittedly, there’s not much to this; remember, though, that it’s the thought that counts. (And, personally, we like the thought of a script for creating a table in a SQL Server database that requires only a few lines of code.) As you can see, we start out easy enough: we simply assign the name of the SQL Server computer to a variable named strComputer. Once that’s done we create an instance of the ADODB.Connection object; as you probably already know, ADO (ActiveX Data Objects) is the technology of choice when it comes to writing scripts that interact with databases.

With the Connection object in hand we then use this line of code to connect to the SQL Server service on the computer atl-sql-01 and, in particular, to the Master database on that computer:

objConnection.Open _
    “Provider=SQLOLEDB;Data Source=” & strComputer & “;” & _
        “Trusted_Connection=Yes;Initial Catalog=Master”

Note. Yes, we know: the code says Initial Catalog=Master. Don’t worry about that; that’s just the syntax we need to use. The important thing is, weird syntax or not, this does connect us to the Master database. If you want to connect to a different database (say, your own Employees database) then just modify the code accordinglty: Initial Catalog=Employees.

After we’ve made the connection to the database all we need is one line of code to create a simple two-field table:

objConnection.Execute “CREATE TABLE TestTable (UserName TEXT,TotalAmount INTEGER)”

Let’s talk about this line of code for a moment. What we’re doing here is calling the Execute method to execute a SQL command. And what is that SQL command? This:

“CREATE TABLE TestTable (UserName TEXT,TotalAmount INTEGER)”

You probably don’t need us to tell you that this is the command that creates a new table. And you’re right: there isn’t much to this, either. We simply use the Create Table command followed by:

TestTable, the name of our new table.

(UserName TEXT,TotalAmount INTEGER), the field definitions for the new table. For this example our table contains just two fields: a text field named UserName, and an integer field named TotalAmount. Obviously you can add as many fields as your table needs: just specify the field name followed by the field data type, separating individual values (that is, individual field definitions) with commas.

Note. That’s a good question: just what field data types are available to you? For a complete list, take a look at the Transact-SQL Reference on MSDN.

And that’s it: call the Execute method and you’ll have your new table in no time. From that point on, we expect that you’ll live happily ever after.

But that’s a good point: what it you don’t live happily ever after, what if you later come to regret having created this new table? Listen, don’t lose any sleep over that. If push comes to shove, just use the following script (which relies on the Drop Table command) to delete the table from the database:

strComputer = “atl-sql-01” 

Set objConnection = CreateObject(“ADODB.Connection”)

objConnection.Open _ “Provider=SQLOLEDB;Data Source=” & strComputer & “;” & _ “Trusted_Connection=Yes;Initial Catalog=Master”

objConnection.Execute “DROP TABLE TestTable”

Easy come, easy go.

At any rate, thanks again for the question, FD, and for the idea about what to get as a wedding gift. Our only concern, of course, is this: what happens if everyone decides to give the happy couple SQL Server scripts?

Oh, well. We’ll just save the receipt and they can take it back if they need to.

Note. Well, sure, we could just give the bride and groom money. But our concern was this: if we give Corey and Katie money then FD might say, “Wait: you gave them money and all I got was a script that creates a table in a SQL Server database?” We’re just trying to make everyone happy.

Author

0 comments

Discussion are closed.