?
Solved

SQl query

Posted on 2016-11-18
19
Medium Priority
?
25 Views
Last Modified: 2016-12-28
Hello,


I  am trying to  insert rows from table1 to table2 which are not in table2. The query i have is inserting '0'  in the columns which are of datatype integer , marked as ".Instead  how can I  insert null instead of 0 in the columns (integer)

INSERT INTO [Pt] 

SELECT IET.[Iber]
      ,IET.[Invte]
      ,IET.[Sule]
      ,IET.[SeNo]
      ,IET.[Cy]
      ,IET.[Pde]
      ,IET.[Dion]
      ,IET.[Pion]
      ,IET.[Crge]
      ,IET.[Tpe]
      ,IET.[Aass]
      ,IET.[Eode]
      ,IET.[Clode]
      ,IET.[Quantity]
      ,IET.[TaxRate]
      ,IET.[GrossValue]
      ,IET.[NetValue]
      ,IET.[TaxValue]
      ,IET.[GrossValueHome1]
      ,IET.[NetValueHome1]
      ,IET.[TaxValueHome1]
      ,IET.[ExchangeRateHome1]
         ,IET.[InvoiceGUID]
         ,IET.Originator
         , ''
         , IET.DocumentType
         , ''
         , ''
         , ''
         , ''
         , ''
         , ' '
         , ''
         , ''
         , ''
         , ''
         , ''
         , ''
         , ''
         ,''
FROM table2


WHERE [InvoiceGUID] NOT IN 
    (SELECT [Pt] )

Open in new window

0
Comment
Question by:RIAS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 3
  • +1
19 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41892900
Try..

Insert into table2
SELECT * FROM table1
EXCEPT 
SELECT * FROM table2

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41892903
>The query i have is inserting '0'  in the columns which are of datatype integer , marked as "
Numeric data types such as int do not support empty strings '', so my guess is the default value of these columns is 0 so that's what is being inserted.  To change that to a NULL, replace all the ''s in your query with NULL.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41892904
Or.. try

INSERT INTO Table2
SELECT * FROM Table1 a
LEFT JOIN Table2 b
ON a.Col1 = b.col1
WHERE b.col1 IS NULL

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:RIAS
ID: 41892905
I have default to the column as null but ,still inserting '0'.
Is there no way we can have null instead of 0
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41892908
You can use like also...

CASE WHEN '' THEN NULL ELSE value END
0
 

Author Comment

by:RIAS
ID: 41892910
Pawan,
Can you please demonstrate in my query provided.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41892912
just type null in your query instead of ''
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41892933
try..

CREATE TABLE emp_records1
(
	Id INT,
	Name VARCHAR(80),
	Salary NUMERIC(8, 2)
)
GO

INSERT INTO emp_records1
(Id, Name, Salary)
VALUES
(1, 'B', 30000),
(2, 'C', 6000),
(3, 'D', 10000),
(4, 'E', 800),
(5, 'F', 500)


CREATE TABLE emp_records2
(
	Id INT,
	Name VARCHAR(80),
	Salary NUMERIC(8, 2)
)
GO

INSERT INTO emp_records2
(Id, Name, Salary)
VALUES
(1, 'B', 30000),
(2, 'C', 6000)

INSERT INTO emp_records2
SELECT Id,Name,Salary FROM emp_records1
EXCEPT
SELECT Id,Name, CASE WHEN Salary = 500 THEN NULL ELSE Salary END FROM emp_records2


SELECT * FROM emp_records2

Open in new window

0
 

Author Comment

by:RIAS
ID: 41892937
Eric,
Tried it doesnt work
0
 

Author Comment

by:RIAS
ID: 41892946
Pawan,

Find it confusing to implement in my query :
INSERT INTO [Pt] 

SELECT IET.[Iber]
      ,IET.[Invte]
      ,IET.[Sule]
      ,IET.[SeNo]
      ,IET.[Cy]
      ,IET.[Pde]
      ,IET.[Dion]
      ,IET.[Pion]
      ,IET.[Crge]
      ,IET.[Tpe]
      ,IET.[Aass]
      ,IET.[Eode]
      ,IET.[Clode]
      ,IET.[Quantity]
      ,IET.[TaxRate]
      ,IET.[GrossValue]
      ,IET.[NetValue]
      ,IET.[TaxValue]
      ,IET.[GrossValueHome1]
      ,IET.[NetValueHome1]
      ,IET.[TaxValueHome1]
      ,IET.[ExchangeRateHome1]
         ,IET.[InvoiceGUID]
         ,IET.Originator
         , ''
         , IET.DocumentType
         , ''
         , ''
         , ''
         , ''
         , ''
         , ' '
         , ''
         , ''
         , ''
         , ''
         , ''
         , ''
         , ''
         ,''
