How to insert records (plus some non records) from another table into a table

Question

I have two SQL Server tables, and I want to insert data from one into another.

I only want two columns from table A to be inserted into table B (which has more than two columns). I want to add a bunch of nulls and 1s into the rest of table B.

I saw this answer which helped, but what if I want to, for example:

INSERT into B(name, address, foo, bar, blah)
select name, 'B'+address from table A, 1, 1, null

'B'+address is throwing the error
Conversion failed when converting the varchar value 'B' to data type int

`INSERT into B(name, address, foo, bar, blah) select name, 'B'+convert(varchar,address), 1, 1, null from table A` — DimaSUN, Apr 13 '16 at 17:47
what is type of field address in table B? If int then you can not insert varchar into it — DimaSUN, Apr 13 '16 at 17:51
`INSERT into B(name, address, foo, bar, blah) select name, 'B'+address, 1, 1, null FROM A` — juergen d, Apr 13 '16 at 17:53
address is actually a 10 digit int. I need it preserved as B concatenated to that 10 digit int. Right now the 10 digit int is being truncated, only four digits are showing. — Amanda_Panda, Apr 13 '16 at 17:56

score 0 · Answer 1 · answered Apr 13 '16 at 18:46

Try this:

 INSERT INTO B (name, address, foo, bar, blah)
 SELECT name, 'B'+ cast(address as varchar(250)), 1, 1, NULL
 FROM table A

'or

; with CTE 
 as

 (
 SELECT NAME, 'B'+ cast(address as varchar(250)) [address], 1 AS FOO, 1 AS BAR, NULL AS BLAH 
 FROM TABLE A

 )

 INSERT INTO B (NAME,[ADRESS], FOO, BAR, BLAH)
 SELECT NAME, [ADRESS], FOO, BAR, BLAH 
 FROM CTE

How to insert records (plus some non records) from another table into a table

1 Answers1