sql将数据从一个表插入另一个表 [英] sql inserting data from one table to another

问题描述

问候，



有桌子--table1（id，questionid，response，points）



值



1,1，'是'，4



2,1，'不' ，4



3,1，'铜'，2



4,2，'是' ，1



5,2，'是'，2



想要将table1中的数据插入table2（id，ques1，point1，ques2，point2，ques3，point3，ques4，point4，ques5，point5）



插入响应，将值分值到相应的question1d的





如果questionid = 1



插入table2值ques1 ='是'和积分= 4



table2输出



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



1'是'4'是'1 ..........





以下是脚本。请检查这个和任何解决方案

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 

输出

id questionid回复点

1 1是4

2 1是的5

3 1 no 3

4 2 no 12

5 2是2

6 3铜14



这里的问题是ques_1和q1build为此我想插入response ='yes'转到ques_1列并且points = 4值转到q1build

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

输出



id ques_1 q1build ques_2 q2build

1是4否12

output

id ques_1 q1build ques_2 q2build
1 yes 4 no 12

推荐答案

我的第一个想法是这样的：

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

这篇关于sql将数据从一个表插入另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！