Solved

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

Posted on 2016-10-07
13
32 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 32
SQL SELECT query help 7 41
sql server query 6 10
average of calculation (TSQL) 4 9
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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

831 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