[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Insert Into..Select * from --- minus the Identity column -?

rich

3/28/2007 7:38:00 PM

I need to insert data into a table in DB2 from a similar table in DB1. I
want to say

Insert Into tblx Select * from server1.DB1.dbo.tblx

tblx contains 40 columns which I would like to not have to list. One of the
columns is an identity column. Is there a way I can run this insert
statement without having to list all the columns? Note: tblx on DB2 is
empty, so is there a way I can tell it to let the Identity values from
DB1.tblx in?

Thanks,
Rich
7 Answers

Aaron [SQL Server MVP]

3/28/2007 7:44:00 PM

0

> Insert Into tblx Select * from server1.DB1.dbo.tblx
>
> tblx contains 40 columns which I would like to not have to list.

Wow, how lazy we have become!

Have you considered dragging the "Columns" node from Object Explorer (F8 in
Management Studio or Query Analyzer) and then deleting the identity
column???


ML

3/28/2007 7:44:00 PM

0

In your favorute query editing tool find the object browser, then click and
drag the columns "folder" of your table to the query window. Then delete
columns you don't need. Both QA and SSMS have this functionality.


ML

---
http://milambda.blo...

Tibor Karaszi

3/28/2007 7:45:00 PM

0

You have to list the columns. But what is the problem? It is only a matter of dragging the columns
folder in SSMS/QA.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Rich" <Rich@discussions.microsoft.com> wrote in message
news:A2394A95-6BB0-4ACB-8EA7-7E24ED18FA7A@microsoft.com...
>I need to insert data into a table in DB2 from a similar table in DB1. I
> want to say
>
> Insert Into tblx Select * from server1.DB1.dbo.tblx
>
> tblx contains 40 columns which I would like to not have to list. One of the
> columns is an identity column. Is there a way I can run this insert
> statement without having to list all the columns? Note: tblx on DB2 is
> empty, so is there a way I can tell it to let the Identity values from
> DB1.tblx in?
>
> Thanks,
> Rich


Alejandro Mesa

3/28/2007 7:58:00 PM

0

Rich,

> Note: tblx on DB2 is
> empty, so is there a way I can tell it to let the Identity values from
> DB1.tblx in?

Yes you can. See "SET IDENTITY_INSERT" in BOL.


AMB


"Rich" wrote:

> I need to insert data into a table in DB2 from a similar table in DB1. I
> want to say
>
> Insert Into tblx Select * from server1.DB1.dbo.tblx
>
> tblx contains 40 columns which I would like to not have to list. One of the
> columns is an identity column. Is there a way I can run this insert
> statement without having to list all the columns? Note: tblx on DB2 is
> empty, so is there a way I can tell it to let the Identity values from
> DB1.tblx in?
>
> Thanks,
> Rich

rich

3/28/2007 9:34:00 PM

0

Thank you all for your replies and suggestions and for supporting my
laziness. And -- I'm not smart enough to think about just dragging the
columns from the object browser - or is it that I'm too lazy? :).

Thanks all.

"Rich" wrote:

> I need to insert data into a table in DB2 from a similar table in DB1. I
> want to say
>
> Insert Into tblx Select * from server1.DB1.dbo.tblx
>
> tblx contains 40 columns which I would like to not have to list. One of the
> columns is an identity column. Is there a way I can run this insert
> statement without having to list all the columns? Note: tblx on DB2 is
> empty, so is there a way I can tell it to let the Identity values from
> DB1.tblx in?
>
> Thanks,
> Rich

ML

3/28/2007 9:40:00 PM

0

Lazy to the bone. :)
Well, at least you're one step towards not being lazy anymore.


ML

---
http://milambda.blo...

Saga

3/29/2007 7:39:00 PM

0

Woah! I didn't know you could do that, thanks!

One question though, why is it that sometimes when I drag the columns "folder" into
the query pane I get a message box saying "No script created"? Seems to work ok
some of the time, not sure what I am doing wrong when it does not work.

Saga
--


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:erMKZFXcHHA.648@TK2MSFTNGP04.phx.gbl...
>> Insert Into tblx Select * from server1.DB1.dbo.tblx
>>
>> tblx contains 40 columns which I would like to not have to list.
>
> Wow, how lazy we have become!
>
> Have you considered dragging the "Columns" node from Object Explorer (F8 in Management Studio or
> Query Analyzer) and then deleting the identity column???
>