Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

Copy records into same table changing only 2 fields

Application ViewThe screenshot is from the application. The circled are "sets" and the highlighted is a filter. I would like to copy all sets that begin with "6" and name them with a "4" in the beginning. The other change I would like to make is the "list" being pulled (this part should be easy enough as a "list" has an ID_no associated with it).

What would a script look like to copy these sets changing only the description and list fields?

Appreciate the help!
capture.png
capture.png
0
rwaterz
Asked:
rwaterz
  • 13
  • 13
1 Solution
 
chaauCommented:
From your screenshots it is not clear what the columns are. Can you run the SELECT query for the results presented in the application screen and advise what the table name and structure
0
 
rwaterzAuthor Commented:
I completely attached the wrong screenshot, apologies. Here is the correct screenshot:
Application screenshot
And attached is an excel sheet with sample records. Table name is t_au_set_master, not sure what you mean by "structure".
t-au-set-master.xlsx
0
 
chaauCommented:
I think this query should do the thing:
Insert into t_au_set_master
(description, au_ind, season, batch_no, start_dt, 
end_dt, cc_refund_ind, account_cont_ind, pmt_method, 
fund_no, batch_type, start_time, end_time, hold_until_dt, 
order_category, habo_flag, ticket_design, reprint_ind, form_or_boca, 
header_design, list_no, mailing_dt, mailing_purpose, signor, label, 
delivery, mos, ranking_method, perfs_in_pkgs, 
print_receipts, num_contig_seats)
SELECT STUFF(description, 1, 1, '4'), au_ind, season, batch_no, start_dt, 
end_dt, cc_refund_ind, account_cont_ind, pmt_method, 
fund_no, batch_type, start_time, end_time, hold_until_dt, 
order_category, habo_flag, ticket_design, reprint_ind, form_or_boca, 
header_design, list_no, mailing_dt, mailing_purpose, signor, label, 
delivery, mos, ranking_method, perfs_in_pkgs, 
print_receipts, num_contig_seats
FROM t_au_set_master WHERE description IS LIKE '6%'

Open in new window

Please backup you database before proceeding with the query to make sure your data is not stuffed up
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
rwaterzAuthor Commented:
Cannot insert the value NULL into column 'au_set_no', table 'impresario.dbo.T_AU_SET_MASTER'; column does not allow nulls. INSERT fails.

How do we generate ID's? I recall see a "get next ID" SP at some point, I don't know how to use that to our advantage though. Could we do a max + 1 based on t_au_set_master.au_set_no somehow?
0
 
chaauCommented:
If you have an SP it may be doing more than just get MAX(ID). Post its code here for us to figure out. BTW, if it an SP, or a UDF?
0
 
rwaterzAuthor Commented:
Found two, see attached. Both seem to be SP's.
AP-GET-NEXTID-function.txt
ap-get-nextid.txt
0
 
chaauCommented:
That's better. Now, please run this query and give us the results
Select * from [dbo].t_next_id

Open in new window


We need to identify what type to use for this table. The query should give us a hint
0
 
rwaterzAuthor Commented:
Good thought. See line 9 :)
t-next--id.xlsx
0
 
chaauCommented:
BTW, is there a function that calls the SP? you can locate the function by using a "Show Dependencies" option in SSMS.
if there is a UDF then the insert statement will be as easy as this:
Insert into t_au_set_master
(au_set_no, description, au_ind, season, batch_no, start_dt, 
end_dt, cc_refund_ind, account_cont_ind, pmt_method, 
fund_no, batch_type, start_time, end_time, hold_until_dt, 
order_category, habo_flag, ticket_design, reprint_ind, form_or_boca, 
header_design, list_no, mailing_dt, mailing_purpose, signor, label, 
delivery, mos, ranking_method, perfs_in_pkgs, 
print_receipts, num_contig_seats)
SELECT dbo.udf_get_next_id('AU'), STUFF(description, 1, 1, '4'), au_ind, season, batch_no, start_dt, 
end_dt, cc_refund_ind, account_cont_ind, pmt_method, 
fund_no, batch_type, start_time, end_time, hold_until_dt, 
order_category, habo_flag, ticket_design, reprint_ind, form_or_boca, 
header_design, list_no, mailing_dt, mailing_purpose, signor, label, 
delivery, mos, ranking_method, perfs_in_pkgs, 
print_receipts, num_contig_seats
FROM t_au_set_master WHERE description IS LIKE '6%'

