Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 829
  • Last Modified:

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.
0
pauledwardian
Asked:
pauledwardian
  • 5
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now