Need to insert null values into certain columns of my table

Question

I am trying to insert values into table 1 from table 2, there are certain columns from table 1 where I do not want the values from values from table 2. Instead I would like to insert NULL to the values in the table 1.

Below is the example.

insert into dbo.Student
[Number], [Timestamp], [Education], [Roll No],
[Name], [Age],[marks])

Select
[Number], [Timestamp],    
convert(nvarchar(max),dbo.Table1([a.Description Description])) as [Education],
Convert(nvarchar(50),dbo.Table2([a.Description Description])) as [Roll No], 
convert(nvarchar(50),dbo.Table3([a.Description Description])) as [Name],    
dbo.Table4([a.Description Description]) as [Age],    
convert(nvarchar(50),dbo.Table5([a.Description Description])) as [marks]    
from dbo.Data a

I would like to update the columns [Number], [Timestamp], [Education] to NULL and for the rest of the columns I would like to fetch the values from where it is fetching right now.

score 1 · Accepted Answer · answered Apr 29 '19 at 17:27

You can do that multiple ways:

Provide NULL

insert into dbo.Student
    [Number], [Timestamp], [Education], [Roll No],
    [Name], [Age],[marks])
Select
    NULL as [Number], NULL as [Timestamp], NULL as [Education],    
    Convert(nvarchar(50),dbo.Table2([a.Description Description])) as [Roll No],    
    convert(nvarchar(50),dbo.Table3([a.Description Description])) as [Name],        
    dbo.Table4([a.Description Description]) as [Age],    
    convert(nvarchar(50),dbo.Table5([a.Description Description])) as [marks]
from dbo.Data a

Avoid inserting data into those fields

insert into dbo.Student
    [Roll No],
    [Name], [Age],[marks])
Select
    Convert(nvarchar(50),dbo.Table2([a.Description Description])) as [Roll No],    
    convert(nvarchar(50),dbo.Table3([a.Description Description])) as [Name],        
    dbo.Table4([a.Description Description]) as [Age],    
    convert(nvarchar(50),dbo.Table5([a.Description Description])) as [marks]
from dbo.Data a

Change data after insert

After you do insert into dbo.Student ..., do:

update dbo.student set 
   [Number] = NULL,
   [Timestamp] = NULL,
   [Education] = NULL
where ...

You'll have to be careful with the where clause and ensure that you are changing only the records you recently entered.

Hi Thank you for replying . If i add null values as you have showed and run the query, the query does execute and shows me like 1000 rows are being affected, but when i try to look into the table there are no entries into them, they are all Blank — Solomon, Apr 29 '19 at 17:44
@Solomon did you use the query under `Provide NULL` or under `Avoid inserting data...` or `Change data after insert`? — zedfoxus, Apr 29 '19 at 19:44
Also type `commit tran`. Perhaps there's a transaction already running that needs to be committed. — zedfoxus, Apr 29 '19 at 19:44

score 0 · Answer 2 · answered Apr 29 '19 at 17:28

You just need to put NULL in place of the column.

insert into dbo.Student
    [Number], [Timestamp], [Education], [Roll No],
    [Name], [Age],[marks])
Select
    NULL AS [Number], NULL AS [Timestamp],      
    NULL as [Education],    
    Convert(nvarchar(50),dbo.Table2([a.Description Description])) as [Roll No],    
    convert(nvarchar(50),dbo.Table3([a.Description Description])) as [Name],        
    dbo.Table4([a.Description Description]) as [Age],    
    convert(nvarchar(50),dbo.Table5([a.Description Description])) as [marks]
from dbo.Data a

Hi Thank you for replying . If i add null values as you have showed and run the query, the query does execute and shows me like 1000 rows are being affected, but when i try to look into the table there are no entries into them, they are all Blank — Solomon, Apr 29 '19 at 17:40

score 0 · Answer 3 · answered Apr 29 '19 at 17:31

You could just not include the columns you don't want to have values. So from your example it would be:

insert into dbo.Student

[Roll No], [Name], [Age],[marks])

Select

Convert(nvarchar(50),dbo.Table2([a.Description Description])) as [Roll No],

convert(nvarchar(50),dbo.Table3([a.Description Description])) as [Name],

dbo.Table4([a.Description Description]) as [Age],

convert(nvarchar(50),dbo.Table5([a.Description Description])) as [marks]

from dbo.Data a

Here's a couple related questions:

how to insert data in only few column leaving other columns empty or as they are in a mysql table record?

Adding only one value to the table in sql

Hi Thank you for replying . If i exclude the values as you have shown and run the query, the query does execute and shows me like 1000 rows are being affected, but when i try to look into the table there are no entries into them, they are all Blank, even though the query did run. — Solomon, Apr 29 '19 at 17:42

score 0 · Answer 4 · answered Apr 29 '19 at 17:32

0

You can simply avoid those three columns from Insert and Selection part as shown below-

INSERT INTO dbo.Student
--[Number], [Timestamp], [Education], 
([Roll No],[Name], [Age],[marks])
SELECT
--[Number], [Timestamp],      
--convert(nvarchar(max),dbo.Table1([a.Description Description])) as [Education],    
CONVERT(NVARCHAR(50),dbo.Table2([a.Description Description])) AS [Roll No],    
CONVERT(NVARCHAR(50),dbo.Table3([a.Description Description])) AS [Name],        
dbo.Table4([a.Description Description]) AS [Age],    
CONVERT(NVARCHAR(50),dbo.Table5([a.Description Description])) AS [marks]
FROM dbo.Data a

answered Apr 29 '19 at 17:32

mkRabbani

16,295
2
15
24

Hi Thank you for replying . If i exclude the values as you have shown and run the query, the query does execute and shows me like 1000 rows are being affected, but when i try to look into the table there are no entries into them, they are all Blank, even though the query did run – Solomon Apr 29 '19 at 17:42
What do you mean by "blank"? What are you using to display the table rows? Oh, and it's probably a good idea to post your CREATE TABLE statement, so we can see if there are any check constraints or defaults in effect. – user1443098 Apr 29 '19 at 18:11
After executing the above query, i just go ahead use the select * from dbo.Student query to check the values which have been entered, to my surprise i see nothing. – Solomon Apr 29 '19 at 18:31
Thats really surprising that the query executing successfully but no records inserting. Can you please run both insert and the select query in a single execution? If this returns rows, that means something restricting to commit the last changes in your table. – mkRabbani Apr 30 '19 at 04:44

Need to insert null values into certain columns of my table

4 Answers4