May 24, 2015 at 6:57 am
Hello All,
I am having three table tblTest,tblTestQuestion,tblAnswers
Each test can have multiple question and each Question can have multiple answers.
Now I am already having records in database. I wants to create clone copy of existing test except testdetails in tblTest because the test will be unique, and then insert questions and answers into their respective tables.
I was trying to create SP but stuck.
Please find below tables structure
.[tblAnswer](
[id] [int] IDENTITY(1,1) NOT NULL,
[questionId] [int] NOT NULL,
[answer] [nvarchar](500) NOT NULL,
[point] [nvarchar](20) NOT NULL,
[isCorrect] [bit] NOT NULL,
[isDeleted] [bit] NOT NULL,
[createdDate] [datetime] NOT NULL,
[updatedDate] [datetime] NULL,
CONSTRAINT [PK_tblAnswer] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblTest] Script Date: 5/24/2015 6:19:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTest](
[id] [int] IDENTITY(1,1) NOT NULL,
[userId] [bigint] NOT NULL,
[testName] [nvarchar](100) NOT NULL,
[duration] [nvarchar](20) NOT NULL,
[totalQuestion] [int] NOT NULL,
[termsCondition] [text] NOT NULL,
[isDeleted] [bit] NOT NULL,
[createdDate] [datetime] NOT NULL,
[updatedDate] [datetime] NULL,
CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblTestQuestion] Script Date: 5/24/2015 6:19:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTestQuestion](
[id] [int] IDENTITY(1,1) NOT NULL,
[testId] [int] NOT NULL,
[question] [nvarchar](500) NOT NULL,
[isDeleted] [bit] NOT NULL,
[createdDate] [datetime] NOT NULL,
[updatedDate] [datetime] NULL,
CONSTRAINT [PK_tblTestQuestion] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tblAnswer] ON
INSERT [dbo].[tblAnswer] ([id], [questionId], [answer], [point], [isCorrect], [isDeleted], [createdDate], [updatedDate]) VALUES (1, 1, N'Event handling', N'1', 0, 0, CAST(0x0000A48100000000 AS DateTime), NULL)
INSERT [dbo].[tblAnswer] ([id], [questionId], [answer], [point], [isCorrect], [isDeleted], [createdDate], [updatedDate]) VALUES (2, 1, N'Page_Load', N'1', 0, 0, CAST(0x0000A48100000000 AS DateTime), NULL)
INSERT [dbo].[tblAnswer] ([id], [questionId], [answer], [point], [isCorrect], [isDeleted], [createdDate], [updatedDate]) VALUES (3, 1, N'Rendering', N'2', 0, 0, CAST(0x0000A48100000000 AS DateTime), NULL)
INSERT [dbo].[tblAnswer] ([id], [questionId], [answer], [point], [isCorrect], [isDeleted], [createdDate], [updatedDate]) VALUES (4, 1, N'Unload', N'4', 1, 0, CAST(0x0000A48100000000 AS DateTime), NULL)
INSERT [dbo].[tblAnswer] ([id], [questionId], [answer], [point], [isCorrect], [isDeleted], [createdDate], [updatedDate]) VALUES (5, 3, N'answer1', N'1', 0, 0, CAST(0x0000A48100000000 AS DateTime), NULL)
INSERT [dbo].[tblAnswer] ([id], [questionId], [answer], [point], [isCorrect], [isDeleted], [createdDate], [updatedDate]) VALUES (7, 3, N'answer3', N'1', 0, 0, CAST(0x0000A48100000000 AS DateTime), NULL)
INSERT [dbo].[tblAnswer] ([id], [questionId], [answer], [point], [isCorrect], [isDeleted], [createdDate], [updatedDate]) VALUES (54, 29, N'dfgd', N'3453', 0, 0, CAST(0x0000A4970116666F AS DateTime), CAST(0x0000A4970116666F AS DateTime))
INSERT [dbo].[tblAnswer] ([id], [questionId], [answer], [point], [isCorrect], [isDeleted], [createdDate], [updatedDate]) VALUES (55, 29, N'fdgdfg', N'45435', 0, 0, CAST(0x0000A4970116666F AS DateTime), CAST(0x0000A4970116666F AS DateTime))
INSERT [dbo].[tblAnswer] ([id], [questionId], [answer], [point], [isCorrect], [isDeleted], [createdDate], [updatedDate]) VALUES (58, 29, N'dfgd', N'3453', 0, 0, CAST(0x0000A49800BF7636 AS DateTime), CAST(0x0000A49800BF7636 AS DateTime))
INSERT [dbo].[tblAnswer] ([id], [questionId], [answer], [point], [isCorrect], [isDeleted], [createdDate], [updatedDate]) VALUES (59, 29, N'fdgdfg', N'45435', 0, 0, CAST(0x0000A49800BF7636 AS DateTime), CAST(0x0000A49800BF7636 AS DateTime))
INSERT [dbo].[tblAnswer] ([id], [questionId], [answer], [point], [isCorrect], [isDeleted], [createdDate], [updatedDate]) VALUES (1066, 1039, N'445', N'45', 0, 0, CAST(0x0000A4A200CCC28E AS DateTime), CAST(0x0000A4A200CCC28E AS DateTime))
INSERT [dbo].[tblAnswer] ([id], [questionId], [answer], [point], [isCorrect], [isDeleted], [createdDate], [updatedDate]) VALUES (1067, 1039, N'fghfh', N'435', 0, 0, CAST(0x0000A4A200CCC28E AS DateTime), CAST(0x0000A4A200CCC28E AS DateTime))
SET IDENTITY_INSERT [dbo].[tblAnswer] OFF
SET IDENTITY_INSERT [dbo].[tblTest] ON
INSERT [dbo].[tblTest] ([id], [userId], [testName], [duration], [totalQuestion], [termsCondition], [isDeleted], [createdDate], [updatedDate]) VALUES (2, 2, N'Asp.Net', N'40', 2, N'Terms', 0, CAST(0x0000A17600000000 AS DateTime), NULL)
INSERT [dbo].[tblTest] ([id], [userId], [testName], [duration], [totalQuestion], [termsCondition], [isDeleted], [createdDate], [updatedDate]) VALUES (3, 2, N'Quality Assurance', N'3', 4, N'Terms', 0, CAST(0x0000A17600000000 AS DateTime), NULL)
INSERT [dbo].[tblTest] ([id], [userId], [testName], [duration], [totalQuestion], [termsCondition], [isDeleted], [createdDate], [updatedDate]) VALUES (11, 2, N'ret', N'retre', 0, N'', 1, CAST(0x0000A49700F52D77 AS DateTime), CAST(0x0000A49700F52D77 AS DateTime))
INSERT [dbo].[tblTest] ([id], [userId], [testName], [duration], [totalQuestion], [termsCondition], [isDeleted], [createdDate], [updatedDate]) VALUES (12, 2, N'Testwrere', N'32', 0, N'', 1, CAST(0x0000A49700F54921 AS DateTime), CAST(0x0000A49700F54921 AS DateTime))
INSERT [dbo].[tblTest] ([id], [userId], [testName], [duration], [totalQuestion], [termsCondition], [isDeleted], [createdDate], [updatedDate]) VALUES (13, 2, N'gfhgfh', N'gfhg', 1, N'', 1, CAST(0x0000A497010555E7 AS DateTime), CAST(0x0000A497010555E7 AS DateTime))
INSERT [dbo].[tblTest] ([id], [userId], [testName], [duration], [totalQuestion], [termsCondition], [isDeleted], [createdDate], [updatedDate]) VALUES (15, 2, N'Asp.Net1', N'23', 1, N'', 1, CAST(0x0000A497010BA322 AS DateTime), CAST(0x0000A497010BA322 AS DateTime))
INSERT [dbo].[tblTest] ([id], [userId], [testName], [duration], [totalQuestion], [termsCondition], [isDeleted], [createdDate], [updatedDate]) VALUES (16, 2, N'Asp.Net3', N'23', 1, N'', 0, CAST(0x0000A497010E8D83 AS DateTime), CAST(0x0000A497010E8D83 AS DateTime))
INSERT [dbo].[tblTest] ([id], [userId], [testName], [duration], [totalQuestion], [termsCondition], [isDeleted], [createdDate], [updatedDate]) VALUES (17, 2, N'Asp.Net4', N'23', 1, N'', 0, CAST(0x0000A497010F27E8 AS DateTime), CAST(0x0000A497010F27E8 AS DateTime))
INSERT [dbo].[tblTest] ([id], [userId], [testName], [duration], [totalQuestion], [termsCondition], [isDeleted], [createdDate], [updatedDate]) VALUES (18, 2, N'Asp.Net6', N'23', 1, N'', 0, CAST(0x0000A497011013A9 AS DateTime), CAST(0x0000A497011013A9 AS DateTime))
INSERT [dbo].[tblTest] ([id], [userId], [testName], [duration], [totalQuestion], [termsCondition], [isDeleted], [createdDate], [updatedDate]) VALUES (19, 2, N'Asp.Net7', N'34', 1, N'', 1, CAST(0x0000A4970116CFB3 AS DateTime), CAST(0x0000A4970116CFB4 AS DateTime))
SET IDENTITY_INSERT [dbo].[tblTest] OFF
SET IDENTITY_INSERT [dbo].[tblTestQuestion] ON
INSERT [dbo].[tblTestQuestion] ([id], [testId], [question], [isDeleted], [createdDate], [updatedDate]) VALUES (1, 3, N'What is last stage of the web forms lifecycle?', 0, CAST(0x0000A48100000000 AS DateTime), NULL)
INSERT [dbo].[tblTestQuestion] ([id], [testId], [question], [isDeleted], [createdDate], [updatedDate]) VALUES (3, 3, N'What is polymorphism?', 0, CAST(0x0000A48100000000 AS DateTime), NULL)
INSERT [dbo].[tblTestQuestion] ([id], [testId], [question], [isDeleted], [createdDate], [updatedDate]) VALUES (4, 3, N'Asp.Net', 0, CAST(0x0000A4930091F83F AS DateTime), CAST(0x0000A4930091F83F AS DateTime))
INSERT [dbo].[tblTestQuestion] ([id], [testId], [question], [isDeleted], [createdDate], [updatedDate]) VALUES (5, 3, N'QA', 0, CAST(0x0000A4930091F83F AS DateTime), CAST(0x0000A4930091F83F AS DateTime))
INSERT [dbo].[tblTestQuestion] ([id], [testId], [question], [isDeleted], [createdDate], [updatedDate]) VALUES (29, 18, N'fdg', 0, CAST(0x0000A4970116656A AS DateTime), CAST(0x0000A4970116656A AS DateTime))
INSERT [dbo].[tblTestQuestion] ([id], [testId], [question], [isDeleted], [createdDate], [updatedDate]) VALUES (1039, 2, N'New', 0, CAST(0x0000A4A200CCC18A AS DateTime), CAST(0x0000A4A200CCC18A AS DateTime))
SET IDENTITY_INSERT [dbo].[tblTestQuestion] OFF
"]
Any help will be greatly appreciated.
May 24, 2015 at 10:35 am
What exactly are the details you want copied? Could you post the expected results of the copy process based on the sample data you provided?
It seems that you just want to insert into tblTest a copy of a row already in tblTest, but then populate questions and answers differently for the new test?
If the new test has different questions and answers, should the values for testName, duration, and totalQuestion be the same?
In principle this should be a fairly simple INSERT INTO...SELECT FROM, but if a bunch of the details are going to be different, I'm not sure why the test information needs to be copied from an existing one.
What is the stored procedure solution you've attempted so far, and what were the issues you encountered?
I know I asked a lot of questions, but knowing exactly what you've tried and what you want to do will help greatly.
Cheers!
May 25, 2015 at 2:33 am
Hi, Thanks for quick reply.
Values for testName(this will be unique that i will pass), duration, and totalQuestion will be the same of existing test details base don testId i will pass.
I waants to copy only details and the not Ids, all the Ids in tblQuestion ,tblAnswer ... will be different that will autoGenerate.Only Sequence should be the same.
I have created Stored procedure but that is not completed.
.[usp_CreateDuplicateTest]
@testName varchar(500),
@testId bigint,
@userid bigint,
@retValue int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
declare @insertedTestId bigint
if not EXISTS(SELECT testName from tblTest WHERE UPPER(@testName) = UPPER(@testName))
BEGIN
INSERT INTO tblTest SELECT @userid, @testName,duration,totalQuestion,termsCondition,0,GETUTCDATE(),GETUTCDATE() from tblTest WHERE id=@testId
SET @insertedTestId=@@identity
INSERT INTO tblTestQuestion SELECT @insertedTestId,question,0,GETUTCDATE(),GETUTCDATE() from tblTestQuestion WHERE testId=@testId
END
END"]
May 25, 2015 at 10:13 am
Thanks! I think I have a better idea what you're trying to do now.
First, there's a bit of a logic problem. The SELECT used in the NOT EXISTS is comparing @testName to itself, so the SELECT will always return a row unless the table is empty or @testName is NULL, which means the code in the IF block will only run in those two conditions.
I imagine the intended comparison is between @testName and the testName column instead.
Second, if you want to maintain the order of the questions, you'd probably be better off adding a separate column for that, something like QuestionNumber. Relying on the identity column for that could run afoul of identity reseeds, although that's admittedly not the world's most likely scenario.
Additionally, you'd want to specify an ORDER BY clause if you wanted to maintain the order. ORDER BY is the only way to guarantee that order is maintained.
You should also be aware of the exact behavior/scope of @@IDENTITY (and SCOPE_IDENTITY()) and how it works with triggers and the like. OUTPUT...INTO might be a more appropriate method. See http://www.sqlbadpractices.com/how-not-to-retrieve-identity-value/ for a succinct explanation.
Hopefully this points you in the right direction!
Cheers!
May 25, 2015 at 10:45 am
Hi thanks for reply.
Yes you are right the comparison is in between @testname & testname.
The sp which I have written will fine upto tbltest and tbltestquestion but coming to tblAnswer table I am little bit confused that what would be the statement.
May 25, 2015 at 11:00 am
To get the answer information associated with the test id you pass to the procedure, you will need to join the tblAnswer to tblQuestion on the question ID, and use a WHERE clause to filter on tblQuestion.
I'm not sure what information you want to pull from tblAnswer, but the basic form of the SELECT query would be as follows:
SELECT *
FROM tblAnswer A
INNER JOIN tblTestQuestion Q
ON Q.ID=A.questionID
WHERE Q.testID=3 --You'd use the parameter here, instead of a hard-coded value, of course.
Cheers!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply