Select * EXCEPT 1

[DirtyB]

Is there a way in SQL to select *, with an exception. I have a table with about two hundred fields. I simply need to copy a record(with the exception of the IDKey).

I would like to say

select * (except fieldname) from Table1 into Table2

 

[tlbroadbent]

There is no such SQL command available. You can use T-SQl to generate the insert statement for you. The following script will generate an INSERT statement for inserting data from table2 into table1. I assume that the columns in table1 are in the same ordinal position as the columns in table2. I used an IN clause to exclude columns from the selection so this script can be more flexible than using equality criteria.

declare @sql nvarchar(4000)

Set @sql='INSERT table1 SELECT '
Select @sql=@sql +
CASE
WHEN c.CHARACTER_MAXIMUM_LENGTH IS NULL THEN c.COLUMN_NAME + ','
ELSE '''' + c.COLUMN_NAME + ''','
END
FROM
(SELECT TOP 100 PERCENT
COLUMN_NAME,
CHARACTER_MAXIMUM_LENGTH,
ORDINAL_POSITION
FROM information_schema.columns
WHERE TABLE_NAME='table2'
AND TABLE_SCHEMA='dbo'
AND COLUMN_NAME NOT IN ('RecID')
ORDER By ORDINAL_POSITION) As c

SELECT @sql = LEFT(@sql, len(@SQL)-1) + ' FROM table2'
PRINT @sql

I've not tested this script extensively but it should work in most cases. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[DirtyB]

Thanks for the response. I'll test that out and see if it will work. Another question for you then: Is there a way to say select * from x into y WHERE? Can you put a WHERE clause on a select into ? I'm having some serious problems. Thanks again.

 

[tlbroadbent]

Some legal syntax includes.

Use Select Into to create a new table.

SELECT *
INTO table2
FROM table1
WHERE <criteria>

SELECT col1, col2, col4, col5, col7, col9
INTO table2
FROM table1
WHERE <criteria>

Use Insert Into to append records in an existing table.

INSERT table2
SELECT *
FROM table1
WHERE <criteria>

INSERT table2 (col1, col2, col3, col4, col5)
SELECT col2, col5, col2, col9, col7
FROM table1
WHERE <criteria> Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.