如何在数据库中插入数据

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 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 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'， 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 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, OutTimethe 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 DecDECLARE @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' ) )aoption (maxrecursion 365);ThnaksGaurav 解决方案 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);Thanksshows errorMsg 156, Level 15, State 1, Line 16Incorrect 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); 这篇关于如何在数据库中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 1403页，肝出来的..