Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Drop Multiple Columns is they exist Sql 2000 1

Status
Not open for further replies.
jadams0173

jadams0173

Technical User
Feb 18, 2005
1,210
Is there a way to check and see if multiple columns(F19,F20,F21....) exist and if so drop them?

I'm importing data from an .xls file and sometimes there a lot extra columns that get imported but not always. It depends one what the users have done to the file before I import it. I've got a single statement to drop the columns but it errors if they are not there. Being new to SQL 2000 I'm unsure of the best method to handle this. Should I error trap or query the system tables to see if the columns exist?

What I have:
Code: 
ALTER TABLE TEMPIMPORT DROP COLUMN F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22,F23,F24,F25,
F26,F27,F28,F29,F30,F31,F32,F33,F34,F35,F36,F37,F38,F39,F40
 
Sort by date Sort by votes
You could do something along the following lines (test this out on a test database):

Code: 
CREATE TABLE TestTable
(F1 INT,
F2 INT,
F3 INT,
F4 INT)

--Should have columns F1, F2, F3, F4
SELECT * FROM TestTable

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'TestTable' AND COLUMN_NAME = 'F3')
BEGIN
	ALTER TABLE TestTable DROP COLUMN F3
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'TestTable' AND COLUMN_NAME = 'F4')
BEGIN
	ALTER TABLE TestTable DROP COLUMN F4
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'TestTable'  AND COLUMN_NAME = 'F5')
BEGIN
	ALTER TABLE TestTable DROP COLUMN F5
END

--Should only have columns F1 and F2
SELECT * FROM TestTable

DROP TABLE TestTable

It makes for a larger script, but most of it would be copying and pasting.
 
Upvote 0 Downvote
How do you know which columns to drop?
Why not import in temp table and then just insert from it into real table the columns you need?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
RiverGuy,

Thanks for the reply. I was hoping there was an eaiser way than one at a time. But I guess if that's the route I take then I guess that's the way I'll have to do it.

bborissov,
Thanks for the reply. I've never thought about using a temp table and the appending only the needed columns to it. I'll have to see if I can try a few things to make this approach to work.

This is what I am currently doing. I'm open for suggestions if something can be done cleaner/easier/better.

Code: 
[COLOR=blue]Use[/color] ToolWorld
[COLOR=blue]GO[/color]

