sql inserting data from one table to another

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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

SQL

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

SQL

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

Member 10397661

Updated 12-Nov-13 19:43pm

coded007

v4

Add a Solution

1 solution

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)

PIEBALDconsult · Answer 1 · 2013-11-12T15:04:00

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
...

Posted 12-Nov-13 15:04pm

PIEBALDconsult

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