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.

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.

SQL Insert Into leaves some columns null, This is the "tag index" column in the old table and it ranges from 1 to 2. So, I want the value (called val in code) in rows with a tag index of 1 in� Instead I would like to insert NULL to the values in the table 1. Below is the example.

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

SQL inserting NULL values, The SQL INSERT statement can also be used to insert NULL value for a column. for a single row into the table 'agents' then, the following SQL statement can be used: SQL Code: INSERT SQL insert values in specific columns. The SQL It is required to mention the column(s) name in the SQL query. SQL insert values in specific columns The SQL INSERT INTO statement can also be used to insert one or more specific columns for a row. It is required to mention the column(s) name in the SQL query.

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

What is the command to insert null values in SQL fields?, There's a few ways: Let's assume this table: create table mytable (a int, b int, c int, You can also use INSERT INTO mytable SELECT <rows with nulls> from command will not work even if you have not COMMITed your query output in SQL ? You can explicitly insert a NULL by using INSERT INTO mytable (a, b, c) values (1, NULL, 2); You can also omit the column in an INSERT using something like; insert into mytable (a, c) values (1, 3); In both rows, the column "b" in the above table will be NULL. You can also use INSERT INTO mytable SELECT <rows with nulls> from othertable.

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

Inserting values into specific columns, You can add data to some columns in a row by specifying only those columns and Define all other columns not included in the column list to allow NULL or have columns in the table, it must match the order in which you specify the values� If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. The INSERT INTO syntax would be as follows:

Insert NULL value into INT column in MySQL?, You can insert NULL value into an int column with a condition i.e. the column must not have NOT NULL constraints. Selected Reading; UPSC IAS Exams Notes � Developer's Best Practices � Questions and Answers � Effective Resume Writing To understand the above syntax, let us first create a table. What will happen if you try to use the INSERT command to insert a NULL value into a column that has been assigned a PRIMARY KEY constraint An error message is returned and the row is not added to the table. The VALUES clause is not included with the INSERT command if the data is being retrieved from another table.

Inserting Rows with NULL Column Values, Sometimes when a new row is added to a table, values are not necessary or In these cases, a NULL value should be assigned to those columns when the row is When a specific value is not provided for a column on insertion, it is standard� The preceding query returns many columns with null values. Now let's say there is a requirement to replace all these null values with meaningful text. For example, replace null with "no name" for the name column and replace null with "no gender" for the gender column.

How to insert null for missing column?, INSERT INTO TABLE1 (a, b, c) SELECT a, b, NULL FROM TABLE2;. This will insert the values from columns a and b in TABLE2 into the corresponding columns� Method 2(Inserting specific columns): INSERT INTO Student(ROLL_NO,NAME,Age) SELECT ROLL_NO, NAME, Age FROM LateralStudent; Output: This query will insert the data in the columns ROLL_NO, NAME and Age of the table LateralStudent in the table Student and the remaining columns in the Student table will be filled by null which is the default value of the remaining columns.

Comments
  • 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 did you use the query under Provide NULL or under Avoid inserting data... or Change data after insert?
  • Also type commit tran. Perhaps there's a transaction already running that needs to be committed.
  • 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
  • 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.
  • 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
  • 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.
  • 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.
  • 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.