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?
 
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
 
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
csePixelatedAuthor Commented:
i have run the above without the IDENTITY_INSERT, it comes up (0 row(s) affected)
0
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.