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.
LVL 1
HLRosenbergerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
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?
HLRosenbergerAuthor Commented:
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.
HLRosenbergerAuthor Commented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

HLRosenbergerAuthor Commented:
a, I could do this:

select column_name from information_schema.columns where table_name = 'tch_attendance_week' and ordinal_position = 2

Then INSERT using the returned column name with a NULL value.
HLRosenbergerAuthor Commented:
This does not work, syntax wise, but you get the idea.  How could I make this work?

insert into [tds_grant_funding_request] (select column_name from information_schema.columns where table_name = 'tch_attendance_week' and ordinal_position = 2)
 values(null)
Neil RussellTechnical Development LeadCommented:
If you know the number of columns then its as simple as....

INSERT INTO dbo.myTable VALUES(null,null,null,null,null)

That assumes you have 5 columns. your Identity column plus 4 that allow null values.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HLRosenbergerAuthor Commented:
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 WHERE table_name = @TABLE_NAME and ordinal_position = 2
      
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
Neil RussellTechnical Development LeadCommented:
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

Open in new window

Neil RussellTechnical Development LeadCommented:
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

Open in new window

typetoitCommented:
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.
Neil RussellTechnical Development LeadCommented:
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.
Olaf DoschkeSoftware DeveloperCommented:
There is the less known DEFAULT keyword, eg

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

Open in new window


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.
Scott PletcherSenior DBACommented:
Make the default NULL for all the non-PK columns.

Then you can do this:

INSERT INTO table_name DEFAULT VALUES
Olaf DoschkeSoftware DeveloperCommented:
Thanks for sharing, Scott. That's even better of course!
HLRosenbergerAuthor Commented:
thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.