Open in new window

0
 
rwaterzAuthor Commented:
Which SP are you referring to? ap_get_nextid or ap_get_nextid_function?

Both have dependencies, ap_get_nextid's dependencies are attached.

I tried running the script as is and received:

"Cannot find either column "dbo" or the user-defined function or aggregate "dbo.udf_get_next_id", or the name is ambiguous."
get-next-id-dependencies.PNG
0
 
chaauCommented:
I think it is WP_GET_NEXTID. Try this:
Insert into t_au_set_master
(au_set_no, description, au_ind, season, batch_no, start_dt, 
end_dt, cc_refund_ind, account_cont_ind, pmt_method, 
fund_no, batch_type, start_time, end_time, hold_until_dt, 
order_category, habo_flag, ticket_design, reprint_ind, form_or_boca, 
header_design, list_no, mailing_dt, mailing_purpose, signor, label, 
delivery, mos, ranking_method, perfs_in_pkgs, 
print_receipts, num_contig_seats)
SELECT dbo.WP_GET_NEXTID('AU'), STUFF(description, 1, 1, '4'), au_ind, season, batch_no, start_dt, 
end_dt, cc_refund_ind, account_cont_ind, pmt_method, 
fund_no, batch_type, start_time, end_time, hold_until_dt, 
order_category, habo_flag, ticket_design, reprint_ind, form_or_boca, 
header_design, list_no, mailing_dt, mailing_purpose, signor, label, 
delivery, mos, ranking_method, perfs_in_pkgs, 
print_receipts, num_contig_seats
FROM t_au_set_master WHERE description IS LIKE '6%'

Open in new window

If it does not work then check the dependencies of another SP.
Please post the syntax of WP_GET_NEXTID
0
 
rwaterzAuthor Commented:
No dice, same error. I also tried ap_get_nextid in the query you provided.

USE [impresario]
GO
/****** Object:  StoredProcedure [dbo].[WP_GET_NEXTID]    Script Date: 07/14/2014 21:21:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER Procedure [dbo].[WP_GET_NEXTID](
	 @type char(2) = NULL,
	 @increment int = 1)

AS

Set NoCount On

/********************************************************************************************
New Web Procedure 7/17/2007 CWR as a wrapper for ap_get_nextid

WP_GET_NEXTID 'PA'


********************************************************************************************/

Exec AP_GET_NEXTID @type = @type, @increment = @increment

Open in new window

wp-get-nextid.txt
0
 
chaauCommented:
there must be a UDF, otherwise we will have to use a CURSOR to insert the records. Check if there are objects that depend on WP_GET_NEXTID, or objects that depend on AP-GET-NEXTID-function. Also check the user functions in SSMS to see if there is one that looks like getNextID.
0
 
rwaterzAuthor Commented:
Here is a query my predecessor wrote that uses the AP_GET_NEXTID_FUNCTION SP:

DECLARE @errmsg varchar(255),
	@cur_Pmap as varchar(100), 
	@zmap int
	
	
Declare @cur_position int,
	@cIndex int,
	@x int
	
-- 99 = Wiess
-- 121 = Potiker
-- 5 = Forum


/*******************

Set Zone map here

********************/	
Set @zmap = 99

If OBJECT_ID('tempdb..#tmp') is not null drop table #tmp
Create Table #tmp (
	id int identity(1,1) ,
	pmap varchar(30)
	)
	
/*******************

Paste insert statements below from the excel file

********************/


Insert into #tmp (pmap) Values ('75-65-54-15-54-x')

	

