asked on

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.

Aneesh

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)

pauledwardian

ASKER

Thanks! Can you give me a store procedure that would index all the rows in the table. and make the index the primarily key???

pauledwardian

ASKER

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:

pauledwardian

ASKER

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

ASKER CERTIFIED SOLUTION

nishant joshi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pauledwardian

ASKER

Thanks!

pauledwardian

ASKER

Would you please look at my other question as well.
https://www.experts-exchange.com/questions/27839175/SQL-Update-Querry.html