[COLOR=green]--If the table exists then drop it
[/color][COLOR=blue]if[/color] exists ([COLOR=blue]select[/color] * [COLOR=blue]from[/color] dbo.sysobjects [COLOR=blue]where[/color] id = [COLOR=#FF00FF]object_id[/color](N[COLOR=red]'[dbo].[TemplateImport]'[/color]) 
and OBJECTPROPERTY(id, N[COLOR=red]'IsUserTable'[/color]) = 1)

[COLOR=blue]drop[/color] [COLOR=blue]table[/color] [dbo].[TEMPLATEIMPORT]
[COLOR=blue]GO[/color]

[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

[COLOR=green]--Import the table back into the DB
[/color][COLOR=blue]SELECT[/color] * 
[COLOR=blue]into[/color] TEMPLATEIMPORT
[COLOR=blue]FROM[/color] OPENROWSET([COLOR=red]'Microsoft.Jet.OLEDB.4.0'[/color],
[COLOR=red]'Excel 8.0;Database=C:\SQL_StencilDB\TOOLS.xls;HDR=YES'[/color],
[COLOR=red]'SELECT * FROM [TS$]'[/color])

[COLOR=green]/*UPDATE TEMPLATEIMPORT
[/color][COLOR=green]	SET [TEMPLATE #] = CASE WHEN [TEMPLATE #] = '.' THEN ''
[/color][COLOR=green]	WHEN [TEMPLATE #]  IS NULL THEN ''
[/color][COLOR=green]	WHEN [TEMPLATE #] = '.' THEN ''
[/color][COLOR=green]	
[/color][COLOR=green]	--SET STAT = CASE WHEN STAT IS NULL THEN ''
[/color][COLOR=green]	END*/[/color]


[COLOR=blue]Update[/color] TEMPLATEIMPORT
	[COLOR=blue]SET[/color] [TEMPLATE #] = [COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] [TEMPLATE #][COLOR=blue]IS[/color] NULL
	OR [TEMPLATE #] = [COLOR=red]'.'[/color]

[COLOR=blue]UPDATE[/color] TEMPLATEIMPORT
	 [COLOR=blue]SET[/color] STAT=[COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] STAT [COLOR=blue]IS[/color] NULL
		
[COLOR=blue]UPDATE[/color] TEMPLATEIMPORT
	 [COLOR=blue]SET[/color] DES=[COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] DES [COLOR=blue]IS[/color] NULL

[COLOR=blue]UPDATE[/color] TEMPLATEIMPORT
	 [COLOR=blue]SET[/color] CUSTOMER = [COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] CUSTOMER [COLOR=blue]IS[/color] NULL
	OR CUSTOMER LIKE  [COLOR=red]'%.%'[/color]

[COLOR=blue]UPDATE[/color] TEMPLATEIMPORT
	 [COLOR=blue]SET[/color] [MPI ASM#] = [COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] [MPI ASM#] [COLOR=blue]IS[/color] NULL
	OR [MPI ASM#]=[COLOR=red]'.'[/color]
	OR [MPI ASM#] = [COLOR=red]'???'[/color]

[COLOR=blue]UPDATE[/color] TEMPLATEIMPORT
	 [COLOR=blue]SET[/color] BOT = 0
	[COLOR=blue]WHERE[/color] BOT [COLOR=blue]IS[/color] NULL

[COLOR=blue]UPDATE[/color] TEMPLATEIMPORT
	 [COLOR=blue]SET[/color] [TOP] = 0
	[COLOR=blue]WHERE[/color] [TOP] [COLOR=blue]IS[/color] NULL

[COLOR=blue]UPDATE[/color] TEMPLATEIMPORT
	 [COLOR=blue]SET[/color] NOTES = [COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] NOTES [COLOR=blue]IS[/color] NULL
	OR NOTES LIKE [COLOR=red]'%.%'[/color]
	OR NOTES = [COLOR=red]'???'[/color]

[COLOR=blue]UPDATE[/color] TEMPLATEIMPORT
	 [COLOR=blue]SET[/color] [CUSTOMER P/N] = [COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] [CUSTOMER P/N] [COLOR=blue]IS[/color] NULL
	OR [CUSTOMER P/N] LIKE [COLOR=red]'%.%'[/color]
	OR [CUSTOMER P/N] = [COLOR=red]'???'[/color]

[COLOR=blue]UPDATE[/color] TEMPLATEIMPORT
	 [COLOR=blue]SET[/color] PE = [COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] PE [COLOR=blue]IS[/color] NULL
	OR PE LIKE [COLOR=red]'%.%'[/color]
	OR PE = [COLOR=red]'???'[/color]
[COLOR=green]--ALTER TABLE TEMPLATEIMPORT DROP COLUMN F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22,F23,F24,F25,
[/color][COLOR=green]--F26,F27,F28,F29,F30,F31,F32,F33,F34,F35,F36,F37,F38,F39,F40
[/color]
[COLOR=blue]Alter[/color] [COLOR=blue]Table[/color] TEMPLATEIMPORT [COLOR=blue]Alter[/color] [COLOR=blue]Column[/color] LOC nvarchar(25)
[COLOR=blue]Alter[/color] [COLOR=blue]Table[/color] TEMPLATEIMPORT [COLOR=blue]Alter[/color] [COLOR=blue]Column[/color] STAT nvarchar(40)
[COLOR=blue]Alter[/color] [COLOR=blue]Table[/color] TEMPLATEIMPORT [COLOR=blue]Alter[/color] [COLOR=blue]Column[/color] DES nvarchar(25)
[COLOR=blue]Alter[/color] [COLOR=blue]Table[/color] TEMPLATEIMPORT [COLOR=blue]Alter[/color] [COLOR=blue]Column[/color] CUSTOMER nvarchar(30)
[COLOR=blue]Alter[/color] [COLOR=blue]Table[/color] TEMPLATEIMPORT [COLOR=blue]Alter[/color] [COLOR=blue]Column[/color] [MPI ASM#] nvarchar(35)
[COLOR=blue]Alter[/color] [COLOR=blue]Table[/color] TEMPLATEIMPORT [COLOR=blue]Alter[/color] [COLOR=blue]Column[/color] LOC nvarchar(25)
[COLOR=blue]Alter[/color] [COLOR=blue]Table[/color] TEMPLATEIMPORT [COLOR=blue]Alter[/color] [COLOR=blue]Column[/color] [TEMPLATE #] nvarchar(88)
[COLOR=blue]Alter[/color] [COLOR=blue]Table[/color] TEMPLATEIMPORT [COLOR=blue]Alter[/color] [COLOR=blue]Column[/color] NOTES nvarchar(80)
[COLOR=blue]Alter[/color] [COLOR=blue]Table[/color] TEMPLATEIMPORT [COLOR=blue]Alter[/color] [COLOR=blue]Column[/color] [CUSTOMER P/N] nvarchar(50)
[COLOR=blue]Alter[/color] [COLOR=blue]Table[/color] TEMPLATEIMPORT [COLOR=blue]Alter[/color] [COLOR=blue]Column[/color] PE nvarchar(35)
[COLOR=blue]ALTER[/color] [COLOR=blue]TABLE[/color] TEMPLATEIMPORT [COLOR=blue]ALTER[/color] [COLOR=blue]COLUMN[/color] [TOP] NVARCHAR(30)
[COLOR=blue]ALTER[/color] [COLOR=blue]TABLE[/color] TEMPLATEIMPORT [COLOR=blue]ALTER[/color] [COLOR=blue]COLUMN[/color] BOT NVARCHAR(30)

[COLOR=green]--delete the scrapped templates from the sheet/table
[/color][COLOR=blue]DELETE[/color] [COLOR=blue]FROM[/color] TEMPLATEIMPORT
[COLOR=blue]WHERE[/color]     (STAT LIKE N[COLOR=red]'Scrap%'[/color])

[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]OFF[/color]



[COLOR=green]--AT THIS POINT THE DATA SHOULD BE FAIRLY CLEAN. WE STILL NEED TO REVIEW IT AND MAKE SURE BEFORE
[/color][COLOR=green]--APPENDING IT TO THE PRODUCTION TABLE.[/color]
 
Upvote 0 Downvote
I'm sure you could create a looping construct and some dynamic sql to make a concise script to do this. But I'm not sure you'd want to. I prefer to see a script of exactly what is going to happen to my database. Much easier to debug and look for what could go wrong.

But anyways, I do like the temp table idea. Any persistent table in my databases--including staging tables--I want to keep the columns consistent as to my business rules.

The next question would be--instead of doing a SELECT * on that Excel sheet, can you specify the columns so that you never return any of those extra F.. columns?
 
Upvote 0 Downvote
Code: 
DECLARE @sql varchar(8000)
SET @sql = ','
SELECT @sql = @sql+COLUMN_NAME+','
       FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name = 'TEMPLATEIMPORT'
SET @sql = REPLACE(@sql, 'F12,' , '')
SET @sql = REPLACE(@sql, 'F13,' , '')
...
SET @sql = REPLACE(@sql,'F831,','') -- :-)
IF LEFT(@sql,1) = ','
    SET @sql = SUBSTRING(@sql,2,8000)

IF RIGHT(@sql,1) = ','
    SET @sql = LEFT(@sql,LEN(@sql)-1)


SET @sql = 'ALTER TABLE TEMPLATEIMPORT 
                  DROP COLUMN '+@sql
EXEC(@sql)

NOT TESTED AT ALL


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
ERROR! ERROR!

You should replace these columns you NEED not these coulumns you don't need:
Code: 
DECLARE @sql varchar(8000)
SET @sql = ','
SELECT @sql = @sql+COLUMN_NAME+','
       FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name = 'TEMPLATEIMPORT'
SET @sql = REPLACE(@sql, 'LOC,' , '')
SET @sql = REPLACE(@sql, 'STAT,' , '')
...
SET @sql = REPLACE(@sql,'BOT,','') -- :-)

IF LEFT(@sql,1) = ','
    SET @sql = SUBSTRING(@sql,2,8000)

IF RIGHT(@sql,1) = ','
    SET @sql = LEFT(@sql,LEN(@sql)-1)


SET @sql = 'ALTER TABLE TEMPLATEIMPORT
                  DROP COLUMN '+@sql
EXEC(@sql)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
Thanks again bborissov. I have just now seen the posts. It may take a few minutes to understand what's going on. In the mean time I took your advice about using the temp table. It actually sped up the query from 6 seconds to 2 seconds. Not that 6 sec is a long time but pretty big % gain.

Here is what it looks like now.
Code: 
[COLOR=blue]Use[/color] ToolWorld
[COLOR=blue]GO[/color]

[COLOR=green]--If the table exists then drop it
[/color][COLOR=blue]if[/color] exists ([COLOR=blue]select[/color] * [COLOR=blue]from[/color] dbo.sysobjects [COLOR=blue]where[/color] id = [COLOR=#FF00FF]object_id[/color](N[COLOR=red]'[dbo].[TemplateImport]'[/color]) 
and OBJECTPROPERTY(id, N[COLOR=red]'IsUserTable'[/color]) = 1)

[COLOR=blue]drop[/color] [COLOR=blue]table[/color] [dbo].[TEMPLATEIMPORT]
[COLOR=blue]GO[/color]

[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

[COLOR=green]--Import the table back into the DB
[/color][COLOR=blue]SELECT[/color] * 
[COLOR=blue]into[/color] #TEMP
[COLOR=blue]FROM[/color] OPENROWSET([COLOR=red]'Microsoft.Jet.OLEDB.4.0'[/color],
[COLOR=red]'Excel 8.0;Database=C:\SQL_StencilDB\TOOLS.xls;HDR=YES'[/color],
[COLOR=red]'SELECT * FROM [TS$]'[/color])

[COLOR=blue]Update[/color] #Temp
	[COLOR=blue]SET[/color] [TEMPLATE #] = [COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] [TEMPLATE #][COLOR=blue]IS[/color] NULL
	OR [TEMPLATE #] = [COLOR=red]'.'[/color]

[COLOR=blue]UPDATE[/color] #Temp
	 [COLOR=blue]SET[/color] STAT=[COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] STAT [COLOR=blue]IS[/color] NULL
		
[COLOR=blue]UPDATE[/color] #Temp
	 [COLOR=blue]SET[/color] DES=[COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] DES [COLOR=blue]IS[/color] NULL

[COLOR=blue]UPDATE[/color] #Temp
	 [COLOR=blue]SET[/color] CUSTOMER = [COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] CUSTOMER [COLOR=blue]IS[/color] NULL
	OR CUSTOMER LIKE  [COLOR=red]'%.%'[/color]

[COLOR=blue]UPDATE[/color] #Temp
	 [COLOR=blue]SET[/color] [MPI ASM#] = [COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] [MPI ASM#] [COLOR=blue]IS[/color] NULL
	OR [MPI ASM#]=[COLOR=red]'.'[/color]
	OR [MPI ASM#] = [COLOR=red]'???'[/color]

[COLOR=blue]UPDATE[/color] #Temp
	 [COLOR=blue]SET[/color] BOT = 0
	[COLOR=blue]WHERE[/color] BOT [COLOR=blue]IS[/color] NULL

[COLOR=blue]UPDATE[/color] #Temp
	 [COLOR=blue]SET[/color] [TOP] = 0
	[COLOR=blue]WHERE[/color] [TOP] [COLOR=blue]IS[/color] NULL

[COLOR=blue]UPDATE[/color] #Temp
	 [COLOR=blue]SET[/color] NOTES = [COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] NOTES [COLOR=blue]IS[/color] NULL
	OR NOTES LIKE [COLOR=red]'%.%'[/color]
	OR NOTES = [COLOR=red]'???'[/color]

[COLOR=blue]UPDATE[/color] #Temp
	 [COLOR=blue]SET[/color] [CUSTOMER P/N] = [COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] [CUSTOMER P/N] [COLOR=blue]IS[/color] NULL
	OR [CUSTOMER P/N] LIKE [COLOR=red]'%.%'[/color]
	OR [CUSTOMER P/N] = [COLOR=red]'???'[/color]

[COLOR=blue]UPDATE[/color] #Temp
	 [COLOR=blue]SET[/color] PE = [COLOR=red]''[/color]
	[COLOR=blue]WHERE[/color] PE [COLOR=blue]IS[/color] NULL
	OR PE LIKE [COLOR=red]'%.%'[/color]
	OR PE = [COLOR=red]'???'[/color]

[COLOR=green]--delete the scrapped templates from the sheet/table
[/color][COLOR=blue]DELETE[/color] [COLOR=blue]FROM[/color] #Temp
[COLOR=blue]WHERE[/color]     (STAT LIKE N[COLOR=red]'Scrap%'[/color])


[COLOR=blue]CREATE[/color] [COLOR=blue]TABLE[/color] TEMPLATEIMPORT (
LOC nvarchar(25),
STAT nvarchar(40),
DES nvarchar(25),
CUSTOMER nvarchar(30),
[MPI ASM#] nvarchar(35),
[TEMPLATE #] nvarchar(88),
NOTES nvarchar(80),
[CUSTOMER P/N] nvarchar(50),
PE nvarchar(35),
[TOP] NVARCHAR(30),
BOT NVARCHAR(30)
)

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] TEMPLATEIMPORT
(Loc,
STAT,
DES,
CUSTOMER,
[MPI ASM#],
[TEMPLATE #],
NOTES,
[CUSTOMER P/N],
PE,
[TOP],
BOT)
[COLOR=blue]SELECT[/color] Loc,
STAT,
DES,
CUSTOMER,
[MPI ASM#],
[TEMPLATE #],
NOTES,
[CUSTOMER P/N],
PE,
[TOP],
BOT
[COLOR=blue]FROM[/color] #TEMP

[COLOR=blue]DROP[/color] [COLOR=blue]TABLE[/color] #TEMP

[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]OFF[/color]
[COLOR=green]--AT THIS POINT THE DATA SHOULD BE FAIRLY CLEAN. WE STILL NEED TO REVIEW IT AND MAKE SURE BEFORE
[/color][COLOR=green]--APPENDING IT TO THE PRODUCTION TABLE.[/color]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Steve Yu
  • Locked
  • Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN 1
Replies
3
Views
495
Chris Miller
Chris Miller
MathiasB
  • Question
Parameterised sql server query
Replies
8
Views
320
Chriss Miller
C
SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
821
SashaBuilder3
SashaBuilder3
gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
919
pjw001
pjw001
peac3
  • Locked
  • Question
Consolidate multiple rows into single row 1
Replies
4
Views
1K
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top