-- Delete any duplicated pmaps from the above insert statements
DELETE
 FROM #tmp
 WHERE ID NOT IN
 (
 SELECT MAX(ID)
 FROM #tmp
 GROUP BY pmap)
	

--Delete any pmaps that already exist
Delete
from 
	#tmp
Where
	pmap in (
Select
	p.description
From
	T_PMAP p
Where
	p.zmap_no = @zmap)
	
	
	

	

Select
	@x = Isnull(MIN(id),0)
From
	#tmp
	
While @x <> 0
	Begin
	
Select
	@cur_Pmap = pmap
From 
	#tmp
Where
	id = @x




If LEN(@cur_pmap) > 30
	  Begin
		select @errmsg = 'Pmap description too long: ' + @cur_Pmap
		goto error_handler
	  End
	  
	  
Declare @new_pmap_no int 
 
EXEC @new_pmap_no = [dbo].AP_Get_NextID_Function 'PM'


INSERT 		
		[dbo].t_pmap
		(
		pmap_no,
		description,
		zmap_no,
		gl_hold_no,
		gl_realize_no,
		gl_benevolent_no,
		desig_code,
		price_category
		)
SELECT
		@new_pmap_no,
		@cur_Pmap,
		@zmap,
		'10-24200-000',
		Null,
		'10-24200-000',
		2,
		1
		
Select @new_pmap_no
		
IF 	@@error <> 0 
	BEGIN
				SELECT 	@errmsg = 'Error in TP_COPY_PMAP: Insert into T_PMAP failed'
				GOTO 	error_handler
	END



INSERT 		
		[dbo].t_subprice
		(
		zmap_no,
		pmap_no,
		zone_no,
		price,
		min_price, 
		not_on_sale_ind,
		editable_ind 
		)
SELECT 
	@zmap as zmap,
	@new_pmap_no as pmap_no,
	z.zone_no, 
	Case when a.Element = 'x' then 0 else Cast(a.Element as money) end as price,
	Case when a.Element = 'x' then 0 else Cast(a.Element as money) end as min_price,
	Case when a.Element = 'x' then 'Y' else 'N' end as not_on_sale_ind,
	'N' as editable_ind
FROM 
	FT_SPLIT_LIST(@cur_pmap, '-') a Join
	T_ZONE z on z.zmap_no = @zmap and z.rank = a.ElementId 
ORDER BY a.ElementID

IF 	@@error <> 0
	BEGIN
		SELECT 	@errmsg = 'Error in TP_COPY_PMAP: Insert into T_SUBPRICE failed'
		GOTO 	error_handler
	END


Select
	@x = isnull(MIN(id),0)
From
	#tmp
Where
	id > @x


End

Return

error_handler:
	RAISERROR(@errmsg, 11, 2)

Open in new window

0
 
rwaterzAuthor Commented:
Sounds like we need a cursor to complete this. How would you work that into the query?

Thanks for being patients by the way.
0
 
chaauCommented:
BTW, why don't you just create a UDF? Are you allowed to create functions in this database?
0
 
rwaterzAuthor Commented:
I am not familiar with how to use/create a UDF or how it would apply to what we are trying to accomplish. I try to use what is already in place as I am still quite the beginner.
0
 
chaauCommented:
USE [impresario]
GO
CREATE FUNCTION dbo.fn_Get_NextID(
   @type char(2),
   @increment int = 1)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
   DECLARE @NextID int;
   EXEC  AP_GET_NEXTID_function @type,  @NextID OUTPUT, @increment;
   RETURN(@NextID);
END;
GO
Insert into t_au_set_master
(au_set_no, description, au_ind, season, batch_no, start_dt, 
end_dt, cc_refund_ind, account_cont_ind, pmt_method, 
fund_no, batch_type, start_time, end_time, hold_until_dt, 
order_category, habo_flag, ticket_design, reprint_ind, form_or_boca, 
header_design, list_no, mailing_dt, mailing_purpose, signor, label, 
delivery, mos, ranking_method, perfs_in_pkgs, 
print_receipts, num_contig_seats)
SELECT dbo.fn_Get_NextID('AU'), STUFF(description, 1, 1, '4'), au_ind, season, batch_no, start_dt, 
end_dt, cc_refund_ind, account_cont_ind, pmt_method, 
fund_no, batch_type, start_time, end_time, hold_until_dt, 
order_category, habo_flag, ticket_design, reprint_ind, form_or_boca, 
header_design, list_no, mailing_dt, mailing_purpose, signor, label, 
delivery, mos, ranking_method, perfs_in_pkgs, 
print_receipts, num_contig_seats
FROM t_au_set_master WHERE description IS LIKE '6%'

