CODEWITHSUNDEEP
sqlsql-server
Solomon
Solomon

Reputation: 13

Need to insert null values into certain columns of my table

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.

Upvotes: 0

Views: 1481

Answers (4)

mkRabbani
mkRabbani

Reputation: 16918

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

Upvotes: 0

Dylan Anthony
Dylan Anthony

Reputation: 682

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

Upvotes: 0

reidh.olsen
reidh.olsen

Reputation: 111

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

Upvotes: 0

zedfoxus
zedfoxus

Reputation: 37129

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.

Upvotes: 1

Related Questions