How do I...how to add duplicate row as new row except one column in sql

Question

1.00/5 (2 votes)

See more:

I have one table tableName is tblProduct...

the table is

ID Product Customer
1 Lux Hari
2 Dove Ram
2 Pears Banu

I need to add the Last two rows into the same table except id like this...

ID Product Customer
1 Lux Hari
2 Dove Ram
2 Pears Banu
3 Dove Ram
3 Pears Banu

Posted 20-Feb-14 0:29am

Member 10339067

Updated 20-Jul-23 22:47pm

Add a Solution

Comments

Member 10339067 20-Feb-14 6:41am

Reply quickly

2 solutions

Solution 1

for insert last two duplicate rows

SQL

insert into tblProduct(Product,Customer)  select Product,CustomerFrom tblProduct where id in (select max(id) from tblProduct union select max(id)-1  from tblProduct)

Posted 20-Feb-14 0:41am

King Fisher

Comments

Member 10339067 20-Feb-14 6:53am

Thank you very much for the solution. But I have a different scenario where the ID are random also the ID value is 'NULL' when executed using the above query.
Please help solving my issue.

King Fisher 20-Feb-14 7:00am

sqlserver 2012?

Member 10339067 20-Feb-14 7:06am

Sqlserver2005

Member 10339067 20-Feb-14 7:28am

otherwise do onething pass id as own value

King Fisher 20-Feb-14 7:32am

Does Latst() is Inbuilt_function in your Sqlserver.check out

http://www.w3schools.com/sql/sql_func_last.asp

Member 10339067 20-Feb-14 7:34am

thank u sir....

King Fisher 20-Feb-14 7:38am

Does it work?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

george4986 · Accepted Answer · 2014-02-20T01:38:00

Solution 2

check this

SQL

insert into tblProduct(id,Customer,Product)
(select TOP 2 id+1,Customer,Product
From tblProduct
group by id,Product,customer  )order by id desc

Posted 20-Feb-14 1:38am

george4986

Comments

Member 10339067 20-Feb-14 8:21am

Your first query displays NULL to id, second query is working and it is adding the Maximum number +1. Its fine...
Shall we give where condition to that select command(select TOP 2 id+1,Customer,Product From tblProduct group by id,Product,customer WHERE id=@id)

Member 10339067 20-Feb-14 8:34am

I got the solution to that.. i attached that.. Thank u very much sir........You gave some idea so i got the result thank u so much sir......
INSERT INTO tblProduct(id,Product,Customer)
(SELECT 33,Product,Customer FROM tblProduct WHERE id=2)

george4986 20-Feb-14 22:46pm

i didn't posted 2 queries.Any way, u got the result right? .that's nice ;-)
gud luck