INSERT INTO without specifiying columns 1

[Exie]

Hi,

Is there a way to insert data into a table (via a select statement) without specifying column names ?

I'm trying to do something like:
insert into allCustomer
select
1 as LogID ,
'C' as ChangeType ,
getdate() as TransactionDate,
*
from customer

All of the column names/types match the target table "allCustomer" but I wanted to avoid specifying them all as the table has over 100 columns.

 

[r937]

yes, you can

r937.com | rudy.ca

 

[PerBylund]

You don't have to specify fieldnames, at least not according to SQL Server Books Online. Try this syntax:

INSERT INTO MySalesReason
SELECT SalesReasonID, Name, ModifiedDate
FROM AdventureWorks.Sales.SalesReason
WHERE ReasonType = N'Marketing';

 

[mrdenny]

You don't have to specify the column names so long as the source and destination have the same number of columns in the same order, and that none of the destination tables columns are identity fields.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[AlexCuse]

You only need to name three columns in your insert into. The other option is pad your insert with '', for the 97 other columns you don't want to insert anything to.

And I also think this specifying columns is a good practice to get into, makes your code more readable.

Why do you not want to include column names in your insert statement?

Ignorance of certain subjects is a great part of wisdom

 

[SQLSister]

You can often get away with not specifying the columns but it is usually a bad practice (unless you have an identity column which personally I think you should have in evry table). What if someone changes the column order on you, then the insert might put data into the wrong column.

If you don't want to type the columns, QA will let you drag them over from the object browser. just open the table name in the browser and display all the columns, then drag the word column over to the query and bingo , there are all the column names. Just delete the ones you don't need, such as the identity column) and add a few line breaks (because it won't) and there you are - All the columns and all spelled correctly.

I presume there is a simliar method to do this task in SQL Server 2005?

Questions about posting. See faq183-874

 

[Zathras]

Good tip. I never tried dragging stuff from the explorer window before. (Have a star.)

 

[SQLSister]

Zathras, believe me it saves me a lot of time and trouble and avoids many many typos

Questions about posting. See faq183-874