SQL help

[Cogman]

So I came in and wrote a program to interface with a MS SQL server for my company. The problem is, the guy that setup every table didn't tell the table to initialize unknown values to 0 (they are all NULL). So to do an insert into the table, you have to fill in EVERY VALUE...

Here's what I would like to do. After inserting data into the table, I would like to set every numeric value that is NULL to 0, and every string like value to a blank value. Without remaking the table (As all his programs will create a table that doesn't initialize values) is there any way to do this? I would rather not have to add 500 0's to an insert statement because the table isn't setup correctly.

So something like Select * from tablename where NULL would be nice.

 

[KIAman]

Is this going to be a 1 time thing and nulls will never occur again?
Do you want this to be automated so whenever a null is entered, the initial values are entered instead?
Are you not going to cleanup the existing data but only want your new entries initialized?

 

[Cogman]

Originally posted by: KIAman
Is this going to be a 1 time thing and nulls will never occur again?
Do you want this to be automated so whenever a null is entered, the initial values are entered instead?
Are you not going to cleanup the existing data but only want your new entries initialized?

1. After the import program I'm writing executes, Nulls should never again be entered into the database. The import function may be applied multiple times.
2. That would be nice, but I don't know if I could change a table to make it do such. (especially since some of the other programs my boss wrote will just drop the tables and create a new bad table.).
3. I'm Inserting data in, because the tables have many columns, I would rather not have to tag on to each sql command the inserting instructions to initialize values that aren't imported. Existing data shouldn't have any nulls in it.

 

[MGMorden]

It's a sloppy solution, but you could write a trigger that executes after an insert to change all null values to 0's. Truthfully though you'd be better off just doing that once to clean up your old stuff, and then modifying the table itself for the future so that the null values default to 0 instead.

 

[MGMorden]

Didn't see your last post before I hit submit - is there any chance you can just modify his code so that it creates the tables properly?

 

[Cogman]

Originally posted by: MGMorden
Didn't see your last post before I hit submit - is there any chance you can just modify his code so that it creates the tables properly?

I wish.. His code is like the spaghetti monster from hell... Delphi + SQL + no formal programming training + 40 years of enforcement = nightmare code. If I had my way, I would dump all the code he has written and start from scratch.. It is that bad.

 

[KIAman]

If the table schema won't change then this is also a messy solution but if you are allowed to write a stored procedure for the insert, you can simply create the parameters of the data you want entered and for everything else, just insert the initialized values statically.

This way, your front end can call the SP and only enter the parameters you need. Something like below.

Create Procedure dbo.InsertSomething
@YourParam, @YourOtherParam

AS
BEGIN

Insert into TABLENAME (YourParam, YourOtherParam, NotUsedIntField1, NotUsedStringField1)
Values (@YourParam, @YourOtherParam, 1, 'NOTHING')

END

Then on the front end, you only input the wanted parameters and not worry about the rest. Not the cleanest solution but will save significant typing time on the front end.

 

[Cogman]

That's what I fear. I hate messy easily preventable solutions. Oh well, that's what I get for being a new hire-y.

 

[brandonbull]

Make sure your program validates data before sending it to the database.

 

[Cogman]

Originally posted by: brandonbull
Make sure your program validates data before sending it to the database.

What, No Fusetalk problems

Already covered.

For those interest, The fastest route ended going something like this.

Get column names for the table.
Create a commnad based on each column name that basically says

Update table set column_name='' or 0 (program determines which to used based on column data type) Where column_name IS NULL;

Letting the SQL server do the rest of the work. It adds an extra 16 seconds to the execution time (Without is about 10 seconds), I could no doubt get faster times if I excluded rows I know to already be set, but that would make the Frankenstein esque code look even worse then it does now.

Considering that the original code I was given took about 1 minute to execute, I say that it is still fairly good. (halving the time vs. 10x speed improvement, Darn those tables for not being setup properly!).

 

[imported_Dhaval00]

After inserting data into the table, I would like to set every numeric value that is NULL to 0, and every string like value to a blank value.

Why don't you just create a default constraint? Unless I misunderstood your question. Try and stay away from triggers .

 

[Cogman]

Originally posted by: Dhaval00

After inserting data into the table, I would like to set every numeric value that is NULL to 0, and every string like value to a blank value.

Why don't you just create a default constraint? Unless I misunderstood your question. Try and stay away from triggers .

The tables are being dropped and recreated more then once by my bosses programs. While I may rewrite his code in the future, that is not feasible now.

 

[Snapster]

Something like this would do it in a loop, I'm sure you can figure out the rest

DECLARE @TableName nvarchar(255)
SET @TableName = N'My_TABLE_NAME'

DECLARE @Columns TABLE
(
ColNum int IDENTITY(1,1) PRIMARY KEY,
ColName nvarchar(255)
)
-- Insert into temp table, filter out the columns you don't want here by data type/name
INSERT INTO @Columns (ColName)

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
where TABLE_NAME = @TableName
and DATA_TYPE = 'int'


DECLARE @index int, @maxIndex int, @currentCol nvarchar(255)
SET @index = 1
SET @maxIndex = (select Max(ColNum) from @Columns)

WHILE (@index <= @maxIndex) BEGIN

SET @currentCol = (SELECT ColName FROM @Columns WHERE ColNum=@index)
-- perform sql on current column
exec ('SELECT ' + @currentCol + ' FROM ' + @TableName + ' WHERE ' + @currentCol + ' IS NULL')

SET @index = @index + 1
END

 

[nakedfrog]

Wait, so the columns have no default values, but they also don't allow NULL values? How strange.

 

[Cogman]

Originally posted by: nakedfrog
Wait, so the columns have no default values, but they also don't allow NULL values? How strange.

No, the columns have no default value (So if unspecified they initialize as NULL). However, the programs written aren't able to handle NULL values.

 

[nakedfrog]

Originally posted by: Cogman

Originally posted by: nakedfrog
Wait, so the columns have no default values, but they also don't allow NULL values? How strange.

No, the columns have no default value (So if unspecified they initialize as NULL). However, the programs written aren't able to handle NULL values.

I'd be more inclined to modify the programs, personally. Or maybe set up views and pull the data from those rather than directly from the tables.

 

[Cogman]

Originally posted by: nakedfrog

Originally posted by: Cogman

Originally posted by: nakedfrog
Wait, so the columns have no default values, but they also don't allow NULL values? How strange.

No, the columns have no default value (So if unspecified they initialize as NULL). However, the programs written aren't able to handle NULL values.

I'd be more inclined to modify the programs, personally. Or maybe set up views and pull the data from those rather than directly from the tables.

I somewhat would as well, but I'm still new here, so I don't have access to the full source yet.