• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 29
  • Last Modified:

SQl query

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
RIAS
Asked:
RIAS
  • 8
  • 7
  • 3
  • +1
2 Solutions
 
Pawan KumarDatabase ExpertCommented:
Try..

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

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Pawan KumarDatabase ExpertCommented:
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
RIASAuthor Commented:
I have default to the column as null but ,still inserting '0'.
Is there no way we can have null instead of 0
0
 
Pawan KumarDatabase ExpertCommented:
You can use like also...

CASE WHEN '' THEN NULL ELSE value END
0
 
RIASAuthor Commented:
Pawan,
Can you please demonstrate in my query provided.
0
 
Éric MoreauSenior .Net ConsultantCommented:
just type null in your query instead of ''
0
 
Pawan KumarDatabase ExpertCommented:
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
 
RIASAuthor Commented:
Eric,
Tried it doesnt work
0
 
RIASAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Please post entire query ? I think it is not complete..one
0
 
RIASAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
RIASAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
have you tried replacing '' with null like I showed in my last query?
0
 
RIASAuthor Commented:
Eric, Working,i think it will work ! Please bear with me
0
 
Pawan KumarDatabase ExpertCommented:
can you give me the table schema for [pt] table ?
0
 
RIASAuthor Commented:
Eric,
Your solution worked.
Pawan your is working too but,Eric's is in line with my query.

Thanks
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 8
  • 7
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now