CODEWITHSUNDEEP
sql-serverinsert
see sharp
see sharp

Reputation: 79

Insert data from table into another in SQL Server

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

Upvotes: 0

Views: 112

Answers (2)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

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

Upvotes: 1

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

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

Upvotes: 0

Related Questions