[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to Insert Multiple SQL table rows using DEFAULT VALUES from a SELECT statement

Posted on 2009-04-23
4
Medium Priority
?
510 Views
Last Modified: 2012-05-06
I wish to INSERT multiple rows into a table using values obtained from a SELECT statement.   Even through the SELECT statement by itself returns three rows, the total number of rows in the post-inserted table increments by one.   And that one doesn't seem to have any of the values from the select...      I'm sure that I have a syntax problem.  Can someone assist?

No error messages are generated.   The table has no constraints or unique indentification columns.   There are only 4 columns in the table.  Each is identified within the INSERT and SELECT statements.
INSERT INTO [bbprocessing].[dbo].[STUDENROLL_ISIS]
      ([External_Course_Key]
      ,[External_Person_key]
      ,[Role]
      ,[Row_status])
DEFAULT VALUES SELECT [External_Course_Key]
      ,[External_Person_key]
      ,[Role]
      ,'Disabled' AS [Row_status]
  FROM [bbprocessing].[dbo].[STUDENROLL_ISIS]
	WHERE [External_Course_Key] = '2009215873' AND Row_status = 'Enabled'

Open in new window

0
Comment
Question by:RichardKline
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 1100 total points
ID: 24219968
Hi,

Why do you use DEFAULT VALUES? Using DEFAULT VALUES will insert a row with the default values of the columns in the row. So removing this should give you 3 rows from the select only.

/peter
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 900 total points
ID: 24219992
INSERT INTO tablename DEFAULT VALUES

By itself will insert a row with default values for each column.  If you are wanting to use query, but default columns that are NULL you will probably want to do like this:

e.g. If [Role] is NULL and you want a default, use --
ISNULL([Role], 'Default Role')


INSERT INTO [bbprocessing].[dbo].[STUDENROLL_ISIS]
      ([External_Course_Key]
      ,[External_Person_key]
      ,[Role]
      ,[Row_status])
SELECT [External_Course_Key]
      ,[External_Person_key]
      , [Role]
      ,'Disabled' AS [Row_status]
  FROM [bbprocessing].[dbo].[STUDENROLL_ISIS]
	WHERE [External_Course_Key] = '2009215873' AND Row_status = 'Enabled'

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24219997
Peter beat me to saying it.  Basically that is what I am saying as well, so I agree.
0
 
LVL 1

Author Closing Comment

by:RichardKline
ID: 31574009
Thank you.   I used DEFAULT VALUES due to my incomplete understanding!  It does make more sense now,
I hate splitting points  --  a couple of minutes quicker versus a bit more expansive answer.   I hope that this division is satisfactory,
Have a good weekend!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question