Hi,
I have 10 tables each with 2 columns (Id- identity, Name_xxxx)
The name of the second column is different from a table to other.
I want to write a stored procedure to insert a record into the table, but with 2 parameters in input (the name of the table and the value)
exec sp_InsertIntoTable @TableName='Table1',Value='a1'
exec sp_InsertIntoTable @TableName='Table2',Value='CVB'
Inside te sp I will have something like this:
insert into @Table values(@value)
But I don't know how to declare the @Table
Thanks in advance
May 14, 2020 at 8:25 pm
For something like that, you will need to use dynamic SQL.
Be careful using it though as if it is not designed carefully and with proper safeguards, it is easy to get some SQL injection happening from it.
Another thing to not do is start your stored procedure name with "SP_". It is a best practice and there are other good reasons not to (such as how the optimizer works with stored procedures that start with sp_).
My question though is since you are already providing the table name and the value being inserted, isn't it just as easy to write:
INSERT INTO Table1
VALUES('a1');
INSERT INTO Table2
VALUES('CVB');
Maybe I am missing something with the point of the stored procedure, but to me it feels like an INSERT statement would work just as easily...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 15, 2020 at 1:30 pm
It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity value, which is no longer needed when you can use a SEQUENCE instead.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 15, 2020 at 8:10 pm
It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity value, which is no longer needed when you can use a SEQUENCE instead.
I have to disagree with this. If the OP's tables are only related by a coincidence (whereby they happen to share the same structure), then a sequence would only serve to fragment the primary keys. Or are you suggesting to check each table prior to insert whether the key will fit? If so it should be noted this is an anti-pattern compared to the framework method of using the IDENTITY property of the tables(s). If these tables are in any way related then it's been data modeled improperly and could (and should) be improved.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
My opinion, if it is a sequence or an identity really depends on a lot more than we can accurately guess from what was posted. It might be that each of the tables are completely unrelated, or it might be that they relate to each other. Or the ID column may be completely unused in which case it may be better to just drop the column. We do not have enough information.
I am a little curious if AntonyO got this figured out or not. Quick bit of code to make this work:
CREATE OR ALTER PROCEDURE [dbo].[sp_InsertIntoTable]
@TableName VARCHAR(255)
, @value VARCHAR(MAX)
AS
BEGIN
DECLARE @sql VARCHAR(MAX);
SELECT
@sql = 'INSERT INTO ' + @TableName + ' VALUES (' + @Value + ')';
EXEC sp_executesql
@sql;
END;
GO
Although, I still caution anyone reading this to avoid using "sp_" at the start of a stored procedure.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 16, 2020 at 4:57 am
ScottPletcher wrote:It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity value, which is no longer needed when you can use a SEQUENCE instead.
I have to disagree with this. If the OP's tables are only related by a coincidence (whereby they happen to share the same structure), then a sequence would only serve to fragment the primary keys. Or are you suggesting to check each table prior to insert whether the key will fit? If so it should be noted this is an anti-pattern compared to the framework method of using the IDENTITY property of the tables(s). If these tables are in any way related then it's been data modeled improperly and could (and should) be improved.
I'm not following you. A SEQUENCE returns sequential values as well, what would cause fragmenting? Gaps, yes, but not fragmentation, since all future values are still all larger than existing values.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 16, 2020 at 4:58 am
My opinion, if it is a sequence or an identity really depends on a lot more than we can accurately guess from what was posted. It might be that each of the tables are completely unrelated, or it might be that they relate to each other. Or the ID column may be completely unused in which case it may be better to just drop the column. We do not have enough information.
I am a little curious if AntonyO got this figured out or not. Quick bit of code to make this work:
CREATE OR ALTER PROCEDURE [dbo].[sp_InsertIntoTable]
@TableName VARCHAR(255)
, @value VARCHAR(MAX)
AS
BEGIN
DECLARE @sql VARCHAR(MAX);
SELECT
@sql = 'INSERT INTO ' + @TableName + ' VALUES (' + @Value + ')';
EXEC sp_executesql
@sql;
END;
GOAlthough, I still caution anyone reading this to avoid using "sp_" at the start of a stored procedure.
I was basing it on that being the only columns in the table. Based on that -- if that's really true -- it seems the table is being used only to gen an identity value. And you don't need a table any more just to generate sequential numbers.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 16, 2020 at 8:50 am
Steve Collins wrote:ScottPletcher wrote:It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity value, which is no longer needed when you can use a SEQUENCE instead.
I have to disagree with this. If the OP's tables are only related by a coincidence (whereby they happen to share the same structure), then a sequence would only serve to fragment the primary keys. Or are you suggesting to check each table prior to insert whether the key will fit? If so it should be noted this is an anti-pattern compared to the framework method of using the IDENTITY property of the tables(s). If these tables are in any way related then it's been data modeled improperly and could (and should) be improved.
I'm not following you. A SEQUENCE returns sequential values as well, what would cause fragmenting? Gaps, yes, but not fragmentation, since all future values are still all larger than existing values.
True true, you're correct it would be strictly increasing. It's still sub optimal although perhaps less silly than using a guid or hash code.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 18, 2020 at 2:44 pm
ScottPletcher wrote:Steve Collins wrote:ScottPletcher wrote:It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity value, which is no longer needed when you can use a SEQUENCE instead.
I have to disagree with this. If the OP's tables are only related by a coincidence (whereby they happen to share the same structure), then a sequence would only serve to fragment the primary keys. Or are you suggesting to check each table prior to insert whether the key will fit? If so it should be noted this is an anti-pattern compared to the framework method of using the IDENTITY property of the tables(s). If these tables are in any way related then it's been data modeled improperly and could (and should) be improved.
I'm not following you. A SEQUENCE returns sequential values as well, what would cause fragmenting? Gaps, yes, but not fragmentation, since all future values are still all larger than existing values.
True true, you're correct it would be strictly increasing. It's still sub optimal although perhaps less silly than using a guid or hash code.
Again, why is it "suboptimal"? As long as the values are always ascending, what difference does it make to SQL, or to us, if there are gaps in the numbers? There can be gaps in identity values too, but they don't lessen the contiguous nature of that data either.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 18, 2020 at 3:18 pm
Again, why is it "suboptimal"? As long as the values are always ascending, what difference does it make to SQL, or to us, if there are gaps in the numbers? There can be gaps in identity values too, but they don't lessen the contiguous nature of that data either.
Not sure what you mean by "again" did something already happen? To review, either the tables are related by a coincidence of design or they're improperly modeled. If they're related by a coincidence of design then one consequence of using a SEQUENCE is to reduce the number of records it's possible to fit in each table by a factor that depends (for each table) on 9 external independent situations.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 29, 2020 at 5:49 am
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply