SQL Server stored procedure inserting duplicate rows
how to avoid duplicate records in sql stored procedure
sql copy row with new id
how to check duplicate records in stored procedure
how to avoid inserting duplicate records in sql server c#
I have a table with column
GetDup and I'd like to the duplicate records based on the value of this column. For example, if value on is 1 in
GetDup, then duplicate the record once. If value in the column is 2, then duplicate the record twice and so on and the statement has to be in looping statement.
What will be a good way to write a stored procedures for this? Please help.
+--------+--------------+---------------+ | Getdup | CustomerName | CustomerAdd | +--------+--------------+---------------+ | 1 | John | 123 SomeWhere | | 2 | Bob | 987 SomeWhere | +--------+--------------+---------------+
What I want:
+--------+--------------+---------------+ | Getdup | CustomerName | CustomerAdd | +--------+--------------+---------------+ | 1 | John | 123 SomeWhere | | 1 | John | 123 SomeWhere | | 2 | Bob | 987 SomeWhere | | 2 | Bob | 987 SomeWhere | | 2 | Bob | 987 SomeWhere | +--------+--------------+---------------+
picture of data
Answer #2 After Clarification
Number Table to the Rescue!
The number table in my example (or tally table, if you want to call it that), is both temporary and very small. To make it bigger, just add more values to
z and add more
CROSS JOINs. In my opinion, a number table and a calendar table are both things that should be in every database you have. They are extremely useful.
MS SQL Server 2017 Schema Setup:
CREATE TABLE mytable ( Getdup int, CustomerName varchar(10), CustomerAdd varchar(20) ) ; INSERT INTO mytable (Getdup, CustomerName, CustomerAdd) VALUES (1,'John','123 SomeWhere'), (2,'Bob','987 SomeWhere') ;
;WITH z AS ( SELECT * FROM ( VALUES(0),(0),(0),(0) ) v(x) ) , numTable AS ( SELECT num FROM ( SELECT ROW_NUMBER() OVER (ORDER BY z1.x)-1 num FROM z z1 CROSS JOIN z z2 ) s1 ) SELECT t1.Getdup, t1.CustomerName, t1.CustomerAdd FROM mytable t1 INNER JOIN numTable ON t1.getdup >= numTable.num ORDER BY CustomerName, CustomerAdd
| Getdup | CustomerName | CustomerAdd | |--------|--------------|---------------| | 2 | Bob | 987 SomeWhere | | 2 | Bob | 987 SomeWhere | | 2 | Bob | 987 SomeWhere | | 1 | John | 123 SomeWhere | | 1 | John | 123 SomeWhere |
EDIT: After further clarification of the problem, this won't duplicate rows, this will only duplicate the data in a column.
Something like one of these might work.
SELECT replicate(mycolumn,getdup) AS x FROM mytable
SELECT repeat(mycolumn,getdup) AS x FROM mytable
SELECT rpad(mycolumn,getdup*length(mycolumn),mycolumn) AS x FROM mytable
SELECT repeat(mycolumn,getdup+1) AS x FROM mytable
If you can provide more details for exactly what you want and what you're working with, we might be able to help you better.
NOTE 2: Depending on what you need, you may need to do some math magic. You say above if
GetDup is 1 then you want one duplicate. If that means that your output should be
GetDup``GetDup, then you'll want to add one in the
rpad() functions. ie
replicate(mycolumn,getdup+1). Oracle SQL will be a little different, since it uses
SQL server Inserting duplicate rows. - Wrox, My sql server is inserting duplicate rows -- everything is same in two consecutive I am using stored procedure to populate the orders table. My sql server is inserting duplicate rows -- everything is same in two consecutive rows except the primary key. This is in a table named orders. I am using stored procedure to populate the orders table.
In standard SQL you can use a recursive CTE:
with recursive cte as ( select t.dup, . . . from t union all select cte.dup - 1, . . . from cte where cte.dup > 1 ) select * from cte;
Of course, not all databases support recursive CTEs (and the
recursive keyword is not used in some of them).
How to avoid duplicate records using Store Procedure., Your way of declaring parameter @result is wrong. Correct way to declare a local variable is: Hide Copy Code. DECLARE @result int -- in body Inserting lots of rows into SQL with a Logic App and a Stored Procedure Posted by michaelstephensonuk December 9, 2018 in Logic App When you are working with API’s and Logic Apps and there is lots of rows of data involved you will sometimes come up with the following problems:
So, you want recursive solution :
with t as ( select Getdup, CustomerName, CustomerAdd, 0 as id from table union all select Getdup, CustomerName, CustomerAdd, id + 1 from t where id < getdup ) insert into table (col1, col2, col3) select Getdup, CustomerName, CustomerAdd from t order by getdup option (maxrecursion 0);
How to avoid inserting a duplicate using stored procedure?, Hi All, I am a beginner, I don't have any idea on "How to write a stored procedure for inserting data". MyTable (pk, val) values (@pk, @val) set @bit = 1 end try begin catch -- duplicate key set @bit = 0 NET and SQL Server Find Duplicates From a Table in SQL Server. Summary: in this tutorial, you will learn how to use the GROUP BY clause or ROW_NUMBER() function to find duplicate values in a table. Technically, you use the UNIQUE constraints to enforce the uniqueness of rows in one or more columns of a table.
Question About Trapping Attempt to Insert Duplicate Record in , It attempts to do a simple insertion of a new record into a table in my SQL Server 2012 database. I'd like to adjust the stored procedure to where Things to Do. You can now easily identify and relieve a database table from duplicate value. 1. Try creating the UspRemoveDuplicatesByAggregate stored procedure based on the method mentioned above and remove duplicates by calling the stored procedure 2. Try modifying the stored procedure created above (UspRemoveDuplicatesByAggregates)
Different ways to SQL delete duplicate rows from a SQL Table, Suppose your SQL table contains duplicate rows and you want to remove those duplicate rows. Let's create a sample Employee table and insert a few records in it. commonly known as CTE to remove duplicate rows in SQL Server. However, I would suggest not to implement these procedures and -- Example for INSERT Statement inside the Stored Procedure in SQL Server USE [SQL Tutorial] GO IF OBJECT_ID ( 'InsertStoredProcedureFourthExample', 'P' ) IS NOT NULL DROP PROCEDURE InsertStoredProcedureFourthExample; GO CREATE PROCEDURE InsertStoredProcedureFourthExample @Occupation VARCHAR(50) AS BEGIN SET NOCOUNT ON; INSERT INTO [EmployeeDup] ([
Duplicate records returned from table with no duplicates, Duplicate records returned from table with no duplicates · sql-server sql-server-2008-r2 duplication isolation-level. I have a stored procedure that Use SSIS package to SQL delete duplicate rows. SQL Server integration service provides various transformation, operators that help both administrators and developers in reducing manual effort and optimize the tasks. SSIS package can remove the duplicate rows from a SQL table as well.
- Tag your question with the database you are using.
- Please provide sample data and expected results in table form
- sample data is attached
- I would also question why you want to reinsert duplicated records into your table. What is the end-goal?
- sqlfiddle.com/#!17/9e0b4/1 <<< This includes the +1 to
mycolumnthat it seems like you're asking for.
- How this will relate with the o/p ??
- @YogeshSharma My original answer was before OP clarified. I've updated my answer to use a tally table to get rows instead of a replicated column.
- Yogesh. . Thanks for your answer, using your logic, How can I physically insert the records to the table?
- @JongLee. . . Use
INSERT INTO. .statement.
- @JongLee. . . If you are inserting record in existing table then start with 1 as id.
- Thank you so much.. Insert into.. and starting id with 1 worked. That was what I wanted. Once Side question. the actual table that work with right now has too many columns. Is there way to use "*" instead of specifying the actual column name for each.
- @JongLee. . . Of course you can use