{"id":66253,"date":"2006-10-16T21:44:00","date_gmt":"2006-10-16T21:44:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/10\/16\/how-can-i-create-a-table-in-a-sql-server-database\/"},"modified":"2006-10-16T21:44:00","modified_gmt":"2006-10-16T21:44:00","slug":"how-can-i-create-a-table-in-a-sql-server-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-create-a-table-in-a-sql-server-database\/","title":{"rendered":"How Can I Create a Table in a SQL Server Database?"},"content":{"rendered":"

\"Hey, \n

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

— FD<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, FD. And thanks: you\u2019ve saved the Scripting Guys!<\/P>\n

Or, at any rate, you\u2019ve 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\u2019s wedding. Before he can leave, however, he needs to do two things: 1) he needs to write the next Hey, Scripting Guy!<\/I> 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!<\/P>\n

At any rate, FD, here\u2019s a script that can create a table in a SQL Server database. We hope you like it. And Corey and Katie, congratulations; here\u2019s a script that can create a table in a SQL Server database. We hope you like it, too:<\/P>

strComputer = “atl-sql-01” <\/p>\n
Set objConnection = CreateObject(“ADODB.Connection”)<\/p>\n
objConnection.Open _\n    “Provider=SQLOLEDB;Data Source=” & strComputer & “;” & _\n        “Trusted_Connection=Yes;Initial Catalog=Master” <\/p>\n
objConnection.Execute “CREATE TABLE TestTable (UserName TEXT,TotalAmount INTEGER)”\n<\/PRE>\n
OK, admittedly, there\u2019s not much to this; remember, though, that it\u2019s the thought that counts. (And, personally, we like<\/I> 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\u2019s done we create an instance of the ADODB.Connection<\/B> 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.<\/P>\n
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:<\/P>
objConnection.Open _\n    “Provider=SQLOLEDB;Data Source=” & strComputer & “;” & _\n        “Trusted_Connection=Yes;Initial Catalog=Master”\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Yes, we know: the code says Initial Catalog=Master<\/B>. Don\u2019t worry about that; that\u2019s just the syntax we need to use. The important thing is, weird syntax or not, this does<\/I> connect us to the Master database. If you want to connect to a different database (say, your own Employees<\/I> database) then just modify the code accordinglty: Initial Catalog=Employees<\/B>.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
After we\u2019ve made the connection to the database all we need is one line of code to create a simple two-field table:<\/P>
objConnection.Execute “CREATE TABLE TestTable (UserName TEXT,TotalAmount INTEGER)”\n<\/PRE>\n
Let\u2019s talk about this line of code for a moment. What we\u2019re doing here is calling the Execute<\/B> method to execute a SQL command. And what is<\/I> that SQL command? This:<\/P>
“CREATE TABLE TestTable (UserName TEXT,TotalAmount INTEGER)”\n<\/PRE>\n
You probably don\u2019t need us to tell you that this is the command that creates a new table. And you\u2019re right: there isn\u2019t<\/I> much to this, either. We simply use the Create Table<\/B> command followed by:<\/P>\n\n\n\n\n
\u2022<\/TD>\n\n
TestTable<\/B>, the name of our new table.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
(UserName TEXT,TotalAmount INTEGER)<\/B>, 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.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. That\u2019s a good question: just what field data types are<\/I> available to you? For a complete list, take a look at the Transact-SQL Reference<\/B><\/A> on MSDN.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
And that\u2019s it: call the Execute method and you\u2019ll have your new table in no time. From that point on, we expect that you\u2019ll live happily ever after.<\/P>\n
But that\u2019s a good point: what it you don\u2019t<\/I> live happily ever after, what if you later come to regret having created this new table? Listen, don\u2019t lose any sleep over that<\/I>. If push comes to shove, just use the following script (which relies on the Drop Table<\/B> command) to delete the table from the database:<\/P>
strComputer = “atl-sql-01” <\/p>\n
Set objConnection = CreateObject(“ADODB.Connection”)<\/p>\n
objConnection.Open _\n    “Provider=SQLOLEDB;Data Source=” & strComputer & “;” & _\n        “Trusted_Connection=Yes;Initial Catalog=Master” <\/p>\n
objConnection.Execute “DROP TABLE TestTable”\n<\/PRE>\n
Easy come, easy go.<\/P>\n
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<\/I> decides to give the happy couple SQL Server scripts? <\/P>\n
Oh, well. We\u2019ll just save the receipt and they can take it back if they need to.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Well, sure, we could<\/I> just give the bride and groom money. But our concern was this: if we give Corey and Katie money then FD might say, \u201cWait: you gave them money<\/I> and all I got was a script that creates a table in a SQL Server database?\u201d We\u2019re just trying to make everyone happy.<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I create a table in a SQL Server database?— FD Hey, FD. And thanks: you\u2019ve saved the Scripting Guys! Or, at any rate, you\u2019ve 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\u2019s wedding. […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[7,19,146,3,176,5],"class_list":["post-66253","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-active-directory","tag-activex-data-objects-ado","tag-databases","tag-scripting-guy","tag-sql-server","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I create a table in a SQL Server database?— FD Hey, FD. And thanks: you\u2019ve saved the Scripting Guys! Or, at any rate, you\u2019ve 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\u2019s wedding. […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66253","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=66253"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66253\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=66253"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66253"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66253"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}