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.

Here is SP

ALTER PROCEDURE [dbo].[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

			if(SELECT COUNT(*) from tblTestQuestion WHERE testId= @testId)>0
			BEGIN
				INSERT INTO tblTestQuestion SELECT @insertedTestId,question,0,GETUTCDATE(),GETUTCDATE() from tblTestQuestion WHERE testId=@testId
			END
			

		END
    
	
END

Please find below tables structure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[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

>> I am having three table Tests, Test_Questions, tblAnswers. Each test can have multiple question and each Question can have multiple answers. <<

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI-ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. 

And you need to read and download the PDF for: https:--www.simple-
talk.com-books-sql-books-119-sql-code-smells-

The prefrix tbl- is a design error called a tibble and we laugh at noobs who do this. Do you remember FORTRAN I and FORTRAN II? That is where we got this convetnion! The prefix usp_ is another example.

Since a table models a set, its name has to be plural or collective. A row models an element of that set. So the table name is Personnel and each rows is an employee. 

CamelCase does NOT work; your eye jumps to the Uppercase letter, then jerks back to the start of each word. It makes the code harder to debug by about 8-12% depending  on other coding errors,

>> Now I am already having records sic in database. <<

Rows are nothing  like records. That is more 1960's FORTRAN, which used sequential file systems. There were no keys so you located the records by a sequential record number. In bad SQL, noobs will use IDENTITY to mimic their old magnetic tapes. In stinking bad SQL, noobs will use GUIDs; they do NOT know that the G is for Global NOT for local data elements. It locates things in the external environment. 

>> I wants to create clone copy of existing test except test details in Tests because the test will be unique, and then insert questions and answers INTO their respective tables. <<

What math do you do with identifiers? NONE! Do why did you make identifers for tests and users BIGINT? Where is the validation and verification?

We do NOT use bit flags in SQL; that was assembly language.
 The creation_date and updated_date are audit meta data. It is both stupid and illegal to put them in the tqalbe under audit. What happens to the audit trail when you delete this row? You destroy it! 

Why do you have @return_value INTEGER OUTPUT? Let me answer that; old C family programmers expect all statements to return values, so they can write SQL to look like C. 

You have things like duration and points modeled with NVARCHAR(20) instead of a numeric value. Why do you need twenty Chinese characters to score a question? 

Why did you write UPPER(test_name) = UPPER(@test_name))?  Your DDL would guarantee that the test name is always uppercase! And where did you get that TEXT data type? 

Where is the relationship, usually a REFERENCES clause, between the questions and answers? Oh, FORTRAN files do not have references like tables do! 

Nothing you are doing is right. Nothing. Look at my credentials in SQL and consider how serious this criticism is.  

Why don't you buy a test generation package and not re-invent the wheel? They will do the grading and stats, will stand up in court, etc. 

I started to type a solution, but I found that this would be a lot easier if you changed the data model:

1) Don't use the IDENTITY property. The point with IDENTITY is that it permits high-concurrency inserts without serialisation. This is nothing you need here. It only makes things difficult.

2) Use natural questions for tblQuestions and tblAnswers. The primary key in tblQuestions should be (TestId, QuestionNo), where QuestionNo is a running number within the test. Likewise, tblAnswer should have the key (TestId, QuestionNo, AnswerNo.)

Once you have these changes in place, the cloning procedure is a lot easier to write.