Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 47
  • Last Modified:

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.
0
Queennie L
Asked:
Queennie L
  • 6
  • 4
  • 3
1 Solution
 
Pawan KumarDatabase ExpertCommented:
INSERT INTO tableName
SELECT MAX(Col1) + 1 FROM YoutTable
0
 
Scott PletcherSenior DBACommented:
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
0
 
Queennie LAuthor Commented:
@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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Queennie LAuthor Commented:
@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
0
 
Scott PletcherSenior DBACommented:
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.
0
 
Queennie LAuthor Commented:
I want to give 250 each point to both of you but how?
0
 
Pawan KumarDatabase ExpertCommented:
Hopefully it will work....

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


--

Open in new window


Enjoy !!
0
 
Queennie LAuthor Commented:
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.
0
 
Scott PletcherSenior DBACommented:
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.]
0
 
Pawan KumarDatabase ExpertCommented:
@Author - what about the last code I submitted.? Have you checked it?
0
 
Queennie LAuthor Commented:
@Pawan Kumar Khowal:

The code you submitted is also right.

Thank you.
0
 
Queennie LAuthor Commented:
I don't know how to split 500.
0
 
Scott PletcherSenior DBACommented:
@Pawan Kumar Khowal:
The code you submitted is also right.

That's just not true.  Split the points however you like, but don't assume both T-SQL return the same results.

CREATE TABLE Table1A (             ID INT )
INSERT INTO Table1A VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
CREATE TABLE Table2B(            ID INT)
INSERT INTO Table2B VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)

DELETE FROM Table2B WHERE ID = 11
--and code below still returns the number 11
--If DELETE FROM Table2B WHERE ID = 10
--code below returns NULL
--in summary, this code expects all values to be in table2::
--  but if that's true, you never need to read table2, just add 1 to the last ID in table1

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
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now