Solved

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

Posted on 2016-10-07
13
38 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 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can we attach PDF to table 2 46
SQL Server Database Inventory 7 30
Search Text in Views 2 28
SQL Query 20 23
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

751 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