how to insert data in databse

Question

0.00/5 (No votes)

See more:

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

SQL

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

Posted 28-Jan-13 17:27pm

demoninside9

Add a Solution

Comments

RDBurmon 28-Jan-13 23:50pm

Hello demoninside9 , Mr Andrew have provide very good solution with clear explanation. Please implement and if helps then vote it and mark your query as "[SOLVED] by accepting the solution.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

chaau · Answer 1 · 2013-01-28T17:45:00

Solution 1

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):

SQL

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);

Posted 28-Jan-13 17:45pm

chaau

Comments

RDBurmon 28-Jan-13 23:49pm

Thanks Andrew

demoninside9 29-Jan-13 0:15am

shows error
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'CONVERT'.

chaau 29-Jan-13 4:53am

this is because you need to substitute the MyTable and the Users, as well as UserID in the query above with the real table names. I guess you have already designed and created a table that will hold the login information

demoninside9 29-Jan-13 23:47pm

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 DATE(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )
option (maxrecursion 365);

shows error
'DATE' is not a recognized built-in function name.
see the link http://www.freeimagehosting.net/h3v76

chaau 29-Jan-13 23:55pm

There is no DATE(WEEKDAY, DateValue ) in my answer. I used DATENAME.

demoninside9 30-Jan-13 1:08am

shows error
Incorrect syntax near the keyword 'CONVERT'.
see the image below >>
http://www.freeimagehosting.net/7mt53

sisir patro · Answer 2 · 2013-01-28T20:25:00

HI,

Try like this:

SQL

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

demoninside9 · Answer 3 · 2013-01-28T18:15:00

Solution 2

shows error

SQL

Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'CONVERT'.

SQL

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);

Posted 28-Jan-13 18:15pm

demoninside9

Updated 29-Jan-13 18:15pm

v9

Comments

RDBurmon 29-Jan-13 0:36am

I have updated above solution Try now

demoninside9 29-Jan-13 0:56am

still same error :(

RDBurmon 29-Jan-13 1:21am

Try now

demoninside9 29-Jan-13 1:34am

shows 'DATETIME' is not a recognized built-in function name.

RDBurmon 29-Jan-13 1:37am

Try now

demoninside9 29-Jan-13 1:38am

'DATE' is not a recognized built-in function name.

demoninside9 29-Jan-13 1:40am

i am using Sql Server 2008

RDBurmon 29-Jan-13 3:37am

Try now

demoninside9 29-Jan-13 3:49am

'VARCHAR' is not a recognized built-in function name.

RDBurmon 29-Jan-13 5:49am

Try now

demoninside9 29-Jan-13 5:57am

'DATE' is not a recognized built-in function name.

RDBurmon 29-Jan-13 6:43am

Try now

demoninside9 29-Jan-13 21:07pm

'DATE' is not a recognized built-in function name.

see the link http://www.freeimagehosting.net/h3v76

RDBurmon 30-Jan-13 0:15am

Try now and if any problem share the image by same method

demoninside9 30-Jan-13 1:05am

shows error "Operand type clash: date is incompatible with int"
here is the image
http://www.freeimagehosting.net/pfqud

RDBurmon 30-Jan-13 1:12am

What is type of "DateValue" in DateList table ?

demoninside9 30-Jan-13 1:17am

DateTime type

http://www.freeimagehosting.net/biyj4

RDBurmon 30-Jan-13 1:51am

Comments "Insert statement" and try to run

demoninside9 30-Jan-13 2:01am

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);

shows error
Incorrect syntax near ')'.

RDBurmon 30-Jan-13 2:05am

Uncomment last line and try

demoninside9 30-Jan-13 2:14am

Command(s) completed successfully.

RDBurmon 30-Jan-13 2:16am

Ok then comment out last sentence also and put comma before Insert . rerun and revert back

demoninside9 30-Jan-13 2:18am

Incorrect syntax near ','.

RDBurmon 30-Jan-13 2:20am

attach image

demoninside9 30-Jan-13 2:21am

http://www.freeimagehosting.net/oeyvt

RDBurmon 30-Jan-13 2:25am

not comma try (;)

demoninside9 30-Jan-13 2:26am

Incorrect syntax near ';'.

RDBurmon 30-Jan-13 2:27am

Attach image

RDBurmon 30-Jan-13 2:29am

Try this
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)
) ;

demoninside9 30-Jan-13 2:31am

http://www.freeimagehosting.net/ahcnt

demoninside9 30-Jan-13 2:29am

http://www.freeimagehosting.net/nmaag

RDBurmon 30-Jan-13 3:19am

Remove ; and then try

demoninside9 30-Jan-13 3:42am

Incorrect syntax near ')'.

http://www.freeimagehosting.net/eyo2z

RDBurmon 30-Jan-13 5:06am

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)
)
SELECT DateValue FROM DateList;
Try this

demoninside9 30-Jan-13 5:31am

Operand type clash: date is incompatible with int

http://www.freeimagehosting.net/4dap1

RDBurmon 30-Jan-13 5:35am

I don't have SQL 2008 to validate

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 convert(VARCHAR(15),DateValue + 1,101)
FROM DateList
WHERE convert(VARCHAR(15),DateValue + 1,101) < convert(VARCHAR(15), @EntDt, 101)
)
SELECT DateValue
FROM DateList;

Try this

demoninside9 30-Jan-13 5:43am

same error

Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int

RDBurmon 30-Jan-13 5:49am

Try this

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 convert(VARCHAR(15),DATEADD(dd,1,DateValue ),101)
FROM DateList
WHERE convert(VARCHAR(15),DATEADD(dd,1,DateValue ),101) < convert(VARCHAR(15), @EntDt, 101)
)
SELECT DateValue
FROM DateList;

demoninside9 30-Jan-13 5:50am

Types don't match between the anchor and the recursive part in column "DateValue" of recursive query "DateList".

RDBurmon 30-Jan-13 5:52am

Image

demoninside9 30-Jan-13 6:00am

http://www.freeimagehosting.net/9uc12

RDBurmon 30-Jan-13 6:10am

Try this

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 convert(VARCHAR(15),@STARTDATE,101) DateValue

UNION ALL

SELECT convert(VARCHAR(15),DATEADD(dd,1,DateValue ),101) DateValue
FROM DateList
WHERE convert(VARCHAR(15),DATEADD(dd,1,DateValue ),101) < convert(VARCHAR(15), @EntDt, 101)
)
SELECT DateValue
FROM DateList;

demoninside9 30-Jan-13 6:15am

working http://www.freeimagehosting.net/culeu

demoninside9 30-Jan-13 6:25am

now I need to put insert option ?

RDBurmon 31-Jan-13 7:18am

Yes put it back and see what happen.

how to insert data in databse

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us