Open in new window

If you are not confident in keeping the UDF in your database, you can drop it:
DROP FUNCTION dbo.fn_Get_NextID

Open in new window

0
 
rwaterzAuthor Commented:
Here is the error when running the above query:

An insufficient number of arguments were supplied for the procedure or function dbo.fn_Get_NextID.
0
 
chaauCommented:
Sorry, use
... SELECT dbo.fn_Get_NextID('AU', 1), ...

Open in new window

0
 
rwaterzAuthor Commented:
Now I get:

Only functions and some extended stored procedures can be executed from within a function.
0
 
chaauCommented:
OK, it is taking too long. Sorry, I should have created a cursor solution for you from the beginning. Never mind, just use this code, it will do the thing:
DECLARE @descr VARCHAR(100)
DECLARE @NextID int;
DECLARE db_cursor CURSOR FOR  
SELECT description 
FROM t_au_set_master
WHERE description IS LIKE '6%'

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @descr  

WHILE @@FETCH_STATUS = 0  
BEGIN  
   EXEC  AP_GET_NEXTID_function 'AU',  @NextID OUTPUT, 1;
Insert into t_au_set_master
(au_set_no, description, au_ind, season, batch_no, start_dt, 
end_dt, cc_refund_ind, account_cont_ind, pmt_method, 
fund_no, batch_type, start_time, end_time, hold_until_dt, 
order_category, habo_flag, ticket_design, reprint_ind, form_or_boca, 
header_design, list_no, mailing_dt, mailing_purpose, signor, label, 
delivery, mos, ranking_method, perfs_in_pkgs, 
print_receipts, num_contig_seats)
SELECT @NextID, STUFF(description, 1, 1, '4'), au_ind, season, batch_no, start_dt, 
end_dt, cc_refund_ind, account_cont_ind, pmt_method, 
fund_no, batch_type, start_time, end_time, hold_until_dt, 
order_category, habo_flag, ticket_design, reprint_ind, form_or_boca, 
header_design, list_no, mailing_dt, mailing_purpose, signor, label, 
delivery, mos, ranking_method, perfs_in_pkgs, 
print_receipts, num_contig_seats
FROM t_au_set_master WHERE description =@descr
   FETCH NEXT FROM db_cursor INTO @descr
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

Open in new window

0
 
rwaterzAuthor Commented:
No worries, I'm just glad you haven't given up. Error received:

Violation of PRIMARY KEY constraint 'PK_T_AU_SET_MASTER_1__22'. Cannot insert duplicate key in object 'dbo.T_AU_SET_MASTER'.
0
 
chaauCommented:
Questions:
1. Do you have any entries that are already in the table that are beginning with 4 that match those that begin with 6, ie there is a record for 6WED3, do you have 4WED3?
2. Please check what columns comprise PK_T_AU_SET_MASTER_1__22?
3. Run SELECT MAX(au_set_no) FROM t_au_set_master; SELECT next_id from t_next_id where type='AU'. Show us the results
4. Run SELECT description from t_au_set_master where description LIKE '6%' or description LIKE '4%'. Show us the results
0
 
rwaterzAuthor Commented:
That error was on me, I had just changed some of the names, plus there was another filter I needed to add to the where clause. I renamed some sets and they copied over.

By the way, SSMS didn't like "IS LIKE" very much, I had to remove the "IS" under the where clause-description.

Thanks so much for your help!
0
 
chaauCommented:
Yes, IS LIKE was just a typo. I have noticed it in the last post
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 13
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now