December 19, 2021 at 8:02 pm
I have the table as in data below , 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.
While I am copying I would like to set the new DATE3 as 31.12.2021, also the part which confuses me is that there are FKs and PKs and all the records have their own Ids and I do not want the new rows to have old IDS.
I have the following query and I was planning to use INSERT INTO .
I would appreciate help , Thanks in advance.
SELECT * FROM dbo.FREE04
WHERE TEXT1='erledigt' AND TEXT2 like 'inspektion%' AND DATE2 BETWEEN '01-01-2021' AND '31-12-2021' ORDER BY DATE2 DESC;
December 19, 2021 at 10:07 pm
DATE2 >= '01-01-2021' AND DATE2 <'01-01-2022' ORDER BY DATE2 DESC;
December 19, 2021 at 10:51 pm
Is the Id column an IDENTITY column?
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.
December 20, 2021 at 7:07 am
Hi yes , the Id column is IDENTITY column
December 20, 2021 at 7:10 am
Thanks but how do I insert / copy the rows that I have selected in the same table dbo.FREE04 without having duplicate id columns & without messing up Pk & Fk ?
like this
INSERT INTO dbo.FREE04
SELECT * FROM dbo.FREE04
WHERE TEXT1='erledigt' AND TEXT2 like 'inspektion%' AND DATE2 >= '01-01-2021' AND DATE2 <'01-01-2022' ORDER BY DATE2 DESC;
Thanks in advance.
December 20, 2021 at 9:54 am
Your syntax is nearly right, but not quite. You will have to explicitly name all of the columns, except for the IDENTITY column, which will take care of itself as part of the INSERT. You can also see here how to set Date3 to 31 Dec as part of the same query.
INSERT dbo.FREE04 (Col1, Col2, Date3, ...)
SELECT Col1, Col2, '20211231', ... FROM dbo.FREE04 WHERE ...
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.
December 20, 2021 at 10:01 am
Thanks. How can I ensure for example that rows (that have a TEXT1 = inspektion ) that occur 5 times in the table to be replicated 5 more times and also rows with TEXT1=inspektion OLI plus occuring for e.g. 3 times in the table to be replicated 3 more times.
Can I add sort of a count based on the TEXT1 column and then make INSERT INTO create the new rows based on the count ?
Thanks again .
December 20, 2021 at 10:03 am
sorry and what about the FK and PK , do I need to copy them too in new rows ? Thanks
December 20, 2021 at 10:15 am
Do you have a non-Production version of the database that you can test this on? You really need to do that, I think.
How can I ensure for example that rows (that have a TEXT1 = inspektion ) that occur 5 times in the table to be replicated 5 more times and also rows with TEXT1=inspektion OLI plus occuring for e.g. 3 times in the table to be replicated 3 more times.
Everything which is selected by your query will be inserted.
Can I add sort of a count based on the TEXT1 column and then make INSERT INTO create the new rows based on the count ?
No need. Just make sure that your SELECT query results in exactly the rows you wish to INSERT (excluding the IDENTITY column).
sorry and what about the FK and PK , do I need to copy them too in new rows ? Thanks
Assuming the IDENTITY column is the PK, as I said above, it will take care of itself with new numbers.
Regarding FKs ... we're talking about columns in this table which reference PKs in other tables, correct? If that is the case, there should be no issue. The new rows will continue to reference the PKs in the other tables, same as the existing rows.
But please remember, I cannot see your data, so this advice needs to be tested. If you know how to use
BEGIN TRAN, ROLLBACK and COMMIT
you might also use them as part of your testing.
Another possibility which you may find interesting is if you have a column in the table which is unused, or contains free text. As part of the INSERT, set that column to something unique ('xxxxxx' or whatever works for you). If you do that, you can always SELECT the rows which have just been inserted afterwards (and DELETE them, if necessary).
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.
December 20, 2021 at 10:33 am
Hi, this is test data , I am not into working on production & Thanks alot for the answer ,would be nice if you could test too with the sugegsted method, deeply appreciated.
Testing is for you to do, not me! You should learn a few things in the process.
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.
December 20, 2021 at 10:51 am
okay Thanks
December 20, 2021 at 3:40 pm
You can first insert into a TEMP Table to see the results, then insert from Temp table back into target table.
It's an extra step, but helps you test & verify the result.
INSERT INTO dbo.FREE04_TEMP
SELECT * FROM dbo.FREE04 WHERE .............
December 21, 2021 at 10:52 am
Hi There
So this is my querry
USE [dummy_database]
GO
INSERT INTO [dbo].[FREE04]
([TEXT1]
,[FK1]
,[YESNO1]
,[TEXT2]
,[NOTES1]
,[DATE1]
,[DATE2]
,[DOCUMENTREF1]
,[DOCUMENTREF2]
,[DOCUMENTREF3]
,[TEXT3]
,[TEXT4]
,[TEXT5]
,[NOTES2]
,[TEXT7]
,[TEXT8]
,[NOTES3]
,[TEXT9]
,[NUMBER2]
,[NUMBER3]
,[TEXT12]
,[TEXT13]
,[TEXT11]
,[TEXT14]
,[TEXT15]
,[NUMBER4]
,[TEXT16]
,[NOTES4]
,[DATE3]
,[TEXT6]
,[NUMBER5]
,[TEXT10]
,[TEXT17]
,[DATE4]
,[TEXT20]
,[TEXT21]
,[CREATEDBY]
,[MODIFIEDBY]
,[DATECREATE]
,[DATEMODIFIED]
,[INTERNET1]
,[TEXT22]
,[TEXT18]
,[FK2]
,[TEXT19]
,[FK3]
,[DATE5]
,[TEXT23]
,[TEXT25]
,[TEXT26]
,[TEXT27]
,[DATE6]
,[OptimisticLockField]
,[YESNO2]
,[TEXT24]
,[TEXT28]
,[NUMBER1]
,[NUMBER6])
SELECT [TEXT1]
,[FK1]
,[YESNO1]
,'NEW am 21.12.2021 Test Ticket'
,[NOTES1]
,[DATE1]
,[DATE2]
,[DOCUMENTREF1]
,[DOCUMENTREF2]
,[DOCUMENTREF3]
,[TEXT3]
,[TEXT4]
,[TEXT5]
,[NOTES2]
,[TEXT7]
,[TEXT8]
,[NOTES3]
,[TEXT9]
,[NUMBER2]
,[NUMBER3]
,[TEXT12]
,[TEXT13]
,[TEXT11]
,[TEXT14]
,[TEXT15]
,[NUMBER4]
,[TEXT16]
,[NOTES4]
,[DATE3]
,[TEXT6]
,[NUMBER5]
,[TEXT10]
,[TEXT17]
,[DATE4]
,[TEXT20]
,[TEXT21]
,[CREATEDBY]
,[MODIFIEDBY]
,[DATECREATE]
,[DATEMODIFIED]
,[INTERNET1]
,[TEXT22]
,[TEXT18]
,[FK2]
,[TEXT19]
,[FK3]
,[DATE5]
,[TEXT23]
,[TEXT25]
,[TEXT26]
,[TEXT27]
,[DATE6]
,[OptimisticLockField]
,[YESNO2]
,[TEXT24]
,[TEXT28]
,[NUMBER1]
,[NUMBER6]
FROM dbo.FREE04
WHERE TEXT26='Sample Herrmann';
GO
now the problem is if the result of my SELECT WHERE is more than 1 result SQL gives the following error
The subquery returned more than one value. This is not allowed if the subquery follows =,! =, <, <=,>, Or> = or is used as an expression.
As long as the result of SELECT WHERE is only 1 record , it generated 1 record , but I would like to replicate more than 1 row , what can be the issue here ? Thanks
December 21, 2021 at 11:00 am
There are no subqueries in the code you posted. You must be executing something else as well.
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.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply