Suppose that there is a User master table having all 10 users inside. Try this
with cte as
(select cast('01/06/2012' as datetime) as LoginDate,'09:30 AM' Intime,'06:30 PM' Outtime
union all
select dateadd(dd,1,LoginDate) LoginDate,Intime,Outtime
from cte
where convert(varchar,dateadd(dd,1,LoginDate),112)<='20121231')
insert into table1 (userid,LoginDate,Intime,Outtime)
select a.userid,b.*
from user_master a,cte b;
(select cast('01/06/2012' as datetime) as LoginDate,'09:30 AM' Intime,'06:30 PM' Outtime
union all
select dateadd(dd,1,LoginDate) LoginDate,Intime,Outtime
from cte
where convert(varchar,dateadd(dd,1,LoginDate),112)<='20121231')
insert into table1 (userid,LoginDate,Intime,Outtime)
select a.userid,b.*
(select cast('01/06/2012' as datetime) as Login_Date,'09:30 AM' In_Time,'06:30 PM' Out_Time
union all
select dateadd(dd,1,Login_Date) Login_Date,In_Time,OutTime from cte
where convert(varchar,dateadd(dd,1,Login_Date),112)<='2012/12/31')
insert into mtblAttendance (User_Id,Login_Date,In_Time,Out_Time)
select a.User_Id,b.*
from mtblEmployee a,cte b;
shows error
Msg 208, Level 16, State 1, Line 1
Invalid object name 'cte'.
It is our choices that show what we truly are, far more than our abilities...
(select cast('01/06/2012' as datetime) as Login_Date,'09:30 AM' In_Time,'06:30 PM' Out_Time
union all
select dateadd(dd,1,Login_Date) Login_Date,In_Time,OutTime from cte
where convert(varchar,dateadd(dd,1,Login_Date),112)<='2012/12/31')
insert into mtblAttendance (User_Id,Login_Date,In_Time,Out_Time)
select a.User_Id,b.*
from mtblEmployee a,cte b;
shows error
Msg 208, Level 16, State 1, Line 1
Invalid object name 'cte'.
Try again this whole script in italic.
with cte as (select cast('01/06/2012' as datetime) as LoginDate,'09:30 AM' Intime,'06:30 PM' Outtime union all select dateadd(dd,1,LoginDate) LoginDate,Intime,Outtime from cte where convert(varchar,dateadd(dd,1,LoginDate),112)<='20121231') insert into table1 (userid,LoginDate,Intime,Outtime) select a.userid,b.* from user_master a,cte b;
demoninside9
Participant
1260 Points
1721 Posts
query to insert in database
Jan 27, 2013 12:41 PM|LINK
Hi all,
actually I need a program doesn't matter if it'd be in sql or in c#.
I need to insert some data into database. The thing is that I need to insert data for 10 users. see the fields below.
UserId, LoginDate, InTime, OutTime
the unique part is that LoginDate, InTime, OutTime for every User from 01/06/2012 to 31/12/2012.
and the InTime is 09:30 AM and Out Time is 6:30 PM for every date from 01/06/2012 to 31/12/2012 for every UserId.
It is possible in one program in C# Or in sql query for every UserId.
Please help. How can I do this.
Thnaks
Gaurav
santosh.jagd...
Star
7625 Points
1454 Posts
Re: query to insert in database
Jan 28, 2013 05:10 AM|LINK
write stored procedure which will take input parameters as follows
@UserName, @LoginDate, @InTime and @outtime
Now get user id as follows
Now inter a row in required table
MCP
demoninside9
Participant
1260 Points
1721 Posts
Re: query to insert in database
Jan 28, 2013 05:55 AM|LINK
I need to insert for every date from 01/06/2012 to 31/12/2012.
Thanks
wmec
Contributor
6228 Points
3226 Posts
Re: query to insert in database
Jan 28, 2013 07:45 AM|LINK
Suppose that there is a User master table having all 10 users inside. Try this
with cte as
(select cast('01/06/2012' as datetime) as LoginDate,'09:30 AM' Intime,'06:30 PM' Outtime
union all
select dateadd(dd,1,LoginDate) LoginDate,Intime,Outtime
from cte
where convert(varchar,dateadd(dd,1,LoginDate),112)<='20121231')
insert into table1 (userid,LoginDate,Intime,Outtime)
select a.userid,b.*
from user_master a,cte b;
HuaMin Chen
demoninside9
Participant
1260 Points
1721 Posts
Re: query to insert in database
Jan 28, 2013 09:27 AM|LINK
(select cast('01/06/2012' as datetime) as Login_Date,'09:30 AM' In_Time,'06:30 PM' Out_Time union all select dateadd(dd,1,Login_Date) Login_Date,In_Time,OutTime from cte where convert(varchar,dateadd(dd,1,Login_Date),112)<='2012/12/31') insert into mtblAttendance (User_Id,Login_Date,In_Time,Out_Time) select a.User_Id,b.* from mtblEmployee a,cte b;shows error
santosh.jagd...
Star
7625 Points
1454 Posts
Re: query to insert in database
Jan 29, 2013 10:08 AM|LINK
whatever is your date ( from 01/06/2012 to 31/12/2012), pass it from front end to stored procedure.
you don't need to get it from SQL.
MCP
wmec
Contributor
6228 Points
3226 Posts
Re: query to insert in database
Jan 30, 2013 12:51 AM|LINK
Try again this whole script in italic.
with cte as
(select cast('01/06/2012' as datetime) as LoginDate,'09:30 AM' Intime,'06:30 PM' Outtime
union all
select dateadd(dd,1,LoginDate) LoginDate,Intime,Outtime
from cte
where convert(varchar,dateadd(dd,1,LoginDate),112)<='20121231')
insert into table1 (userid,LoginDate,Intime,Outtime)
select a.userid,b.*
from user_master a,cte b;
HuaMin Chen