Copy the data into the same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Copy the data into the same table

Author

Topic

soorma
Yak Posting Veteran

52 Posts

Posted - 2010-08-02 : 14:27:20

I am trying to copy the data into same table. I have 3 tables.
There are 3 things i need to do. The first 2 things i was able to do it.

1) Insert into the survey table and the get the surveyid as @@identity.
2) Then i used the @@identity to insert into the SurveyQuestions table(Copy the data into the same table) with the new surveyID.

3) Which i am not able to do it is copy the data again into surveychoices table. It is how the data is stored in the table.

This is how the data looks like

Survey
surveyid= 1 (Auto number)
title= test title
description= test title
surveystatus=1
CreatedBy=test
date= today's date

SurveyQuestions
Questionsid= 1 (autonumber)
SurveyID=1
Questions="Good Work"
AnswerType= M

Questionsid= 2 (autonumber)
SurveyID=1
Questions="bad Work"
AnswerType= S

SurveyChoices
ChoiceID=1(autonumber)
QuestionID=1
Choice=Good

ChoiceID=2(autonumber)
QuestionID=1
Choice=Bad

ChoiceID=3(autonumber)
QuestionID=2
Choice=Excellent

ChoiceID=4(autonumber)
QuestionID=2
Choice=Poor

When the user wants to select all the questions form the older survey i am able to create a new survey get the new surveyid and insert all the questions from the older survey with the new surveyid which i created. (i will be passing the old surveyid).

DECLARE @NewID INT
insert into survey(title,description, surveystatus,CreatedBy,date )
values('Yahoo title','Yahoo title',1,'test',Getdate())
SELECT @NewID = SCOPE_IDENTITY()

INSERT SurveyQuestions(surveyid, questions,answertype)
SELECT @NewID ,questions,answertype
FROM SurveyQuestions
WHERE surveyid='1'

The above query result looks like this

Survey

surveyid= 1 (Auto number)
title= test title
description= test title
surveystatus=1
CreatedBy=test
date= today's date

The new One Created
surveyid= 2 (Auto number)
title= Yahoo title
description= Yahoo title
surveystatus=1
CreatedBy=test
date= today's date

SurveyQuestions

Questionsid= 1 (autonumber)
SurveyID=1
Questions="Good Work"
AnswerType= M

Questionsid= 2 (autonumber)
SurveyID=1
Questions="bad Work"
AnswerType= S

The new One Created
Questionsid= 3 (autonumber)
SurveyID=2
Questions="Good Work"
AnswerType= M

Questionsid= 4 (autonumber)
SurveyID=2
Questions="bad Work
AnswerType= S

This thing i am not able to do it copy the data in the surveychoices table with the new questionID of the surveyquestions. I want to copy the data of questionID 1(Good, Bad), questionID2 (Excellent and Poor) with the new quetsionid(3,4) which i created in the surveySquestions.

The Data should look like this

SurveyChoices
ChoiceID=1(autonumber)
QuestionID=1
Choice=Good

ChoiceID=2(autonumber)
QuestionID=1
Choice=Bad

ChoiceID=3(autonumber)
QuestionID=2
Choice=Excellent

ChoiceID=4(autonumber)
QuestionID=2
Choice=Poor

ChoiceID=5(autonumber)
QuestionID=3
Choice=Good

ChoiceID=6(autonumber)
QuestionID=3
Choice=Bad

ChoiceID=7(autonumber)
QuestionID=4
Choice=Excellent

ChoiceID=8(autonumber)
QuestionID=4
Choice=Poor

The table structure looks like this.

Survey