FROM table2


WHERE [InvoiceGUID] NOT IN 
    (SELECT [Pt] )

Open in new window

0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41892955
Please post entire query ? I think it is not complete..one
0
 

Author Comment

by:RIAS
ID: 41892957
SELECT IET.[Iber]
      ,IET.[Invte]
      ,IET.[Sule]
      ,IET.[SeNo]
      ,IET.[Cy]
      ,IET.[Pde]
      ,IET.[Dion]
      ,IET.[Pion]
      ,IET.[Crge]
      ,IET.[Tpe]
      ,IET.[Aass]
      ,IET.[Eode]
      ,IET.[Clode]
      ,IET.[Quantity]
      ,IET.[TaxRate]
      ,IET.[GrossValue]
      ,IET.[NetValue]
      ,IET.[TaxValue]
      ,IET.[GrossValueHome1]
      ,IET.[NetValueHome1]
      ,IET.[TaxValueHome1]
      ,IET.[ExchangeRateHome1]
         ,IET.[InvoiceGUID]
         ,IET.Originator
         , ''
         , IET.DocumentType
         , ''
         , ''
         , ''
         , ''
         , ''
         , ' '
         , ''
         , ''
         , ''
         , ''
         , ''
         , ''
         , ''
         ,''
FROM table2


WHERE [InvoiceGUID] NOT IN 
    (SELECT   )






INSERT INTO emp_records2
SELECT Id,Name,Salary FROM emp_records1
EXCEPT
SELECT Id,Name, CASE WHEN Salary = 500 THEN NULL ELSE Salary END FROM emp_records2

Open in new window

0
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41892968
Where is the 2nd table , Something is missing in bold part.  

INSERT INTO [Pt]
SELECT *
FROM table2
WHERE [InvoiceGUID] NOT IN  ( SELECT   )

you need to write something like..

INSERT INTO [Pt]
SELECT [colss.....] FROM table2
EXCEPT
SELECT [colss.....] FROM [Pt]
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 1000 total points
ID: 41892971
it has to work! your latest queries are not the same as the first one!

try something like this:

INSERT INTO [Pt] 

SELECT IET.[Iber]
      ,IET.[Invte]
      ,IET.[Sule]
      ,IET.[SeNo]
      , .......  -- removed for clarity
      ,IET.[Dion]
         , ''
         , IET.DocumentType
         , null
         , null 
         , ''
....

Open in new window

0
 

Author Comment

by:RIAS
ID: 41892975
Please ignore my previous query,apologies.
please find the query below

INSERT INTO [Pt] 

SELECT IET.[InvoiceNumber]
      ,IET.[InvoiceDate]
      ,IET.[SupplierTitle]
      ,IET.[SupplierInvoiceNo]
      ,IET.[Currency]
      ,IET.[PROACTISCode]
      ,IET.[Description]
      ,IET.[Position]
      ,IET.[CompanyRecharge]
      ,IET.[TravelExpenseType]
      ,IET.[AccomodationClass]
      ,IET.[EventCode]
      ,IET.[ClientCode]
      ,IET.[Quantity]
      ,IET.[TaxRate]
      ,IET.[GrossValue]
      ,IET.[NetValue]
      ,IET.[TaxValue]
      ,IET.[GrossValueHome1]
      ,IET.[NetValueHome1]
      ,IET.[TaxValueHome1]
      ,IET.[ExchangeRateHome1]
         ,IET.[InvoiceGUID]
         ,IET.Originator
         , ''
         , IET.DocumentType
         , ''
         , ''
         , ''
         , ''
         , ''
         , ' '
         , ''
         , ''
         , ''
         , ''
         , ''
         , ''
         , ''
         ,''
FROM [dbo].[table1] 


WHERE [InvoiceGUID] NOT IN 
    (SELECT [InvoiceGUID] FROM Pt )

Open in new window

0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41892980
have you tried replacing '' with null like I showed in my last query?
0
 

Author Comment

by:RIAS
ID: 41892987
Eric, Working,i think it will work ! Please bear with me
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41892989
can you give me the table schema for [pt] table ?
0
 

Author Closing Comment

by:RIAS
ID: 41892992
Eric,
Your solution worked.
Pawan your is working too but,Eric's is in line with my query.

Thanks
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
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…

752 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