SQL Data Types

This is my columns in excel and I need to create table for it, what data types should I create for them in my table:

PSID,      Payroll Status      ,Clock/Employee Number      ,Last Name      ,First Name      ,Department ID      ,Reason for vacation,      Hours of Vacation Left,            Department Name

Also, I need a store procedure to delete the data in a table.
pauledwardianAsked:
Who is Participating?
 
nishant joshiTechnology Development ConsultantCommented:
your code is perfect but it should be written like below then more feasible..
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Persons]') AND type in (N'U'))
BEGIN
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Persons]') AND type in (N'U'))
BEGIN
use AdventureWorks  
DROP TABLE Persons
END
CREATE TABLE Persons
(
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
)
ALTER TABLE dbo.Persons
ADD ID INT IDENTITY(1,1)
ALTER TABLE dbo.Persons
ADD CONSTRAINT PK_Persons
PRIMARY KEY(ID)

Open in new window



Thanks,
Nishant
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
without seeing the data, it will be hard

PSID,         probably int  
Payroll Status      bit or varchar(10)
Clock/Employee Number     if it is  integer, go for int
Last Name      varchar(64)
First Name     varchar(64)
Department ID      int
Reason for vacation varchar(2000)  
Hours of Vacation Left  int
Department Name  varchar(100)
0
 
pauledwardianAuthor Commented:
Thanks! Can you give me a store procedure that would index all the rows in the table. and make the index the primarily key???
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pauledwardianAuthor Commented:
I mean like a column that increments by each row. Like 1 for row 1, 2 for row 2, 3 for row 3....
Like this:
pic
0
 
pauledwardianAuthor Commented:
I just wrote this. Do you think this will do it???
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Persons]') AND type in (N'U'))
BEGIN
CREATE TABLE Persons
(
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
)
		ALTER TABLE dbo.Persons
		ADD ID INT IDENTITY
		 ALTER TABLE dbo.Persons
		 ADD CONSTRAINT PK_Persons
		 PRIMARY KEY(ID)
END
else
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Persons]') AND type in (N'U'))
BEGIN
use AdventureWorks  
	DROP TABLE Persons
	CREATE TABLE Persons
(
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
)
		ALTER TABLE dbo.Persons
		ADD ID INT IDENTITY
		 ALTER TABLE dbo.Persons
		 ADD CONSTRAINT PK_Persons
		 PRIMARY KEY(ID)
end

Open in new window

0
 
pauledwardianAuthor Commented:
Thanks!
0
 
pauledwardianAuthor Commented:
Would you please look at my other question as well.
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_27839175.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.