如何在数据库中插入数据 [英] how to insert data in databse
问题描述
大家好,
实际上我需要一个查询来将数据插入数据库。
我有一个Epmloyee表和一个考勤表。
我需要将一些数据插入数据库。问题是我需要为10个用户插入数据。看下面的字段。
UserId,LoginDate,InTime,OutTime
唯一的部分是LoginDate每个用户的,InTime,OutTime从2012年6月1日到2012年12月31日。
和InTime是09:30 AM,Out Time是6:对于每个UserId,从2012年6月1日到2012年12月31日的每个日期下午30点。
可以在C#中的一个程序中或在sql查询中每个UserId。
请帮助。我怎么能这样做。
我发现6月到12月的工作日
>
DECLARE @ STARTDATE 日期时间跨度>;
DECLARE @ EntDt datetime ;
set @ STARTDATE = ' 06/01/2012';
set @ EntDt = ' 12/31/2012';
声明 @ dcnt int ;
; DateList as
(
选择 @ STARTDATE DateValue
union all
选择 DateValue + 1 来自 DateList
其中 DateValue + 1 < ; convert ( VARCHAR ( 15 ), @ EntDt , 101 )
)
选择 count(*)作为 DayCnt 来自(
选择 DateValue,DATENAME(WEEKDAY,Dat eValue) as WEEKDAY 来自 DateList
其中 DATENAME(WEEKDAY,DateValue)不 IN ( ' 星期六',' 星期日')
)a
选项(maxrecursion 365 );
Thnaks
Gaurav
大。 90%的工作已完成。
现在,您需要做的就是将其与用户表连接,以获取系统中所有用户ID的列表,并编写一个很好的INSERT INTO SQL,与此相似(用你的表替换MyTable):
DECLARE @ STARTDATE datetime ;
DECLARE @ EntDt datetime ;
set @ STARTDATE = ' 06/01/2012';
set @ EntDt = ' 12/31/2012';
声明 @ dcnt int ;
DateList as
(
选择 @ STARTDATE DateValue
union < span class =code-keyword> all
选择 DateValue + 1 来自 DateList
其中 DateValue + 1 < convert ( VARCHAR ( 15 ), @ EntDt , 101 )
)
INSERT INTO MyTable
选择 u.UserID,
转换( DATETIME ( CONVERT ( VARCHAR ( 10 ),DateValue, 121 )+ ' 09:30:00', 121 )作为 InTime,
转换( DATETIME ( CONVERT ( VARCHAR ( 10 ),DateValue, 121 )+ ' 09:30:00', 121 )作为 OutTime
来自 DateList,用户u
where DATENAME(WEEKDAY,DateValue)不 IN (' Saturday',' 星期日')
选项(maxrecursion 365 ) ;
HI,
试试这样:
DECLARE @ STARTDATE 日期时间跨度>;
DECLARE @ EntDt datetime ;
set @ STARTDATE = ' 06/01/2012';
set @ EntDt = ' 12/31/2012';
声明 @ dcnt int ;
DateList as
(
选择 @ STARTDATE DateValue
union < span class =code-keyword> all
选择 DateValue + 1 来自 DateList
其中 DateValue + 1 < convert ( VARCHAR ( 15 ), @ EntDt , 101 )
)
INSERT INTO MyTable
选择 u.UserID,
转换( DATETIME , (DateValue)+ ' 09:30:00', 121 )作为 InTime,
转换( DATETIME ,(DateValue)+ ' 09:30:00',< span class =code-digit> 121 )作为 OutTime
来自 DateList,Users u
其中 DATENAME(WEEKDAY,DateValue)不 IN (' Saturday',' 星期日')
选项(maxrecursion 365 );
谢谢
显示错误
消息 156 ,等级 15 ,状态 1 ,行 16
关键字' CONVERT'。
DECLARE @ STARTDATE AS DATE ;
DECLARE @ EntDt AS DATE ;
set @ STARTDATE = ' 06/01/2012';
set @ EntDt = ' 12/31/2012';
声明 @ dcnt AS int ;
DateList as
(
选择 @ STARTDATE DateValue
union < span class =code-keyword> all
选择 DateValue + 1 来自 DateList
其中 DateValue + 1 < convert ( VARCHAR ( 15 ), @ EntDt , 101 )
)
INSERT INTO mtblAttendance
选择 u.User_Id,
转换( VARCHAR ( 10 ), CONVERT ( DATETIME , CONVERT ( VARCHAR ( 10 ),DateValue, 121 ))+ ' 09:30:00', 121 ) As In_Time ,
转换( VARCHAR ( 10 ), CONVERT ( DATETIME , CONVERT ( VARCHAR ( 10 ),DateValue, 121 ))+ ' 09:30:00', 121 ) As Out_Time
来自 DateList,mtblEmployee u
其中 DATENAME(dw,DateValue)不 IN ( ' 星期六',' 星期日')
选项(maxrecursion 365 );
hi all,
actually I need a query to insert data into database.
I have a Epmloyee table and a Attendance table.
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.
I found the working days from June to Dec
DECLARE @STARTDATE datetime;
DECLARE @EntDt datetime;
set @STARTDATE = '06/01/2012';
set @EntDt = '12/31/2012';
declare @dcnt int;
;with DateList as
(
select @STARTDATE DateValue
union all
select DateValue + 1 from DateList
where DateValue + 1 < convert(VARCHAR(15),@EntDt,101)
)
select count(*) as DayCnt from (
select DateValue,DATENAME(WEEKDAY, DateValue ) as WEEKDAY from DateList
where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )
)a
option (maxrecursion 365);
Thnaks
Gaurav
Great. 90% of job is done.
Now, all you need to do is join it with your "Users" table to get the list of all User IDs in the system and write a nice INSERT INTO SQL, similar to this (replace MyTable with your table):
DECLARE @STARTDATE datetime; DECLARE @EntDt datetime; set @STARTDATE = '06/01/2012'; set @EntDt = '12/31/2012'; declare @dcnt int; with DateList as ( select @STARTDATE DateValue union all select DateValue + 1 from DateList where DateValue + 1 < convert(VARCHAR(15),@EntDt,101) ) INSERT INTO MyTable select u.UserID, Convert(DATETIME(CONVERT(VARCHAR(10), DateValue, 121) + ' 09:30:00', 121) As InTime, Convert(DATETIME(CONVERT(VARCHAR(10), DateValue, 121) + ' 09:30:00', 121) As OutTime from DateList, Users u where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' ) option (maxrecursion 365);
HI,
Try like this:
DECLARE @STARTDATE datetime; DECLARE @EntDt datetime; set @STARTDATE = '06/01/2012'; set @EntDt = '12/31/2012'; declare @dcnt int; with DateList as ( select @STARTDATE DateValue union all select DateValue + 1 from DateList where DateValue + 1 < convert(VARCHAR(15),@EntDt,101) ) INSERT INTO MyTable select u.UserID, Convert(DATETIME,(DateValue) + ' 09:30:00', 121) As InTime, Convert(DATETIME, (DateValue) + ' 09:30:00', 121) As OutTime from DateList, Users u where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' ) option (maxrecursion 365);
Thanks
shows error
Msg 156, Level 15, State 1, Line 16 Incorrect syntax near the keyword 'CONVERT'.
DECLARE @STARTDATE AS DATE; DECLARE @EntDt AS DATE; set @STARTDATE = '06/01/2012'; set @EntDt = '12/31/2012'; declare @dcnt AS int; with DateList as ( select @STARTDATE DateValue union all select DateValue + 1 from DateList where DateValue + 1 < convert(VARCHAR(15),@EntDt,101) ) INSERT INTO mtblAttendance select u.User_Id, Convert(VARCHAR(10),CONVERT(DATETIME,CONVERT(VARCHAR(10), DateValue, 121)) + ' 09:30:00', 121) As In_Time, Convert(VARCHAR(10),CONVERT(DATETIME,CONVERT(VARCHAR(10), DateValue, 121)) + ' 09:30:00', 121) As Out_Time from DateList, mtblEmployee u where DATENAME (dw, DateValue ) not IN ( 'Saturday','Sunday' ) option (maxrecursion 365);
这篇关于如何在数据库中插入数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!