Click here to Skip to main content
13,000,982 members (59,872 online)
Rate this:
 
Please Sign up or sign in to vote.
Greetings,

Having table --table1(id,questionid,response,points)

values

1,1,'yes',4

2,1,'no',4

3,1,'copper',2

4,2,'yes',1

5,2,'yes',2

want to insert data from table1 into table2(id,ques1,point1,ques2,point2,ques3,point3,ques4,point4,ques5,point5)

insert response ,points values into coresponding question1d's


if questionid=1

insert into table2 values ques1='yes' and points=4

table2 output

id,ques1,point1,ques2,point2,ques3,point3,ques4,point4,ques5,point5

1 'yes' 4 'yes' 1 ..........


below are the scripts.please check this and any solution

CREATE TABLE [dbo].[surveyanswers]( 
[id] [int] IDENTITY(1,1) NOT NULL, 
[questionid] [nvarchar](50) NULL, 
[response] [nvarchar](50) NULL, 
[points] [nvarchar](50) NULL, 
CONSTRAINT [PK_surveyanswers] 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 

output
id questionid response points
1 1 yes 4
2 1 yes 5
3 1 no 3
4 2 no 12
5 2 yes 2
6 3 copper 14

Here questionid is ques_1 and q1build for this i want to insert response='yes' goes to ques_1 column and points =4 value goes to q1build

CREATE TABLE [dbo].[surveryanswers1]( 
[id] [int] IDENTITY(1,1) NOT NULL, 
[ques_1] [nvarchar](50) NULL, 
[q1build] [nvarchar](50) NULL, 
[ques_2a] [nvarchar](50) NULL, 
[q2abuild] [nvarchar](50) NULL, 
[ques_2b] [nvarchar](50) NULL, 
[q2bbuild] [nvarchar](50) NULL, 
[ques_2c] [nvarchar](50) NULL, 
[q2cbuild] [nvarchar](50) NULL, 
[ques_3] [nvarchar](50) NULL, 
[q3build] [nvarchar](50) NULL, 
[ques_4] [nvarchar](50) NULL, 
[qbuild] [nvarchar](50) NULL, 
[ques_5] [nvarchar](50) NULL, 
[qbuild5] [nvarchar](50) NULL, 
CONSTRAINT [PK_surveryanswers1] 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

output

id ques_1 q1build ques_2 q2build
1 yes 4 no 12
Posted 12-Nov-13 14:24pm
Updated 12-Nov-13 19:43pm
coded0078.1K
v4

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

My first thought is something along the lines of:

INSERT INTO table2 ( ... )
SELECT A.*
,B.response,B.points
...
FROM (SELECT * FROM table WHERE questionid=1) A
INNER JOIN (SELECT * FROM table WHERE questionid=2) B
ON A.questionid=B.questionid
...
  Permalink  
Comments
Member 10397661 12-Nov-13 21:59pm
   
below are the scripts.please check this and any solution

CREATE TABLE [dbo].[surveyanswers](
[id] [int] IDENTITY(1,1) NOT NULL,
[questionid] [nvarchar](50) NULL,
[response] [nvarchar](50) NULL,
[points] [nvarchar](50) NULL,
CONSTRAINT [PK_surveyanswers] 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


output

id questionid response points

1 1 yes 4
2 1 yes 5
3 1 no 3
4 2 no 12
5 2 yes 2
6 3 copper 14




Here questionid is ques_1 and q1build for this i want to insert response='yes' goes to ques_1 column and points =4 value goes to q1build

CREATE TABLE [dbo].[surveryanswers1](
[id] [int] IDENTITY(1,1) NOT NULL,
[ques_1] [nvarchar](50) NULL,
[q1build] [nvarchar](50) NULL,
[ques_2a] [nvarchar](50) NULL,
[q2abuild] [nvarchar](50) NULL,
[ques_2b] [nvarchar](50) NULL,
[q2bbuild] [nvarchar](50) NULL,
[ques_2c] [nvarchar](50) NULL,
[q2cbuild] [nvarchar](50) NULL,
[ques_3] [nvarchar](50) NULL,
[q3build] [nvarchar](50) NULL,
[ques_4] [nvarchar](50) NULL,
[qbuild] [nvarchar](50) NULL,
[ques_5] [nvarchar](50) NULL,
[qbuild5] [nvarchar](50) NULL,
CONSTRAINT [PK_surveryanswers1] 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


output

id ques_1 q1build ques_2 q2build

1 yes 4 no 12
PIEBALDconsult 12-Nov-13 22:55pm
   
Why would I do that?
coded007 13-Nov-13 1:48am
   
Good idea is you can maintain a survey asnwers with questionid,response and points you got. Please recheck your table sturcture

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170624.1 | Last Updated 13 Nov 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100