Solved

SQl query

Posted on 2016-11-18
19
23 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 28

Expert Comment

by:Pawan Kumar
ID: 41892900
Try..

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

Open in new window

0
 
LVL 65

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 28

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 28

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 28

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 28

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 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 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 250 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 28

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2, need a pivot/cross tab query... 4 76
Checking for column changes SQL 2014 4 41
SQL syntax question 6 72
Help with SQL pivot 11 49
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

732 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