Solved

Insert the next number to Table 1 from Table 2 using SQL Server 2008 R2 query

Posted on 2016-10-07
13
34 Views
Last Modified: 2016-10-07
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
Comment
Question by:Queennie L
  • 6
  • 4
  • 3
13 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41834062
INSERT INTO tableName
SELECT MAX(Col1) + 1 FROM YoutTable
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41834066
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
 

Author Comment

by:Queennie L
ID: 41834151
@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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:Queennie L
ID: 41834163
@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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41834173
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
 

Author Comment

by:Queennie L
ID: 41834174
I want to give 250 each point to both of you but how?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41834175
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
 

Author Comment

by:Queennie L
ID: 41834179
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41834185
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41834189
@Author - what about the last code I submitted.? Have you checked it?
0
 

Author Comment

by:Queennie L
ID: 41834200
@Pawan Kumar Khowal:

The code you submitted is also right.

Thank you.
0
 

Author Comment

by:Queennie L
ID: 41834201
I don't know how to split 500.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41834213
@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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question