CREATE TABLE [dbo].[Survey](
[SurveyId] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](max) NULL,
[Description] [varchar](max) NULL,
[SurveyStatus] [varchar](50) NULL,
[CreatedBy] [varchar](250) NULL,
[Date] [datetime] NULL,
[SurveyCompleted] [datetime] NULL,
CONSTRAINT [PK__Survey__0425A276] PRIMARY KEY CLUSTERED
(
[SurveyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SurveyQuestions

REATE TABLE [dbo].[SurveyQuestions](
[QuestionId] [int] IDENTITY(1,1) NOT NULL,
[SurveyId] [int] NULL,
[Questions] [varchar](max) NULL,
[AnswerType] [varchar](max) NULL,
CONSTRAINT [PK__SurveyQuestions__023D5A04] PRIMARY KEY CLUSTERED
(
[QuestionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SurveyChoices
CREATE TABLE [dbo].[SurveyChoices](
[ChoiceId] [int] IDENTITY(1,1) NOT NULL,
[QuestionId] [int] NULL,
[choice] [varchar](max) NULL,
CONSTRAINT [PK__SurveyChoices__7E6CC920] PRIMARY KEY CLUSTERED
(
[ChoiceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-02 : 15:11:06

Gives us an example of Step #1

Show us how you are doing the Inserts and where the source data comes from

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-02 : 15:12:22

And why don't you use Title as the survey key in the first place?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

soorma
Yak Posting Veteran

52 Posts

Posted - 2010-08-02 : 15:23:34

this is how i am inserting for step 1 and 2. I am not using title because there can be two titles with the same name.

The user clicks on the gridview that he wants to copy all the questions and options from the this survey(test survey). So i am passing the surevyid of the test survey to copy all the questions of test survey to the new survey. This is how i am doing below. Creating a new survey and copying all the questions with the new surevyID

DECLARE @NewID INT
insert into survey(title,description, surveystatus,CreatedBy,date )
values('Yahoo title','Yahoo title',1,'test',Getdate())
SELECT @NewID = SCOPE_IDENTITY()

INSERT SurveyQuestions(surveyid, questions,answertype)
SELECT @NewID ,questions,answertype
FROM SurveyQuestions
WHERE surveyid='1'(TestSurvey ID)

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-02 : 16:50:23

quote:
Originally posted by soorma
I am not using title because there can be two titles with the same name.

So then how do you know which Survey is which?

The IDENTITY Column is useless

In any case...is the Survey info all in a single file?

I wonder if you can use OUTPUT to collect all the inserted data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

soorma
Yak Posting Veteran

52 Posts

Posted - 2010-08-02 : 17:06:28

This is how i know which survey is which. I use the createdby to know what survey which person has created.

SELECT [SurveyId], [Title], description FROM [Survey] WHERE (([SurveyStatus] = @SurveyStatus) AND ([CreatedBy] = @CreatedBy))

This will give me all the survey created by that person. Then i pass the surveyID on the grid.

This will give me all the questions for the survey

SELECT SurveyQuestions.QuestionId, SurveyQuestions.SurveyId, SurveyQuestions.Questions, SurveyQuestions.AnswerType, Survey.Title FROM SurveyQuestions INNER JOIN Survey ON SurveyQuestions.SurveyId = Survey.SurveyId WHERE (SurveyQuestions.SurveyId = @SurveyId)

This will give me all the options related to the questions

SELECT * FROM [SurveyQuestions] WHERE ([QuestionId] = @QuestionId)

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-03 : 09:58:29

Can a person have a survey with the same name more than once?

And if so, do you have a date created?

If yes

My PK would be

Title, CreatedBy, CreatedDate

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

soorma
Yak Posting Veteran

52 Posts

Posted - 2010-08-03 : 11:06:08

I get ur point. But i have designed that way and programmed in asp.net. But my questions is how can i copy the data in the surveychoices table with the new questionID of the surveyquestions. I want to copy the data of questionID 1(Good, Bad), questionID2 (Excellent and Poor) with the new quetsionid(3,4) which i created in the surveySquestions.

As i told u i am able to copy the questions with the new surevyid not there options with the new questionid of the new surevyid.

Thanks for your help

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

[/quote]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources