Replace text within a Query
up vote
0
down vote
favorite
I have a very long list of data that i need to insert into a table. I have a pre-formatted list of queries that does it, in which i go through each query and replace 'X' with part of an item number. Is there any way that i can save myself typing or copying the text dozens of times over by just replacing 'X' with the text I need?
My set of queries is much longer than this but looks like
Insert into Inventory_Ingredients
Values ('X300', 1001, '30label', 1, '0', 0)
Insert into Inventory_Ingredients
Values ('X300', 1001, '30b', 1, '0', 0)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'Shrink30', 1, '0', .50/100)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'recipeX', 1, '0', 1.00/100)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'wiznic100', 0*30/100, '0', 2.00/100)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'vg', 30-(select sum(inventory_ingredients.quantity)
from inventory_ingredients
where itemnum='recipeX'
group by itemnum)-(0*30/100), '0', 2.00/100)
sql
sql-server tsql asked Nov 11 at 0:30
Yofi
154
add a comment |
up vote
0
down vote
favorite
I have a very long list of data that i need to insert into a table. I have a pre-formatted list of queries that does it, in which i go through each query and replace 'X' with part of an item number. Is there any way that i can save myself typing or copying the text dozens of times over by just replacing 'X' with the text I need?
My set of queries is much longer than this but looks like
Insert into Inventory_Ingredients
Values ('X300', 1001, '30label', 1, '0', 0)
Insert into Inventory_Ingredients
Values ('X300', 1001, '30b', 1, '0', 0)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'Shrink30', 1, '0', .50/100)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'recipeX', 1, '0', 1.00/100)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'wiznic100', 0*30/100, '0', 2.00/100)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'vg', 30-(select sum(inventory_ingredients.quantity)
from inventory_ingredients
where itemnum='recipeX'
group by itemnum)-(0*30/100), '0', 2.00/100)
sql
sql-server tsql asked Nov 11 at 0:30
Yofi
154
1
how about using a variable instead of X ?
– iSR5
Nov 11 at 1:06
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a very long list of data that i need to insert into a table. I have a pre-formatted list of queries that does it, in which i go through each query and replace 'X' with part of an item number. Is there any way that i can save myself typing or copying the text dozens of times over by just replacing 'X' with the text I need?
My set of queries is much longer than this but looks like
Insert into Inventory_Ingredients
Values ('X300', 1001, '30label', 1, '0', 0)
Insert into Inventory_Ingredients
Values ('X300', 1001, '30b', 1, '0', 0)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'Shrink30', 1, '0', .50/100)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'recipeX', 1, '0', 1.00/100)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'wiznic100', 0*30/100, '0', 2.00/100)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'vg', 30-(select sum(inventory_ingredients.quantity)
from inventory_ingredients
where itemnum='recipeX'
group by itemnum)-(0*30/100), '0', 2.00/100)
sql
sql-server tsql asked Nov 11 at 0:30
Yofi
154
I have a very long list of data that i need to insert into a table. I have a pre-formatted list of queries that does it, in which i go through each query and replace 'X' with part of an item number. Is there any way that i can save myself typing or copying the text dozens of times over by just replacing 'X' with the text I need?
My set of queries is much longer than this but looks like
Insert into Inventory_Ingredients
Values ('X300', 1001, '30label', 1, '0', 0)
Insert into Inventory_Ingredients
Values ('X300', 1001, '30b', 1, '0', 0)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'Shrink30', 1, '0', .50/100)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'recipeX', 1, '0', 1.00/100)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'wiznic100', 0*30/100, '0', 2.00/100)
Insert into Inventory_Ingredients
Values ('X300', 1001, 'vg', 30-(select sum(inventory_ingredients.quantity)
from inventory_ingredients
where itemnum='recipeX'
group by itemnum)-(0*30/100), '0', 2.00/100)
sql
sql-server tsql sql
sql-server tsql asked Nov 11 at 0:30
Yofi
154
asked Nov 11 at 0:30
Yofi
154
asked Nov 11 at 0:30
Yofi
154
asked Nov 11 at 0:30
Yofi
154
asked Nov 11 at 0:30
Yofi
154
154
1
how about using a variable instead of X ?
– iSR5
Nov 11 at 1:06
add a comment |
1
how about using a variable instead of X ?
– iSR5
Nov 11 at 1:06
1
1
how about using a variable instead of X ?
– iSR5
Nov 11 at 1:06
how about using a variable instead of X ?
– iSR5
Nov 11 at 1:06
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
Any reason dynamic SQL wouldn't work for what you're doing? Very useful in situations like this. Or if the X is just for values being inserted rather than for changing the query structure, variables would likely do the trick. Even so, I bet one of the two options will do it for you. Here's a good site for dynamic SQL help... https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/
answered Nov 11 at 2:27
Utrolig
242211
None at all, I just am very new and was previously unaware of its existence. so i can just replace all my 'X' with @X and DECLARE @X varchar(50) SET @X = 'insert_data_here'?
– Yofi
Nov 11 at 15:19
Msg 547, Level 16, State 0, Line 5The INSERT statement conflicted with the FOREIGN KEY constraint "fkInventory_Ingredients". The conflict occurred in database "cresql", table "dbo.Inventory". The statement has been terminated.
– Yofi
Nov 11 at 16:03
When I take the'@X300'out of ticks, i getMust declare the scalar variable "@X300".
– Yofi
Nov 11 at 16:06
Presumably because it thinks that@X300is the variable here. How do specify that only@Xis the variable when it must be only part of a string? Maybe something withSUBSTRING
– Yofi
Nov 11 at 16:22
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
Any reason dynamic SQL wouldn't work for what you're doing? Very useful in situations like this. Or if the X is just for values being inserted rather than for changing the query structure, variables would likely do the trick. Even so, I bet one of the two options will do it for you. Here's a good site for dynamic SQL help... https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/
answered Nov 11 at 2:27
Utrolig
242211
None at all, I just am very new and was previously unaware of its existence. so i can just replace all my 'X' with @X and DECLARE @X varchar(50) SET @X = 'insert_data_here'?
– Yofi
Nov 11 at 15:19
Msg 547, Level 16, State 0, Line 5The INSERT statement conflicted with the FOREIGN KEY constraint "fkInventory_Ingredients". The conflict occurred in database "cresql", table "dbo.Inventory". The statement has been terminated.
– Yofi
Nov 11 at 16:03
When I take the'@X300'out of ticks, i getMust declare the scalar variable "@X300".
– Yofi
Nov 11 at 16:06
Presumably because it thinks that@X300is the variable here. How do specify that only@Xis the variable when it must be only part of a string? Maybe something withSUBSTRING
– Yofi
Nov 11 at 16:22
add a comment |
up vote
0
down vote
Any reason dynamic SQL wouldn't work for what you're doing? Very useful in situations like this. Or if the X is just for values being inserted rather than for changing the query structure, variables would likely do the trick. Even so, I bet one of the two options will do it for you. Here's a good site for dynamic SQL help... https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/
answered Nov 11 at 2:27
Utrolig
242211
None at all, I just am very new and was previously unaware of its existence. so i can just replace all my 'X' with @X and DECLARE @X varchar(50) SET @X = 'insert_data_here'?
– Yofi
Nov 11 at 15:19
Msg 547, Level 16, State 0, Line 5The INSERT statement conflicted with the FOREIGN KEY constraint "fkInventory_Ingredients". The conflict occurred in database "cresql", table "dbo.Inventory". The statement has been terminated.
– Yofi
Nov 11 at 16:03
When I take the'@X300'out of ticks, i getMust declare the scalar variable "@X300".
– Yofi
Nov 11 at 16:06
Presumably because it thinks that@X300is the variable here. How do specify that only@Xis the variable when it must be only part of a string? Maybe something withSUBSTRING
– Yofi
Nov 11 at 16:22
add a comment |
up vote
0
down vote
up vote
0
down vote
Any reason dynamic SQL wouldn't work for what you're doing? Very useful in situations like this. Or if the X is just for values being inserted rather than for changing the query structure, variables would likely do the trick. Even so, I bet one of the two options will do it for you. Here's a good site for dynamic SQL help... https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/
answered Nov 11 at 2:27
Utrolig
242211
Any reason dynamic SQL wouldn't work for what you're doing? Very useful in situations like this. Or if the X is just for values being inserted rather than for changing the query structure, variables would likely do the trick. Even so, I bet one of the two options will do it for you. Here's a good site for dynamic SQL help... https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/
answered Nov 11 at 2:27
Utrolig
242211
answered Nov 11 at 2:27
Utrolig
242211
answered Nov 11 at 2:27
Utrolig
242211
answered Nov 11 at 2:27
Utrolig
242211
242211
None at all, I just am very new and was previously unaware of its existence. so i can just replace all my 'X' with @X and DECLARE @X varchar(50) SET @X = 'insert_data_here'?
– Yofi
Nov 11 at 15:19
Msg 547, Level 16, State 0, Line 5The INSERT statement conflicted with the FOREIGN KEY constraint "fkInventory_Ingredients". The conflict occurred in database "cresql", table "dbo.Inventory". The statement has been terminated.
– Yofi
Nov 11 at 16:03
When I take the'@X300'out of ticks, i getMust declare the scalar variable "@X300".
– Yofi
Nov 11 at 16:06
Presumably because it thinks that@X300is the variable here. How do specify that only@Xis the variable when it must be only part of a string? Maybe something withSUBSTRING
– Yofi
Nov 11 at 16:22
add a comment |
None at all, I just am very new and was previously unaware of its existence. so i can just replace all my 'X' with @X and DECLARE @X varchar(50) SET @X = 'insert_data_here'?
– Yofi
Nov 11 at 15:19
Msg 547, Level 16, State 0, Line 5The INSERT statement conflicted with the FOREIGN KEY constraint "fkInventory_Ingredients". The conflict occurred in database "cresql", table "dbo.Inventory". The statement has been terminated.
– Yofi
Nov 11 at 16:03
When I take the'@X300'out of ticks, i getMust declare the scalar variable "@X300".
– Yofi
Nov 11 at 16:06
Presumably because it thinks that@X300is the variable here. How do specify that only@Xis the variable when it must be only part of a string? Maybe something withSUBSTRING
– Yofi
Nov 11 at 16:22
None at all, I just am very new and was previously unaware of its existence. so i can just replace all my 'X' with @X and DECLARE @X varchar(50) SET @X = 'insert_data_here'?
– Yofi
Nov 11 at 15:19
None at all, I just am very new and was previously unaware of its existence. so i can just replace all my 'X' with @X and DECLARE @X varchar(50) SET @X = 'insert_data_here'?
– Yofi
Nov 11 at 15:19
Msg 547, Level 16, State 0, Line 5
The INSERT statement conflicted with the FOREIGN KEY constraint "fkInventory_Ingredients". The conflict occurred in database "cresql", table "dbo.Inventory". The statement has been terminated.– Yofi
Nov 11 at 16:03
Msg 547, Level 16, State 0, Line 5
The INSERT statement conflicted with the FOREIGN KEY constraint "fkInventory_Ingredients". The conflict occurred in database "cresql", table "dbo.Inventory". The statement has been terminated.– Yofi
Nov 11 at 16:03
When I take the
'@X300' out of ticks, i get Must declare the scalar variable "@X300". – Yofi
Nov 11 at 16:06
When I take the
'@X300' out of ticks, i get Must declare the scalar variable "@X300". – Yofi
Nov 11 at 16:06
Presumably because it thinks that
@X300 is the variable here. How do specify that only @X is the variable when it must be only part of a string? Maybe something with SUBSTRING– Yofi
Nov 11 at 16:22
Presumably because it thinks that
@X300 is the variable here. How do specify that only @X is the variable when it must be only part of a string? Maybe something with SUBSTRING– Yofi
Nov 11 at 16:22
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53244759%2freplace-text-within-a-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
how about using a variable instead of X ?
– iSR5
Nov 11 at 1:06