Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Insert, Identity, All nulls
I have a table that uses an Identity column as the PK. Is it possible using SQL to insert a record with NULLS for all fields except for the PK? Now, I know I could specify one of the columns and use a value of NULL. But I have multiple table liek this and I want a generic way to insert a record will all NULLS, without specifying the same of any of the columns.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I can't think of a way to create an empty record assuming you have made all of the other columns nullable. Can you please expound on why you want to do this?
Well, I'm using a code generator product. It has Add and Edit pages. I'm trying to make the Edit page work as both an Add and Edit page. To may it work like an "Add", I fake it out. I Add a blank record and it Edits that record. I want to make it generic. All my table use an identity column as the PK. So, I want to add a record with all NULL values (all are nullable) except for the PK.
Is there a way to specify a column based on ordinal position? That way I don't need to know the name of any column.
a, I could do this:
select column_name from information_schema.columns
Then INSERT using the returned column name with a NULL value.
select column_name from information_schema.columns
Then INSERT using the returned column name with a NULL value.
This does not work, syntax wise, but you get the idea. How could I make this work?
insert into [tds_grant_funding_request
values(null)
insert into [tds_grant_funding_request
values(null)
If you know the number of columns then its as simple as....
INSERT INTO dbo.myTable VALUES(null,null,null,null
That assumes you have 5 columns. your Identity column plus 4 that allow null values.
INSERT INTO dbo.myTable VALUES(null,null,null,null
That assumes you have 5 columns. your Identity column plus 4 that allow null values.
Ah yes, that would work. but I dont want to depend on the number of columns. I can up with a way, using a stored proc; ordinal_position 1 is always the identity PK.
CREATE PROCEDURE [dbo].[InsertNullRecord] @TABLE_NAME varchar(128) AS
DECLARE @COLUMN_NAME varchar(50);
DECLARE @SQL nvarchar(100);
BEGIN
DECLARE tempTable CURSOR FOR
SELECT column_name from information_schema.columns
OPEN tempTable
FETCH NEXT FROM tempTable INTO @COLUMN_NAME
CLOSE tempTable
DEALLOCATE tempTable
SET @SQL = 'INSERT INTO ' + @TABLE_NAME + ' (' + @COLUMN_NAME + ') VALUES(NULL)'
EXEC sp_executesql @SQL
END
CREATE PROCEDURE [dbo].[InsertNullRecord] @TABLE_NAME varchar(128) AS
DECLARE @COLUMN_NAME varchar(50);
DECLARE @SQL nvarchar(100);
BEGIN
DECLARE tempTable CURSOR FOR
SELECT column_name from information_schema.columns
OPEN tempTable
FETCH NEXT FROM tempTable INTO @COLUMN_NAME
CLOSE tempTable
DEALLOCATE tempTable
SET @SQL = 'INSERT INTO ' + @TABLE_NAME + ' (' + @COLUMN_NAME + ') VALUES(NULL)'
EXEC sp_executesql @SQL
END
Not at a PC to play with right now but if you Copy this into SQL and run it, it should give you power you need to make the stored procedure as flexible as you need it.
DECLARE @Columns integer
DECLARE @sql varchar(max)
DECLARE @TABLE_NAME varchar(80)
SET @TABLE_NAME = 'myTableName'
SELECT @columns=COUNT(*) from information_schema.columns
SELECT @columns
SET @sql = 'INSERT INTO ' + @table_name + ' VALUES('
WHILE (@columns >1)
BEGIN
SET @sql = @sql + 'null'
if(@columns = 2)
BEGIN
SET @sql = @sql + ')'
END
ELSE
BEGIN
SET @sql = @sql + ', '
END
SET @columns = @columns - 1
END
SELECT @sql
Or the more efficient method of....
DECLARE @Columns integer
DECLARE @iloop integer
DECLARE @sql varchar(max)
DECLARE @TABLE_NAME varchar(80)
SET @TABLE_NAME = 'myTableName'
SELECT @columns=COUNT(*) from information_schema.columns
SELECT @columns
SET @sql = 'INSERT INTO ' + @table_name + ' VALUES(' + REPLICATE('null, ',@columns-1) + ')'
SELECT @sql
If all of the fields allow NULLs, then you only need to insert one value into the table in order to get a new record.
Yes but you need a column name. My solution was to allow a quick insert with no column names and any number of columns thats all.
There is the less known DEFAULT keyword, eg
You'll need to know the nnumber of columns for this, but it will have another advantage, you'll not only get NULLs in all columns, but default values, where they are defined, The sample column mybit has no default value, then the type default is used - NULL in case of a nullable column.
Bye, Olaf.
declare @mytable as table(id int identity, mytext varchar(20) default 'hello, world' null, mydate date default getdate() null, mybit bit NULL);
insert into @mytable values (default, default, default)
select * from @mytable
You'll need to know the nnumber of columns for this, but it will have another advantage, you'll not only get NULLs in all columns, but default values, where they are defined, The sample column mybit has no default value, then the type default is used - NULL in case of a nullable column.
Bye, Olaf.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial