在sql server中插入并选择数据

I want to insert data from table sp_test with two columns; (empid and amount) into another table sp_emp (regisno,empid,itemid,year,month,week,amount,paymentdate).

我想从表sp_test中插入两列数据; （empid和amount）到另一个表sp_emp（regisno，empid，itemid，year，month，week，amount，paymentdate）。

This is the query I used

这是我使用的查询

Insert into sp_emp(regisno,empid,itemid,year,month,week,amount,paymentdate)
Select * from sp_test

and it returned as select list for insert statement has fewer items. How should I put the values for another column?

并且它作为insert语句的选择列表返回的项目较少。我应该如何为另一列添加值？

7 个解决方案

#1

Try using this:

试试这个：

Insert into sp_emp(empid,amount)
Select empid,amount from sp_test

#2

You can use the following, using only the needed columns:

您只能使用所需的列来使用以下内容：

INSERT INTO sp_emp (empid, amount) 
SELECT empid, amount FROM sp_test

This INSERT INTO command is only working if the other columns of sp_emp are nullable or a DEFAULT value is available. If a columns is not nullable and there is no DEFAULT value set you have to specifiy a valid value for the column. You only need to define columns in the INSERT INTO column list if there is no default value and the column is not nullable.

只有当sp_emp的其他列可以为空或DEFAULT值可用时，此INSERT INTO命令才有效。如果列不可为空并且没有设置DEFAULT值，则必须为列指定有效值。如果没有默认值且列不可为空，则只需在INSERT INTO列列表中定义列。

To set a value you can add the value to the SELECT column list as value:

要设置值，可以将值作为值添加到SELECT列列表中：

INSERT INTO sp_emp (empid, amount, regisno, itemid, year, month, week,paymentdate) 
SELECT empid, amount, 1, 1, 2017, 11, 40, '2017-11-14' FROM sp_test

#3

You have to use NULL or 0 for the remaining columns.

您必须对剩余的列使用NULL或0。

Try the following:

请尝试以下方法：

Insert into sp_emp(regisno,empid,itemid,year,month,week,amount,paymentdate)
Select NULL,EmpID,NULL,NULL,NULL,NULL,Amount,NULL from sp_test

#4

This error occurs when the number of columns in your source list does not match the number of columns on your destination.

当源列表中的列数与目标上的列数不匹配时，会发生此错误。

For example when you are trying to insert into 5 column or a table but in the select statement or values, you have specified only 4 columns or you have given 6 columns then you will get the same error.

例如，当您尝试插入5列或表但在select语句或值中时，您只指定了4列，或者您已经给出了6列，那么您将得到相同的错误。

Here, Your Query is trying to insert values to 8 Columns on the table sp_emp and for that, you are selecting all the columns from the table sp_test. So When you are doing so you must make sure that the number of columns, There order and data type match as in the destination or else you should specify the column names instead of *. So I changed your script as below - assuming that the column names are same in both tables

在这里，您的查询正在尝试将值插入到表sp_emp上的8列中，为此，您将从表sp_test中选择所有列。因此，当您这样做时，您必须确保列数，有顺序和数据类型匹配目标，否则您应指定列名而不是*。所以我改变了你的脚本如下 - 假设两个表中的列名相同

Insert into sp_emp(regisno,empid,itemid,year,month,week,amount,paymentdate)
SELECT regisno,empid,itemid,year,month,week,amount,paymentdate FROM sp_test

If there is any miss match in the data type , then you can cast the source values accordingly

如果数据类型中存在任何未匹配，则可以相应地转换源值

#5

you can use this.

你可以用它。

INSERT INTO sp_emp( empid, amount )
SELECT EmpID, Amount
FROM sp_test

#6

You have insert only specific fields which value you have get.

您只插入了您获得的特定字段。

Insert into sp_emp(empid,amount) Select empid,amount from sp_test

插入sp_emp（empid，amount）从sp_test中选择empid，amount

#7

The the columns list of the insert clause must be equal to the columns list of the select clause (both number of columns and data types).

insert子句的列列表必须等于select子句的列列表（列数和数据类型）。

If your sp_emp columns allows null values, you can specify NULL as values. If your columns have default values, you can set them by using the keyword DEFAULT as the value:

如果sp_emp列允许空值，则可以将NULL指定为值。如果您的列具有默认值，则可以使用关键字DEFAULT作为值来设置它们：

Insert into sp_emp(regisno,empid,itemid,year,month,week,amount,paymentdate)
SELECT DEFAULT,EmpID,NULL,NULL,NULL,NULL,Amount,NULL
FROM sp_test

However, a better option would be to simply omit these columns from the insert clause columns list - This will create a much simpler statement.

但是，更好的选择是简单地从insert子句列列表中省略这些列 - 这将创建一个更简单的语句。

Insert into sp_emp(empid,amount)
SELECT EmpID,Amount
FROM sp_test

Also, as a side note, you should not use the prefix sp_ for anything in your database. If anything, sp_ is naturally used with stored procedures - but Microsoft uses this prefix for the system stored procedures in SQL Server.

此外，作为旁注，您不应该为数据库中的任何内容使用前缀sp_。如果有的话，sp_自然地与存储过程一起使用 - 但是Microsoft将此前缀用于SQL Server中的系统存储过程。

#1