Solved

SQl query

Posted on 2016-11-18
19
19 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
  • 8
  • 7
  • 3
  • +1
19 Comments
 
LVL 24

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 24

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
 

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 24

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 69

Expert Comment

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

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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 24

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 24

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 69

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 69

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 24

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

912 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now