insert - Selecting Columns

15 Jul 2005 4:20 PM

Shahriar

How could I achieve this easily?

I have 50 fields in my table and I want to insert a new row from an existing
row with changing the value of 2 fields only. I want to somehow specify the
two field names I want changed in my insert statement along with the
remaining field names without typing each field name.
Should I use sp_columns 'tablename' to get all fields names and generate the
statement or is there an alternative to this?

Thanks
Shahriar

15 Jul 2005 4:22 PM

Aaron Bertrand [SQL Server MVP]

Why not insert the whole row and then update the two columns afterward?

Show quote

"Shahriar" <Shahr***@discussions.microsoft.com> wrote in message
news:AC456E45-6379-4CF5-AADC-3E500A7F35D3@microsoft.com...
> How could I achieve this easily?
>
> I have 50 fields in my table and I want to insert a new row from an
> existing
> row with changing the value of 2 fields only. I want to somehow specify
> the
> two field names I want changed in my insert statement along with the
> remaining field names without typing each field name.
> Should I use sp_columns 'tablename' to get all fields names and generate
> the
> statement or is there an alternative to this?
>
> Thanks
> Shahriar

15 Jul 2005 4:57 PM

Shahriar

What about Identity columns?

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> Why not insert the whole row and then update the two columns afterward?
>
>
>
>
> "Shahriar" <Shahr***@discussions.microsoft.com> wrote in message
> news:AC456E45-6379-4CF5-AADC-3E500A7F35D3@microsoft.com...
> > How could I achieve this easily?
> >
> > I have 50 fields in my table and I want to insert a new row from an
> > existing
> > row with changing the value of 2 fields only. I want to somehow specify
> > the
> > two field names I want changed in my insert statement along with the
> > remaining field names without typing each field name.
> > Should I use sp_columns 'tablename' to get all fields names and generate
> > the
> > statement or is there an alternative to this?
> >
> > Thanks
> > Shahriar
>
>
>

15 Jul 2005 5:26 PM

Aaron Bertrand [SQL Server MVP]

As David said, it is trivial to drag the list of columns from the Object
Browser in Query Analyzer, then just leave the first column off...

Show quote

"Shahriar" <Shahr***@discussions.microsoft.com> wrote in message
news:CE41134F-BC2E-468A-A8BB-26226894D5A3@microsoft.com...
> What about Identity columns?
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>> Why not insert the whole row and then update the two columns afterward?

15 Jul 2005 4:41 PM

David Portas

I would just list the names. It takes less than two seconds to drag the
list of names from the Object Browser in Query Analyzer. Listing the
names explicitly makes for much more reliable code than using SELECT *.

--
David Portas
SQL Server MVP
--