Insert data from table into another in SQL Server

Question

I try to insert data from one table into another table

table 1 (from where I select data )

update

   divid  divname
    --------------
    1      abc
    2      def
    4      xyz
    5       fgh
    6        ekg
    8        sdf

table2 (table from where I insert data into)

divdw_id   divid

update: i want data like this

divdw_id    divid
001          1
002          2
003          4
004          5
005          6
006          8

I try this this query for inserting data but this shows an error

insert into table2 
values (001, Divid)

select DivId 
from Oper_Db.dbo.table1

but this shows an error

Invalid column name 'Divid'.

So how do I resolve this error?

UPDATE:

when i run only select statement query

insert into DivisionMap (divBI_Id, DiviOp_id)
 select RIGHT('000'+CAST(eindex as VARCHAR(3)),3),eindex from mydatabase.dbo.employee

then this shows like this

(No column name)    eindex
000                    0
022                   22
024                   24
025                   25
027                   27
028                   28

where as i want like this

(No column name)    eindex
000                    0
001                   22
002                  24
003                  25
004                   27
005                   28

The errormessage Invalid column name 'Divid'. clearly states the problem: Your column name is divid but your'e selecting DivId in your query which is difference. — Fabian S.
– Fabian S., Commented Nov 9, 2017 at 7:04

Jayasurya Satheesh · Accepted Answer · 2017-11-10 06:15:46Z

1

You are doing it wrong. You can only give 1 row at a time for values If you want to have the first column hardcoded values like 001 and the 2nd one with Table1.Divid then Try this :

insert into table2 (divdw_id,Divid)
 select '001',DivId from Oper_Db.dbo.table1

Or if 001 is a sequence No, then try this

insert into table2 (divdw_id,Divid)
 select ROW_NUMBER() OVER(ORDER BY DivId),DivId from Oper_Db.dbo.table1

if you need to prefix 0's to the divdw_id try this

insert into table2 (divdw_id,Divid)
 select RIGHT('000'+CAST(ROW_NUMBER() OVER(ORDER BY DivId) AS VARCHAR(10)),3),DivId from Oper_Db.dbo.table1

if you want to start the sequence from 0, use this

insert into table2 (divdw_id,Divid)
 select RIGHT('000'+CAST(ROW_NUMBER() OVER(ORDER BY DivId)-1 AS VARCHAR(10)),3),DivId from Oper_Db.dbo.table1

edited Nov 10, 2017 at 6:15

answered Nov 9, 2017 at 7:01

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

see sharp Over a year ago

this is doing wrong,, when i execute this insert 5 values against 001 where as i want only 1 value i.e. 001 1 and when i execute with 002 then 002 2

Jayasurya Satheesh Over a year ago

Then, can you update the question with your exact requirement ? @seesharp

Jayasurya Satheesh Over a year ago

@seesharp I have Updated the answer

see sharp Over a year ago

in select statement why there is divid then one which you write with cast ... because if divid series is 1,2,3,5,6 then divdw_id should be 1,2,3,4,5 against 1,2,3,5,6 ... whereas when i run your query this is inserting like this divdw_id (001,002,003,005,006) divid (001,002,003,005,006) where as i want divdwId (001,002,003,004,005) divid(001,002,003,005,006)

Jayasurya Satheesh Over a year ago

divdw_id will be a sequence number generated order by Divid and it does not have anything else with Divid.

|

Stanislav Kundii · Accepted Answer · 2017-11-09 07:59:15Z

0

https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql

insert into table2 (divdw_id, divid)
 select RIGHT('000'+CAST(DivId as VARCHAR(3)),3),DivId from Oper_Db.dbo.table1

ps fortune-teller today on vacation, so we will guess your own desires

edited Nov 9, 2017 at 7:59

answered Nov 9, 2017 at 7:01

Stanislav Kundii

2,9041 gold badge15 silver badges19 bronze badges

1 Comment

see sharp Over a year ago

this is doing wrong,, when i execute this insert 5 values against 001 where as i want only 1 value i.e. 001 1 and when i execute with 002 then 002 2

Collectives™ on Stack Overflow

Insert data from table into another in SQL Server

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related