Drop Multiple Columns is they exist Sql 2000 1

[jadams0173]

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:

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

 

[RiverGuy]

You could do something along the following lines (test this out on a test database):

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.

 

[bborissov]

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.

 

[jadams0173]

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.

[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]

 

[RiverGuy]

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?

 

[bborissov]

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.

 

[bborissov]

ERROR! ERROR!

You should replace these columns you NEED not these coulumns you don't need:

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.

 

[jadams0173]

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.

[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]