Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-10-07
13
Medium Priority
?
45 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
13 Comments
 
LVL 30

Expert Comment

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

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 70

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 30

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 70

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 30

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

688 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