Link to home
Create AccountLog in
Avatar of FrankRam
FrankRam

asked on

Insert records from 1 table from anoter table

Hi Experts

I have a table with 150 columns
i have a another table with the same columns
how can i insert into tabe a from table b without having to list all the columns

Hopefully there is a solution for this.  I am getting lazy to scrip this
In the past i would sp_help the table and copy and paste the fields
but then I would have to go back and enter commas



thanks
Frank

Avatar of Muhammad Ahmad Imran
Muhammad Ahmad Imran
Flag of United Kingdom of Great Britain and Northern Ireland image

How about drop the table b and then create as"

CREATE TABLE TABLE_B AS SELECT * FROM TABLE_A
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
otherwise using SSMS

right-click on source table, script table as > select to > new query window
right-click on target table, script table as > insert to > new query window
paste the select from the first into the 2nd, replacing the part from "values ..." onwards
Avatar of FrankRam
FrankRam

ASKER


I tried this

insert into tblb
select * from tbla

i set the identity_insert on

however i get this

An explicit value for the identity column in table 'LOG_INV_LOT_QC' can only be specified when a column list is used and IDENTITY_INSERT is ON.


i guess i can take off the primary key settings for now
ok that seemed to work with the identity settings off

thanks cyberkiwi:

I feel like an idiot, guess i need to brush up on my dml

just had to add minor adjustment to allow solution to work.  identity setttins