Wednesday 10 April 2013

Select from one table and insert into another Table



INSERT INTO table-one
(
  -- Columns
)
SELECT 
  -- Columns
from    table-two



Example :
 
INSERT INTO Suburb
(
   suburb_id,
   suburb_name,
   state_id
)
SELECT 
   suburb_id,
   suburb_name,
   state_id
from    
   Suburb_Temp

Monday 18 February 2013

Declare Temporary Table variable / iterate over a cursor



USE [SMS]
GO
/****** Object:  StoredProcedure [dbo].[SP_StudentInvoice]    Script Date: 02/19/2013 11:19:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <ShihaFath Wazeer>
-- Create date: <19-Feb-2013>
-- Description:   <Description,,>
-- =============================================
Create PROCEDURE [dbo].[SP_StudentInvoice]

-- Add the parameters for the stored procedure here
      @GradeID      INT,
      @Year         INT,     
    
AS
BEGIN

--  Declare temporary table

DECLARE @Students  TABLE
(
SeqNo INT NOT NULL,
AdmissionNumber varchar(10) NOT NULL,
Name1 varchar(100) NULL, 
Name2 varchar(100) NULL, 
GradeID INT NOT NULL,
Section INT NOT NULL,
SectionName varchar(20) NULL, 
GradeName varchar(50) NULL, 
GradeFee Money NULL, 
SpecialDiscount Money NULL,
January varchar(10) NULL,

February    varchar(10) NULL,
March varchar(10) NULL,
April varchar(10) NULL,
May varchar(10) NULL,
June varchar(10) NULL,
July varchar(10) NULL,
August varchar(10) NULL,
September varchar(10) NULL,
October varchar(10) NULL,
November varchar(10) NULL,
December    varchar(10) NULL
);

-- Insert all students’ details to temporary table

INSERT @Students
SELECT    
s.SeqNo,
s.AdmissionNumber,
s.Name1, ISNULL(s.Name2, '') AS Name2,
s.GradeID,
s.Section,
sec.SectionName,
g.GradeName,
g.Fees as GradeFee,
isnull(s.discount,0) as SpecialDiscount,
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
''
FROM         tbl_Student AS s INNER JOIN
                      tbl_Grade AS g ON s.GradeID = g.GradeID INNER JOIN
                      tbl_Section AS sec ON s.Section = sec.SectionID
WHERE     (s.Status = 1) AND (s.isFullDiscount = 0) AND (s.GradeID = @GradeID);


-------------------------------------------------------------
 -- Variable Declaration
DECLARE @SeqNo INT
DECLARE @AdmissionNumber VARCHAR(10)
DECLARE @GradeFee Money
DECLARE @SpecialDiscount Money
DECLARE @DueStatus VARCHAR(5)

-- Cursor Declaration

DECLARE db_Studentcursor CURSOR  FOR

 -- Query For Cursor

SELECT DISTINCT
SeqNo,
AdmissionNumber,
GradeFee,
SpecialDiscount
FROM @Students


 --Open Cursor
OPEN db_Studentcursor

 -- Go to Next Recored

  FETCH NEXT FROM db_Studentcursor INTO @SeqNo,@AdmissionNumber,@GradeFee,@SpecialDiscount
   WHILE @@FETCH_STATUS = 0
   BEGIN       
  
                                                                   
            --update  @Students
            --set
            --January = -- Some Logic within student - Seq Number
            --where SeqNo = @SeqNo
           
           
             --Logic
            
      FETCH NEXT FROM db_Studentcursor INTO @SeqNo,@AdmissionNumber,@GradeFee,@SpecialDiscount
  END
-- Close Cursor

CLOSE db_Studentcursor

 DEALLOCATE db_Studentcursor



-------------------------------------------------------------
--Return Final Table 
select * from    @Students order by AdmissionNumber
END

Attach only mdf file - SQL Server



USE [master]
GO

EXEC sp_attach_single_file_db @dbname='Student',
@physname=N'C:\Shihafath\Student.mdf'
GO