copy rows in the same table based on conditions

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

alig SSC Enthusiast Points: 188 More actions · Answer 4

alig

SSC Enthusiast

Points: 188

December 22, 2021 at 7:03 am

#3966183

Thanks , it was the trigger

Phil Parkin SSC Guru Points: 245813 More actions · Answer 5

alig wrote:

Thanks , it was the trigger

Sounds like the trigger has been written in such a way that it accommodates only single-row INSERTs, which is a common problem. It needs a rewrite!

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

jcelko212 32090 SSCrazy Eights Points: 9220 More actions · Answer 6

>> I have the table as in data below<<

Please look at my credentials. This will let you know I have some authority when I tell you you're doing everything completely wrong. First of all, for the past 30+ years standard netiquette on SQL forums has been post in DDL and insert statements, not make people copy data over and over, row by row, from a spreadsheet. Spreadsheets do not behave like tables; spreadsheets do not have keys; spreadsheets use a completely different data model. But more than that, standard netiquette that you don't make people open attachments in a form of any kind. This is not just SQL! Are you the guy in the office who opens emails from strangers then doesn't understand why what's wrong malware got into his company computer?

Unlike some other people here, I'm not willing to endanger my computer to help somebody for free. We you please quit being so rude? Do you know how to post DDL

>>, now I would like to copy all of the rows in which TEXT1 is 'erledigt' and the DATE2 is within the year 2021, I would like to copy the rows within the same table and make sure to copy the same number of rows as they are in the table currently, for example for a TEXT2 Inspektion OLI PLUS, it occurs 12 times, so I would like to make sure that the it gets copied too 12 times. <<

An SQL programmer would create a view off of a base table, not make copies. That's how we had to do with mag tapes and punchcards in the pre-relational days. You are literally 50 years behind the technology. But more than that, one of the fundamentals of any database, even the non-relational ones, is to reduce redundancy, not increase it. I see you use the old COBOL keyword OCCURS ; have you ever read anything by Dr. Codd about his degree of duplication attribute? We had to repeat copies of things back in the old days of punchcards (yes, I am not old and have worked with this technology).

I see you like "column names" to make them look like a spreadsheet or an array in a procedural language. In SQL programmer would have given a name that implies exactly what that text contains.

I also noticed that you are ignorant of the ISO 8601 standard, which the only date time format allowed in standard SQL. This is the expected yyyy-mm-dd layout that is acceptable to all SQL products and every ISO standard that uses the date.

>> While I am copying I would like to set the new DAT>> 3 as 31.12.2021 [sic: 2021-12-31), also the part which confuses me is that there are FKs and PKs and all the records [sic: rows are not records!} have their own Ids [sic: record numbers are not keys] and I do not want the new rows to have old IDS. <<

Identifiers are usually physical record locations. You're dumb enough to have used an identity column in your data, instead of a real key, you can get duplicates. Think of the identifier table property as being like the parking lot space numbers in a garage. There is nothing logically to keep that space from being used. Buying an automobile or truck or a motorcycle. This is why automobile manufacturers use the VIN. You have horrible fundamental concept errors.

>> I have the following query and I was planning to use INSERT INTO .

SELECT * FROM dbo.FREE04 -- array subscript and not a table name? What kind of entity is a "free"? Non-SQL programmers who are still back in spreadsheets or even BASIC tend to use meaningless short names like this.

>> ... WHERE TEXT1='erledigt' AND TEXT2 like 'inspektion%' AND DATE2 BETWEEN '01-01-2021' AND '31-12-2021' ORDER BY DATE2 DESC; <<

Why are you sorting a query result? 30 years ago, when we were setting up SQL, we wanted to have a tiered architecture. This meant that there would be a separate tier for display purposes, and sorting be done in that tier, not in the query. The order by clause converts things from a table to a cursor. Again you show your spreadsheet/array mindset by adding numbers to the ends of poorly named attributes. What exactly does a "DATE2" mean in a relational model? Something like, "customer_purchase_date" would be a better name for a column in an RDBMS..

Everybody else's been very patient with you, but I would rather you get it right and learn how to be a real SQL programmer. If you keep posting to forums and have people do your work for you, you will become a dangerous liability to anyone that hires you because you won't be competent. If you want to try again and actually follow netiquette, and I'll be happy to play with your code. Hey, this is your chance to get some help from someone who actually invented the language and has written 10 books on it 🙂

Please post DDL and follow ANSI/ISO standards when asking for help.