Insert consecutive numbers

Question

Hi. I have a table A (Acons, A1, A2, A3) in which I should insert information from another table B with columns (B1, B2, B3). The Acons is a column in which should contain some consecutive numbers (it is not an identity and I cannot make it identity). I know xmin - starting the from number the sequence has to be computed. How can I insert the rows into the table A, using a single Insert statement?

I tried like the following, but it didn't work:

DECLARE @i AS INT;

SET @i = xmin;    

INSERT INTO A(Acons, A1, A2, A3)
SELECT @i = (Bcons = (@i + 1)), B1, B2, B3
FROM B

Unfortunatelly, the above solution does not work;

Answer 1

I do not know from where comes xmin but, try it this.

DECLARE @i AS INT;
SET @i = xmin; 
Insert into A
Select @i+1,b1,b2,b3 from B

Answer 2

You already have a set of rows to generate numbers from to add to Bcons. It's trickier in SQL Server 2000 but one hopes you're on SQL Server 2005 +

WITh cNumbers AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY Bcons) AS Number,
        Bcons, B1, B2, B3
    FROM
        B
)
INSERT INTO A (Acons, A1, A2, A3)
SELECT
    Bcons + Number /* need -1 here? */, B1, B2, B3
FROM
    cNumbers

As Philip Kelley mentioned, we don't need the CTE. Doh.

SELECT
    Bcons + ROW_NUMBER() OVER (ORDER BY Bcons) /* need -1 here? */, B1, B2, B3
FROM
    B

Answer 3

This is another way to do it:

DECLARE @i AS INT;
SET @i = xmin;
insert into a
select ((ROW_NUMBER() OVER (ORDER BY b1))+@i) AS Number,b1,b2,b3 from b

--Re-edited