Insert values into single record

  • Using While Loop i want to insert the values into single record like this

    Empid empname D1 D2 D3...D30

    1 asd 2

    Loop Executes second time

    Empid empname D1 D2 D3...D30

    1 asd 2 2

    Loop Executes third time

    Empid empname D1 D2 D3...D30

    1 asd 2 2 1

    Like this loop should update single record for the single employee... Thanks in advance

  • Please explain why you are doing this.

    Also, you first talk about inserts, then later updates as though they are the same thing. They are not.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Yes, it does seem a strange requirement. But why would you want to use a loop, anyway?

    INSERT INTO MyTable (Empid,empname,D1,D2, D3,...,D30)

    VALUES (1,'asd',2,2,1,...,<Val30>)

    John

  • Consider my count is 30

    using while loop i am decrement it..till it reaches zero

    count =30

    then output should be

    Empid empname D1 D2 D3...D30

    1 asd 2

    count =29

    Empid empname D1 D2 D3...D30

    1 asd 2 2

    Count=28

    Empid empname D1 D2 D3...D30

    1 asd 2 2 1

    My code is like

    WHILE @Cnt>0

    BEGIN

    INSERT INTO #Newtemp1(EmpID,Working_Days,MainTeamID,D1,D2...Help me in this insert statement

    SET @Cnt=@Cnt-1;

    END;

    END

  • ganapathy.arvindan (4/29/2016)


    Consider my count is 30

    using while loop i am decrement it..till it reaches zero

    count =30

    then output should be

    Empid empname D1 D2 D3...D30

    1 asd 2

    count =29

    Empid empname D1 D2 D3...D30

    1 asd 2 2

    Count=28

    Empid empname D1 D2 D3...D30

    1 asd 2 2 1

    My code is like

    WHILE @Cnt>0

    BEGIN

    INSERT INTO #Newtemp1(EmpID,Working_Days,MainTeamID,D1,D2...Help me in this insert statement

    SET @Cnt=@Cnt-1;

    END;

    END

    WHILE loops are slow. Most people here will refuse to give you a slow solution when much faster alternatives are available. But unless we can understand what you are trying to do, we cannot do that either.

    Tell us what the source is and what the desired end result is. Use code to do this rather than trying to waffle round it in English.

    Use the link in my signature to understand how best to present your question so that people can understand it and provide you with working code as a solution.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • You say you're going to a single record, but are you actually putting everything into one column? If so, why. That's not what a relational database management system is designed to do. Cramming everything into a single column has tons of bad implications, limitations, performance issues, etc. I'm with everyone else. Why are you doing what you're doing?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Yeah, loop is a bad idea.

    I think the data represents a month (even though he's limited it to 30 )...my guess is he's trying to pivot the data

    E.g.

    --assuming data is for a given month

    IF OBJECT_ID('tempdb..#WorkDays') IS NOT NULL DROP TABLE #WorkDays

    CREATE TABLE #WorkDays ( EmpId int, EmpName varchar(20), MainTeamId int, WorkDate date )

    INSERT INTO #WorkDays ( EmpId, EmpName, MainTeamId, WorkDate )

    VALUES

    ( 1, 'ASD', 2, '2016-05-01' ),

    ( 1, 'ASD', 2, '2016-05-01' ),

    ( 1, 'ASD', 2, '2016-05-02' )

    SELECT *

    FROM

    (

    SELECT T.EmpId, T.EmpName, T.MainTeamId, Calc.WorkDateDay

    FROM #WorkDays T

    CROSS APPLY ( SELECT DATEPART( day, T.WorkDate ) AS WorkDateDay ) AS Calc

    ) D

    PIVOT ( COUNT( WorkDateDay ) FOR WorkDateDay IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31] ) ) PVT

  • Hi, Thanks for ur reply

    My requirement is like i have two tables one is Shiftscheduler (captures only when Shift and shifttype is 2 and 4 respectively) and another table is employee (captures if shift and shift type is 1 and 2 respectively)...step 1 i have to check the shift type 1 or 2..based on that i have to choose the tables then update the temp table based on the shift

    Sample

    Input (Choose it from date to to date)

    Date Empid Shift Shifttype

    1/1/2014 2424 2 4

    1/2/2014 2424 2 4

    1/3/2014 2424 2 4

    1/4/2014 2424 1 2

    Output like

    Empid d1shift d1shifttype d2shift d2shifttype d3shift d3shifttype d4shift d4shifttype

    2424 2 4 2 4 2 4 1 2

    Please help

  • You are asking for a PIVOT here. This might help: http://www.sqlservercentral.com/articles/T-SQL/63681/

  • Sample

    Input (Choose it from date to to date)

    Date Empid Shift Shifttype

    1/1/2014 1424 2 4

    1/2/2014 1424 2 4

    1/3/2014 1424 2 4

    1/4/2014 1424 1 2

    Output like

    Empid d1shift d1shifttype d2shift d2shifttype d3shift d3shifttype d4shift d4shifttype

    2424 2 4 2 4 2 4 1 2

    It should process on daily basis..please suggest me any loop will do or any better sugesstion

    USE [process]

    GO

    /****** Object: StoredProcedure [dbo].[Emp_Shift2] Script Date: 05/02/2016 15:01:00 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE [dbo].[Emp_Shift3]

    @FromDate datetime,

    @EndDate datetime

    AS

    BEGIN

    CREATE TABLE #WorkDays ( EmpId int, EmpName varchar(20), MainTeamId int, WorkDate date )

    INSERT INTO #WorkDays ( EmpId, EmpName, MainTeamId, WorkDate )

    SELECT [Emp_Code],Shift,ShiftType,CreatedOn FROM ShiftScheduler WHERE EndDate BETWEEN @FromDate and @EndDate;

    SELECT *

    FROM

    (

    SELECT

    FROM #WorkDays T

    CROSS APPLY ( ) AS Calc

    ) D

    PIVOT ( COUNT( WorkDateDay ) FOR WorkDateDay IN ( [d1eff],[d1shifttype] [d2eff],[d2shift],[d2shifttype], [d3Eff],[d3shift],[d3shifttype], [d4eff],[d4shift],

    [d4shifttype], [d5Eff],[d5shift],[d5shifttype], [d6Eff],[d6shift],[d6shifttype],[d7],[d7shift],[d7shifttype],[d8Eff],[d8shift],[d8shifttype],

    [d9Eff],[d9shift],[d9shifttype], [d10Eff],[d10shift],[d10shifttype],[d11Eff],[d11shift],[d11shifttype], [d12Eff],[d12shift],[d12shifttype],

    [d13Eff],[d13shift],[d13shifttype],[d14Eff],[d14shift],[d14shifttype],[d15Eff],[d15shift],[d15shifttype],[d16Eff],[d16shift],[d16shifttype],

    [d17Eff],[d17shift],[d17shifttype],[d18Eff],[d18shift],[d18shifttype] ,[d19Eff],[d19shift],[d19shifttype] ,[d20Eff],[d20shift],[d20shifttype],

    [d21Eff],[d21shift],[d21shifttype],[d22Eff],[d22shift],[d22shifttype],[d23Eff],[d23shift],[d23shifttype],[d24Eff],[d24shift],[d24shifttype],[d25Eff],

    [d25shift],[d25shifttype],[d26Eff],[d26shift],[d26shifttype] ,[d27Eff],[d27shift],[d27shifttype],[d28Eff],[d28shift],[d28shifttype],[d29Eff],[d29shift],

    [d29shifttype],[d30Eff],[d30shift],[d30shifttype],[d31Eff],[d31shift],[d31shifttype] )

    ) PVT

    END

    Please help me in getting this pivot done

  • Something like this...

    DECLARE@ShiftScheduler TABLE

    (

    [Date]DATE,

    EmpidINT,

    [Shift]TINYINT,

    [ShiftType]TINYINT

    )

    INSERT@ShiftScheduler

    SELECT'20140101', 1424, 2, 4 UNION ALL

    SELECT'20140102', 1424, 2, 4 UNION ALL

    SELECT'20140103', 1424, 2, 4 UNION ALL

    SELECT'20140104', 1424, 1, 2 UNION ALL

    SELECT'20140101', 1425, 1, 2

    SELECTEmpid,

    MAX( CASE WHEN DATEPART( DAY, [Date] ) = 1 THEN [Shift] ELSE NULL END ) AS D1Shift,

    MAX( CASE WHEN DATEPART( DAY, [Date] ) = 1 THEN [ShiftType] ELSE NULL END ) AS D1ShiftType,

    MAX( CASE WHEN DATEPART( DAY, [Date] ) = 2 THEN [Shift] ELSE NULL END ) AS D2Shift,

    MAX( CASE WHEN DATEPART( DAY, [Date] ) = 2 THEN [ShiftType] ELSE NULL END ) AS D2ShiftType,

    MAX( CASE WHEN DATEPART( DAY, [Date] ) = 3 THEN [Shift] ELSE NULL END ) AS D3Shift,

    MAX( CASE WHEN DATEPART( DAY, [Date] ) = 3 THEN [ShiftType] ELSE NULL END ) AS D3ShiftType,

    MAX( CASE WHEN DATEPART( DAY, [Date] ) = 4 THEN [Shift] ELSE NULL END ) AS D4Shift,

    MAX( CASE WHEN DATEPART( DAY, [Date] ) = 4 THEN [ShiftType] ELSE NULL END ) AS D4ShiftType

    FROM@ShiftScheduler

    WHERE[Date] BETWEEN '20140101' AND '20140104'

    GROUP BY Empid

    The link given by Steve explains the method used to solve this common problem

    I have given the same below for your reference

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    You would have received an answer much earlier had you followed the steps mentioned in the link in my signature.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • USE [process]

    GO

    /****** Object: StoredProcedure [dbo].[Emp_Shift2] Script Date: 05/02/2016 15:01:00 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[Emp_Shift3]

    @FromDate datetime,

    @EndDate datetime

    AS

    BEGIN

    CREATE TABLE #WorkDays(empid1 INT,shift1 INT,shifttype1 INT)

    INSERT INTO #WorkDays(empid1,shift1,shifttype1)

    SELECT SS.Shift,SS.ShiftType,e.empid FROM ShiftScheduler SS,Ihsmaster_delop.dbo.emploee e WHERE EndDate BETWEEN @FromDate and @EndDate AND e.empid=635

    --SS.Emp_Code=e.empcode

    SELECT *

    FROM #WorkDays

    PIVOT (MIN(empid1)

    FOR [shifttype1] IN ([1],[2],[3],[4],[5],[6])

    )

    AS PVT

    END

    Output i am getting is

    shift1123456

    1NULLNULLNULLNULLNULLNULL

    4NULLNULLNULLNULLNULLNULL

    Expected is

    empid1d1d1 d2 d2 d3 d4

    635 Shift shifttype Shift shifttype Shift shifttype

    636 Shift shifttype Shift shifttype Shift shifttype

    Note Shift and shifttype are values from the temptables #WorkDays either shift and shift type either 1 or 2 and 2 and 4 respectively

    Please help

  • Find the employee is in which shift and shifttype

    If he is shift 1 shifttype 1then the record comes from employee table

    if he is shift 2 shifttype 4 then the record comes from shiftscheduler table

    For example :

    I want to know the details empid : 3321 From date 1/1/2015 to 1/10/2015

    1/1/2015 -- shift 1 shifttype 1

    1/2/2015 -- shift 1 shifttype 1

    1/3/2015 -- shift 1 shifttype 1

    1/4/2015 -- shift 1 shifttype 1

    Still this time record comes from employee table

    now shift changes

    1/5/2015 --shift 2 shifttype 4

    1/6/2015 --shift 2 shifttype 4

    Above 2 records comes from shiftscheduler table

    Again shift changes

    1/7/2015 -- shift 1 shifttype 1

    1/8/2015 -- shift 1 shifttype 1

    Above 2 records comes from employee table

    Again Shift changes

    1/9/2015 --shift 2 shifttype 4

    1/10/2015 --shift 2 shifttype 4

    Above 2 records comes from shiftscheduler table

    My expected output is

    Empid d1shift d1shifttype d2shift d2shifttype d3shift d3shifttype d4shift d4shifttype d5shift d5shifttype d6shift d6shifttype d7shift d7shifttype d8shift d8shifttype d9shift d9shifttype

    3321 1 1 1 1 1 1 1 1 2 4 2 4 1 1 1 1 2 4

    d1shift d1shifttype comes from 1/1/2015

    d2shift d2shifttype comes from 1/2/2015

    d3shift d3shifttyp comes from 1/3/2015

    d4shift d4shifttype comes from 1/4/2015

    d5shift d5shifttype comes from 1/5/2015

  • USE [process]

    GO

    /****** Object: StoredProcedure [dbo].[Emp_Shift2] Script Date: 05/02/2016 15:01:00 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[Emp_Shift3]

    @FromDate datetime,

    @EndDate datetime

    AS

    BEGIN

    ----CREATE TABLE #Newtemp(ID INT,Emp_Code VARCHAR(50),Shift INT,ShiftType INT,FromDate datetime,EndDate datetime)

    --CREATE TABLE #WorkDays ( EmpId int, EmpName varchar(20), MainTeamId int, WorkDate date,shift INT,shifttype int )

    ----INSERT INTO #Newtemp(ID,Emp_Code,Shift,ShiftType,FromDate,EndDate)

    ----SELECT [ID],[Emp_Code],Shift,ShiftType,FromDate,EndDate FROM ShiftScheduler WHERE EndDate BETWEEN @FromDate and @EndDate;

    --INSERT INTO #WorkDays ( EmpId,shift,shifttype )

    --SELECT e.empid as [EMID],SS.Shift,SS.ShiftType FROM ShiftScheduler SS,Ihsmaster_delop.dbo.emploee e WHERE EndDate BETWEEN @FromDate and @EndDate AND SS.Emp_Code=e.empcode;

    CREATE TABLE #WorkDays(empid1 INT,shift1 INT,shifttype1 INT)

    INSERT INTO #WorkDays(empid1,shift1,shifttype1)

    SELECT e.empid,SS.Shift,SS.ShiftType FROM ShiftScheduler SS,Ihsmaster_delop.dbo.emploee e WHERE EndDate BETWEEN @FromDate and @EndDate AND e.empid=635

    --SS.Emp_Code=e.empcode

    SELECT *

    FROM #WorkDays

    PIVOT (MIN(Shift1)

    FOR [shift1] IN ([1],[2],[3],[4],[5],[6],[7])

    ) AS PVT0

    END

    Output is

    empid1shifttype11234567

    635112NULLNULLNULLNULLNULL

    6354NULL2NULLNULLNULLNULLNULL

    Not actual

  • Try this...

    DECLARE @cols AS NVARCHAR(MAX),

    @query AS NVARCHAR(MAX)

    select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName)

    from yourtable

    group by ColumnName, id

    order by id

    FOR XML PATH(''), TYPE

    ).value('.', 'NVARCHAR(MAX)')

    ,1,1,'')

    set @query = N'SELECT ' + @cols + N' from

    (

    select value, ColumnName

    from yourtable

    ) x

    pivot

    (

    max(value)

    for ColumnName in (' + @cols + N')

    ) p '

    exec sp_executesql @query;

Viewing 15 posts - 1 through 15 (of 42 total)

You must be logged in to reply to this topic. Login to reply