CODEWITHSUNDEEP
c#sql-server
dfgv
dfgv

Reputation: 97

Combine values from two columns into the third column

AUTOID --- BRANCHID ---- QUTNO
1           10           "10#1"
2           11           "11#2"

AUTOID is a primary, autogenerated column. I need to fill the QUTNO column with combination of values from columns branchid and autoid. How can i do these in sql server insert statement stored procedure?

Upvotes: 2

Views: 720

Answers (4)

Devasayal
Devasayal

Reputation: 40

You may use IDENT_CURRENT() to acheive this. http://msdn.microsoft.com/en-us/library/ms175098.aspx

INSERT INTO TABLE1 (BRANCHID , QUTNO)
VALUES (@branchId, @branchId + '#' + (SELECT CONVERT(VARCHAR(30), IDENT_CURRENT('dbo.TABLE1'))))

Upvotes: 0

vendettamit
vendettamit

Reputation: 14677

Here's the sample of computed columns:

CREATE TABLE dbo.Products 
(
    ProductID int IDENTITY (1,1) NOT NULL
  , QtyAvailable smallint
  , UnitPrice money
  --, InventoryValue AS QtyAvailable * UnitPrice
  , computed1 As Convert(varchar,QtyAvailable) + '#' + Convert(varchar, UnitPrice)
);

-- Insert values into the table.
INSERT INTO dbo.Products (QtyAvailable, UnitPrice)
VALUES (25, 2.00), (10, 1.5);

-- Display the rows in the table.
SELECT ProductID, QtyAvailable, UnitPrice, computed1
FROM dbo.Products;

it'll produce the output:

ProductID   QtyAvailable    UnitPrice   computed1
1                 25         2.00       25#2.00
2                 10         1.50       10#1.50

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166336

How about using a computed column?

Something like

DECLARE @TABLE TABLE(
        AUTOID INT IDENTITY(1,1),
        BRANCHID INT,
        QUTNO AS CAST(BRANCHID AS VARCHAR(25)) + '#' + CAST(AUTOID AS VARCHAR(25))
)

INSERT INTO @TABLE (BRANCHID) VALUES (10),(11)

SELECT * FROM @TABLE

SQL Fiddle DEMO

Upvotes: 11

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

Make the column a computed column and set the computational expression so the two columns are concatenated accordingly.

Upvotes: 6

Related Questions