Insert and select data in sql server

Question

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

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?

Answer 1

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

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:

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

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

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

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

Answer 3

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

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

Answer 4

you can use this.

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

Answer 5

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.

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

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

Answer 6

Try using this:

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

Answer 7

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

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