CODEWITHSUNDEEP
sqlsql-serverdatabasedistinctsql-insert
user9835457
user9835457

Reputation: 1

Copy value from one table A to table B with some filter in the same database sql server

I have my_db in sql server latest with table A(raw data) and I want to create table B from A.

Table A

columA columB   columC columD                     columE
ali    paris    9003   2010-05-16 16:21:00.000    toy
ali    paris    902a   2010-05-16 16:21:00.000    toy
paul   paris
Yan    london
kris   london
kris   london
kris   london

table B should look like below with primary key on columA

columA columB
ali    paris
paul   paris
yan   london
kris   london

I tried to create table B with primary key and when trying insert , got error, trying update error, so I dropped and created again with no key just about same data type , I copied columA but cannot do the same with columB -->

INSERT INTO table B ( columA ) SELECT distinct( columA) FROM table A;

and get very annoying error like "invalid object name, invalid column name" with a valid script or some time, I get "The multi-part identifier "my_db.dbo.table B.columA" could not be bound"

INSERT INTO table B ( columB )
SELECT columB FROM [table A]
WHERE [my_db].[dbo].[table B].columA = (SELECT columA FROM dbo.table B);

I care more about the syntax of copying data from table A to table B in more normalized way, if someone has a clue on this type of error you are welcome.

Upvotes: 0

Views: 865

Answers (2)

Justin Cave
Justin Cave

Reputation: 231671

It appears that you just want

insert into table_b( columnA, columnB )
  select distinct columnA, columnB
    from table_a

Upvotes: 0

GMB
GMB

Reputation: 222482

Why not insert both columns at once?

INSERT INTO tableB (columA, columnB)  
SELECT distinct columA, columnB FROM tableA;

Note that distinct is not a function (it is a language keyword), so it does not take parentheses.

Upvotes: 0

Related Questions