SELECT INTO HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT INTO HELP

Author

Topic

cinewest
Starting Member

8 Posts

Posted - 2007-10-03 : 14:14:45

I wish to copy all fields, except uniqueid and two additonal fields from Table1 to Table2.

I cannot recall proper syntax but know that there is a way to SELECT all fields, excluding named fields from one table to the other.

The statement is similar to following:

SELECT * INTO Table2
EXCEPT (ColA, ColB)
FROM Table1
WHERE field1=x

Thank you in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-03 : 14:17:03

nope. no such thing.
you have to do
select <columns you wish to transfer>
into newTable
from oldTable

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

cinewest
Starting Member

8 Posts

Posted - 2007-10-03 : 14:49:10

No problem -- found an easy way to get the names of all fields without typing them: from Query Analyzer press F8, browse to the table, drag and drop the Column folder and copy all of the names - you can then remove what you don't want to import and it will save you typing time!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-03 : 15:03:33

oh so that was the problem. why didn't you say so in the first place?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-03 : 15:24:46

Even better, right-click the table and select "script object as", saves a lot of typing!

Jim

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-03 : 15:31:24

Or just keep this script handy..

declare @tablename varchar(250)
select @tablename = 'MyTable'

select c.name+','
from syscolumns c join sysobjects o on c.id = o.id
where o.name = @tablename
order by colid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources