How do I create a simple table in SQL Server 2005?

Question

I need to "use a database" and create a table with the following attributes in SQL Server 2005:

Table name is "quiz_mailing_list" Fields are: id (typical auto-increment primary key) email (varchar size 256) optIn (boolean or tinyint size 1) referringEmail (varchar size 256)

Can someone give me the command to "use the database" and the create the proper table? This is much more confusing than MySQL.

When trying:

CREATE TABLE quiz_mailing_list(
        id int identity(1,1) primary key,
        email varchar(256),
        optIn bit
        referringEmail varchar(256))

I get this error:

System.Data.SqlClient.SqlException: Incorrect syntax near 'referringEmail'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) CREATE TABLE quiz_mailing_list( id int identity(1,1) primary key, email varchar(256), optIn bit referringEmail varchar(256))

Thanks!

Answer 1

use MyDatabase
go
create table Quiz_Mailing_List
(
   ID int identity(1,1) primary key clustered,
   Email varchar(256),
   Size tinyint,
   OptIn bit,
   ReferringEmail varchar(256)
)

MSDN documentation on CREATE TABLE.

Answer 2

The command to "use a database" in a T-SQL script is

USE DatabaseName

Answer 3

USE YourDatabaseName
GO

CREATE TABLE quiz_mailing_list (
    id int identity(1,1) primary key,
    email varchar(256),
    optIn bit,
    referringEmail varchar(256))

...that should do it. And next time, if you're asking a homework related question. Tag it as such (if not, my appologies...just seems like there's an awful lot of people trying to get some free credit in class around here).

EDIT

Turns out I forgot a comma after optIn bit. Editted the original answer to be correct.

Answer 4

This should do the trick...

USE [whatever_db]
GO
/****** Object:  Table [dbo].[quiz_mailing_list]    Script Date: 09/11/2009 17:06:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[quiz_mailing_list](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [email] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [optin] [bit] NOT NULL,
    [referringEmail] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Answer 5

Sir you are missing a comma ( ,) after optIn bit. Try below code

CREATE TABLE quiz_mailing_list(
    id int identity(1,1) primary key,
    email varchar(256),
    optIn bit,
    referringEmail varchar(256))