Queennie L
asked on
Insert the next number to Table 1 from Table 2 using SQL Server 2008 R2 query
Create a query to where I want to grab the number the next number in Table 2 to be inserted to Table 1.
Table 1
1
2
3
4
5
6
7
8
9
10
Max Number is 10 in Table 1.
Table 2
1
2
3
4
5
6
7
8
9
10
11
12
13
The result would be:
Table 1
1
2
3
4
5
6
7
8
9
10
11
Thank you for your help.
Table 1
1
2
3
4
5
6
7
8
9
10
Max Number is 10 in Table 1.
Table 2
1
2
3
4
5
6
7
8
9
10
11
12
13
The result would be:
Table 1
1
2
3
4
5
6
7
8
9
10
11
Thank you for your help.
INSERT INTO Table1 ( number )
SELECT ca1.number
FROM (
SELECT MAX(number) AS number
FROM Table1
) AS t1_max_number
CROSS APPLY (
SELECT TOP (1) t2.number
FROM Table2 t2
WHERE t2.number >= t1_max_number.number
ORDER BY t2.number
) AS ca1
SELECT ca1.number
FROM (
SELECT MAX(number) AS number
FROM Table1
) AS t1_max_number
CROSS APPLY (
SELECT TOP (1) t2.number
FROM Table2 t2
WHERE t2.number >= t1_max_number.number
ORDER BY t2.number
) AS ca1
ASKER
@Pawan Kumar Khowal:
The result would be: 10 + 1 = 11 but your query would result in 14
@ScottPletcher:
Your query resulted in: 13
The max number in Table 1 is 10. The next number for Table 1 would be 11 based from Table 2.
Thank you for your help.
The result would be: 10 + 1 = 11 but your query would result in 14
@ScottPletcher:
Your query resulted in: 13
The max number in Table 1 is 10. The next number for Table 1 would be 11 based from Table 2.
Thank you for your help.
ASKER
@Pawan Kumar Khowal:
Your query is right but I have to modify it:
SELECT MAX(col1) + 1 FROM Table 1 WHERE Year = YEAR(getdate())
The result would be: 11
@ScottPletcher:
Your query is right but I have to extract what you already have:
SELECT TOP (1) t2.number
FROM Table2 t2
WHERE t2.number > (SELECT MAX(number) AS number
FROM Table1)
ORDER BY t2.number
The result would be: 11
Your query is right but I have to modify it:
SELECT MAX(col1) + 1 FROM Table 1 WHERE Year = YEAR(getdate())
The result would be: 11
@ScottPletcher:
Your query is right but I have to extract what you already have:
SELECT TOP (1) t2.number
FROM Table2 t2
WHERE t2.number > (SELECT MAX(number) AS number
FROM Table1)
ORDER BY t2.number
The result would be: 11
I don't have sample data to run it, but I would think the code I posted would return 11 if the value 11 is in table 2. I took your original q to mean that you only wanted to add values to table 1 that were found in table 2, not just add 1 to the last number in table 1.
ASKER
I want to give 250 each point to both of you but how?
Hopefully it will work....
Enjoy !!
CREATE TABLE Table1A
(
ID INT
)
GO
INSERT INTO Table1A VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10)
CREATE TABLE Table2B
(
ID INT
)
GO
INSERT INTO Table2B VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10),
(11),
(12),
(13)
INSERT INTO Table1A ( Id )
SELECT Numbew FROM
(
SELECT MAX(Id) Id FROM Table1A k
)ra
CROSS APPLY
(
SELECT ID + 1 Numbew
FROM Table2B q
WHERE ra.Id = q.id
)n
--
Enjoy !!
ASKER
ScottPletcher:
That is correct. I want whatever found in Table 2 will be inserted to the next Max number from Table1 .
This is what I want.
That is correct. I want whatever found in Table 2 will be inserted to the next Max number from Table1 .
This is what I want.
D'OH, sorry, quite right, it should be " > " NOT " >= ". Then my code does work.
I know there's a way to split points, I just can't remember the details, since I mostly just answer qs.
[Fwiw, Pawan's code doesn't meet the requirements you stated.]
I know there's a way to split points, I just can't remember the details, since I mostly just answer qs.
[Fwiw, Pawan's code doesn't meet the requirements you stated.]
@Author - what about the last code I submitted.? Have you checked it?
ASKER
@Pawan Kumar Khowal:
The code you submitted is also right.
Thank you.
The code you submitted is also right.
Thank you.
ASKER
I don't know how to split 500.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT MAX(Col1) + 1 FROM YoutTable