Corrected SQL script

• Terrence-607905

  SSC Rookie

  Points: 27

  More actions

  August 27, 2009 at 10:24 am

  #209273

  Great article! The table creation and data insertion script did not work due to missing declarative statements, here is the complete script:

  CREATE TABLE Employees_2000_2005

  (

  EmpID INT PRIMARY KEY NOT NULL IDENTITY,

  EmpName VARCHAR(255) NOT NULL,

  Title VARCHAR(255) NOT NULL,

  ReportsTo INT FOREIGN KEY REFERENCES Employees_2000_2005(EmpID) NULL

  )

  GO

  declare @CTO as int

  declare @DevManager as int

  declare @TestManager as int

  declare @DevLead1 as int

  declare @DevLead2 as int

  declare @TESTLead as int

  INSERT INTO Employees_2000_2005(EmpName, Title, ReportsTo)

  VALUES ('Akram', 'Chief Technology Officer', NULL)

  SELECT @CTO = @@IDENTITY

  INSERT INTO Employees_2000_2005(EmpName, Title, ReportsTo)

  VALUES ('Ranjit', 'DEV Manager', @CTO)

  SELECT @DevManager = @@IDENTITY

  INSERT INTO Employees_2000_2005(EmpName, Title, ReportsTo)

  VALUES ('Adil', 'TEST Manager', @CTO)

  SELECT @TESTManager = @@IDENTITY

  INSERT INTO Employees_2000_2005(EmpName, Title, ReportsTo)

  VALUES ('Chandan', 'DEV Leader', @DevManager)

  SELECT @DevLead1 = @@IDENTITY

  INSERT INTO Employees_2000_2005(EmpName, Title, ReportsTo)

  VALUES ('Sudeep', 'DEV Leader', @DevManager)

  SELECT @DevLead2 = @@IDENTITY

  INSERT INTO Employees_2000_2005(EmpName, Title, ReportsTo)

  VALUES ('Ashraf', 'TEST Leader', @TESTManager)

  SELECT @TESTLead = @@IDENTITY

  INSERT INTO Employees_2000_2005(EmpName, Title, ReportsTo)

  VALUES ('Dheeraj', 'DEV Engineer', @DevLead1)

  INSERT INTO Employees_2000_2005(EmpName, Title, ReportsTo)

  VALUES ('Hem', 'DEV Engineer', @DevLead1)

  INSERT INTO Employees_2000_2005(EmpName, Title, ReportsTo)

  VALUES ('Gaurav', 'DEV Engineer', @DevLead1)

  INSERT INTO Employees_2000_2005(EmpName, Title, ReportsTo)

  VALUES ('Uday', 'DEV Engineer', @DevLead2)

  INSERT INTO Employees_2000_2005(EmpName, Title, ReportsTo)

  VALUES ('Shayam', 'DEV Engineer', @DevLead2)

  INSERT INTO Employees_2000_2005(EmpName, Title, ReportsTo)

  VALUES ('Mukesh', 'TEST Engineer', @TESTLead)

  INSERT INTO Employees_2000_2005(EmpName, Title, ReportsTo)

  VALUES ('Sarfaraz', 'TEST Engineer', @TESTLead)

  --After inserting the records as similar to the above tree, we will have

  --a table which will have records something like this