[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQl query

Posted on 2016-11-18
19
Medium Priority
?
28 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 32

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 32

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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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 32

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 32

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 32

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 32

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 32

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

656 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