CODEWITHSUNDEEP
sql-server
Nadeem
Nadeem

Reputation: 665

How to insert record from one table to another?

I have 2 tables with same column e.g ID,SupplierID,ConditionValue,Status,Deleted and I insert data as in Code

INSERT INTO SCM_SupplierShippingRateHistory  
SELECT *           
FROM SCM_SupplierShippingRate          
WHERE  Id NOT IN(SELECT ID FROM dbo.GetIDsTableFromIDsList(@NonDeleteShippingIDs))        
AND SupplierId= @SupplierID        
AND ConditionValue IS NULL    
AND Deleted=0

I want to insert same data but I want to set status =4 how can I do this

Upvotes: 0

Views: 58

Answers (2)

SanketS
SanketS

Reputation: 973

you can set status like this.

INSERT INTO SCM_SupplierShippingRateHistory  
SELECT ID,SupplierID,ConditionValue,4 AS Status,Deleted           
FROM SCM_SupplierShippingRate          
WHERE  Id NOT IN(SELECT ID FROM dbo.GetIDsTableFromIDsList(@NonDeleteShippingIDs))        
AND SupplierId= @SupplierID        
AND ConditionValue IS NULL    
AND Deleted=0

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

Explicitly choose the columns.

INSERT INTO SCM_SupplierShippingRateHistory 
(
    Col1,
    Col2,
    .
    .
    .
    Status
) 
SELECT  Col1,
        Col2,
        .
        .
        .
        4 AS [Status]

FROM    SCM_SupplierShippingRate          
WHERE   Id NOT IN
        (
            SELECT ID 
            FROM   dbo.GetIDsTableFromIDsList(@NonDeleteShippingIDs)
        )        
        AND SupplierId= @SupplierID        
        AND ConditionValue IS NULL    
        AND Deleted=0

Upvotes: 1

Related Questions