Append to SQL table, duplicate lines already in SQL table

i have a simple 3 field sql table
ID , ID_AutoReport , Limit

ID is (PK, Int, not null)
ID_AutoReport is (int, null)
Limit is (nvchar(200), not null)

i need a simple sql query to add new rows with new IDs the number of rows i will be adding will be equal to the number of entries for  ID_AutoReport that match 349730 , and will duplicate the data in the Limit field. (I hope I haven't made this confusing)
i think i've gotten myself a little confused as to the way parts of my query relate to the data i am replicating.

SET IDENTITY_INSERT DAAutoReportsLimit ON;

INSERT INTO [SYSTEM SETUP].dbo.[DAAutoReportsLimit] ( 
                                 ID_AutoReport ,
                                 Limit )
            SELECT 
                   350163 ,
                   Limit
            FROM   [SYSTEM SETUP].dbo.[DAAutoReportsLimit]
            WHERE  ID_AutoReport = 349730;
SET IDENTITY_INSERT DAAutoReportsLimit OFF

Open in new window


example:
table before:
ID , ID_AutoReport , Limit
1,349730,555-555-1234
2,349730,555-555-4321
3,349730,bob@gmail.com
4,349731,tom@elseware.net

table after:
1,349730,555-555-1234
2,349730,555-555-4321
3,349730,bob@gmail.com
4,349731,tom@elseware.net
5,350163,555-555-1234
6,350163,555-555-4321
7,350163,bob@gmail.com
LVL 1
csePixelatedAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russ SuterCommented:
I'm trying to understand this but a couple of things are tripping me up.

I see you've defined ID as the primary key but is it also the identity column? If so then it will automatically populate with the next sequential unused value.

Furthermore, since you're not actually inserting a value directly into ID in your statement above, setting IDENTITY_INSERT isn't needed. You would only need to do that if you were explicitly setting the identity column in a query.

Other than that, based on what you're telling us you need and what you've provided I think you've pretty much got it working. Unless there's something I'm missing which is entirely possible.
0
csePixelatedAuthor Commented:
yes, so i will remove the IDENTITY_INSERT, when i first started i thought the number i had to duplicate was the ID not the ID_AutoReport.
so that's why i had added that. mind you i figured out i had messed up and reverted to a backup.
0
csePixelatedAuthor Commented:
i have run the above without the IDENTITY_INSERT, it comes up (0 row(s) affected)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Russ SuterCommented:
I can't see from your above example why that would be the case. If it returns 0 row(s) affected then that means your where clause is selecting everything away. In your above example it should produce exactly the results you indicated.

Also, 0 row(s) updated means the query syntax is valid. It just didn't have anything to update.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
csePixelatedAuthor Commented:
i think i have found my mistake, i got the number in the where clause wrong
0
Russ SuterCommented:
Yep, that would certainly do it.
0
csePixelatedAuthor Commented:
Thankyou Russ, I really just needed that verification that i had the query correct.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.