Is there a way to generate an insert stored procedure
-
Hi all,
I find it tedious and time consuming to take the insert query sqlServer2008 generously makes for us like here:
INSERT INTO [Action_Amusements_Winhost].[dbo].[Location]([Location_Name]
,[Location_Code]
,[Account_Name]
,[Account_Code]
,[Address1]
,[Address2]
,[City]
,[State_Province_Territory]
,[Country]
,[Postal_Code]
,[Contact1_FirstName]
,[Contact1_LastName]
,[Contact1_Role]
,[Contact1_Email]
,[Contact1_Cell]
,[Contact2_FirstName]
,[Contact2_LastName]
,[Contact2_Role]
,[Contact2_Email]
,[Contact2_Cell]
,[Owner_FirstName]
,[Owner_LastName]
,[Owner_Cell]
,[Owner_Email]
,[Location_Office_Phone]
,[Location_Bar_Phone]
,[Email_for_Sales_Report]
,[Notes1]
,[Notes2]
,[Comments]
,[Location_Is_Active]
,[Tax_Percente_State]
,[Tax_Percente_County]
,[Tax_Percente_Other]
,[Split_Percent]
,[Location_Modify_Date]
,[Modifiers_Login]
,[Data_Is_Dummy]
,[Flagged_for_Deletion])
VALUES
(<Location_Name, varchar(50),>
,<Location_Code, varchar(25),>
,<Account_Name, varchar(50),>
,<Account_Code, varchar(25),>
,<Address1, varchar(50),>
,<Address2, varchar(50),>
,<City, varchar(50),>
,<State_Province_Territory, varchar(50),>
,<Country, varchar(50),>
,<Postal_Code, varchar(50),>
,<Contact1_FirstName, varchar(50),>
,<Contact1_LastName, varchar(50),>
,<Contact1_Role, varchar(50),>
,<Contact1_Email, varchar(50),>
,<Contact1_Cell, varchar(50),>
,<Contact2_FirstName, varchar(50),>
,<Contact2_LastName, varchar(50),>
,<Contact2_Role, varchar(50),>
,<Contact2_Email, varchar(50),>
,<Contact2_Cell, varchar(50),>
,<Owner_FirstName, varchar(50),>
,<Owner_LastName, varchar(50),>
,<Owner_Cell, varchar(50),>
,<Owner_Email, varchar(50),>
,<Location_Office_Phone, varchar(50),>
,<Location_Bar_Phone, varchar(50),>
,<Email_for_Sales_Report, varchar(50),>
,<Notes1, varchar(250),>
,<Notes2, varchar(250),>
,<Comments, varchar(250),>
,<Location_Is_Active, bit,>
,<Tax_Percente_State, varchar(10),>
,<Tax_Percente_County, varchar(10),>
,<Tax_Percente_Other, varchar(10),>
,<Split_Percent, varchar(10),>
,<Location_Modify_Date, datetime,>
,<Modifiers_Login, varchar(50),>
,<Data_Is_Dummy, bit,>
,<Flagged_for_Deletion, bit,>)
and turning it into a ready to go stored procedure like here:
CREATE PROCEDURE [dbo].[sp_Insert_Location]@Location_Name varchar(50)
,@Location_Code varchar(25)
,@Account_Name varchar(50)
,@Account_Code varchar(25)
,@Address1 varchar(50)
,@Address2 varchar(50)
,@City varchar(50)
,@State_Province_Territory varchar(50)
,@Country varchar(50)
,@Postal_Code varchar(50)
,@Contact1_FirstName varchar(50)
,@Contact1_LastName varchar(50)
,@Contact1_Role varchar(50)
,@Contact1_Email varchar(50)
,@Contact1_Cell varchar(50)
,@Contact2_FirstName varchar(50)
,@Contact2_LastName varchar(50)
,@Contact2_Role varchar(50)
,@Contact2_Email varchar(50)
,@Contact2_Cell varchar(50)
,@Owner_FirstName varchar(50)
,@Owner_LastName varchar(50)
,@Owner_Cell varchar(50)
,@Owner_Email varchar(50)
,@Location_Office_Phone varchar(50)
,@Location_Bar_Phone varchar(50)
,@Email_for_Sales_Report varchar(50)
,@Notes1 varchar(250)
,@Notes2 varchar(250)
,@Comments varchar(250)
,@Location_Is_Active bit
,@Tax_Percente_State varchar(10)
,@Tax_Percente_County varchar(10)
,@Tax_Percente_Other varchar(10)
,@Split_Percent varchar(10)
,@Location_Modify_Date datetime
,@Modifiers_Login varchar(50)
,@Data_Is_Dummy bit
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [Action_Amusements_Winhost].[dbo].[Location]
([Location_Name]
,[Location_Code]
,[Account_Name]
,[Account_Code]
,[Address1]
,[Address2]
,[City]
,[State_Province_Territory]
,[Country]
,[Postal_Code]
,[Contact1_FirstName]
,[Contact1_LastName]
,[Contact1_Role]
,[Contact1_Email]
,[Contact1_Cell]
,[Contact2_FirstName]
,[Contact2_LastName]
,[Contact2_Role]
,[Contact2_Email]
,[Contact2_Cell]
,[Owner_FirstName]
,[Owner_LastName]
,[Owner_Cell]
,[Owner_Email]
,[Location_Office_Phone]
,[Location_Bar_Phone]
,[Email_for_Sales_Report]
,[Notes1]
,[Notes2]
,[Comments]
,[Location_Is_Active]
,[Tax_Percente_State]
,[Tax_Percente_County]
,[Tax_Percente_Other]
,[Split_Percent]
,[Location_Modify_Date]
,[Modifiers_Login]
,[Data_Is_Dummy])
VALUES
(@Location_Name
,@Location_Code
,@Account_Name
,@Account_Code
,@Address1
,@Address2
,@City
,@State_Province_Territory
,@Country
,@Postal_Code
,@Contact1_FirstName
,@Contact1_LastName
,@Contact1_Role
,@Contact1_Email
,@Contact1_Cell
,@Contact2_FirstName
,@Contact2_LastName
,@Contact2_Role
,@Contact2_Email
,@Contact2_Cell
,@Owner_FirstName
,@Owner_LastName
,@Owner_Cell
,@Owner_Email
,@Location_Office_Phone
,@Location_Bar_Phone
,@Email_for_Sales_Report
,@Notes1
,@Notes2
,@Comments
,@Location_Is_Active
,@Tax_Percente_State
,@Tax_Percente_County
,@Tax_Percente_Other
,@Split_Percent
,@Location_Modify_Date
,@Modifiers_Login
,@Data_Is_Dummy)
END
Is there a way to get this generated , I need this for Entity framework mapping.Seems there
has to a better "way than find" and "replace and cut n paste". I have about 30 more tables and/views I need to do this with.
Thanks in Advance,
Adam
Thanks In advance.
-
I like to write code generators from the catalog (view is INFORMATION_SCHEMA.COLUMNS)
(obviously for the sake of discussion, we hope a table called ABC actually exists)
DECLARE @TABLE_NAME VARCHAR(100)
SET @TABLE_NAME = 'ABC'
SELECT
CASE
WHEN SEQ = 2 OR SEQ = 4
THEN
CASE
WHEN ROW <> 1 THEN ',' + COLUMN_REF
ELSE COLUMN_REF
END
ELSE COLUMN_REF
END PROGRAM_TEXT
FROM
(
SELECT 1 SEQ, '1' ROW, 'INSERT INTO ' + @TABLE_NAME + ' (' COLUMN_REF
UNION ALL
SELECT 2 SEQ, ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) ROW, '[' + COLUMN_NAME + ']' COLUMN_REF
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME
UNION ALL
SELECT 4 SEQ, ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) ROW, '@' + COLUMN_NAME COLUMN_REF
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME
UNION ALL
SELECT 3 SEQ, 1 ROW, ') VALUES (' COLUMN_REF
UNION ALL
SELECT 5 SEQ, 1 ROW, ')' COLUMN_REF
) TAB1
ORDER BY SEQ, ROW
-
and another way:
download and install the SSMS add-on Called SSMS Tools Pack:
that adds a CRUD generator to the right click of any table, which is exactly what you were after:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell's answer is probably more practical. Also for my post, I forgot the procedure declarations!
DECLARE @TABLE_NAME VARCHAR(100)
SET @TABLE_NAME = 'ABC'
SELECT
CASE
WHEN SEQ = 2 OR SEQ = 7 OR SEQ = 9
THEN
CASE
WHEN ROW <> 1 THEN ',' + COLUMN_REF
ELSE COLUMN_REF
END
ELSE COLUMN_REF
END PROGRAM_TEXT
FROM
(
SELECT 1 SEQ, 1 ROW, 'CREATE PROCEDURE [dbo].[SP_Insert_' + @TABLE_NAME + ']' AS COLUMN_REF
UNION ALL
SELECT 2 SEQ, ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) ROW, COLUMN_NAME + ' ' +
DATA_TYPE +
CASE
WHEN DATA_TYPE = 'varchar' THEN '(' + CONVERT(VARCHAR(5),CHARACTER_MAXIMUM_LENGTH) + ')'
ELSE ''
END
COLUMN_REF
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME
UNION ALL
SELECT 3 SEQ, 1 ROW, 'AS' COLUMN_REF
UNION ALL
SELECT 4 SEQ, 1 ROW, 'BEGIN' COLUMN_REF
UNION ALL
SELECT 5 SEQ, 1 ROW, 'SET NOCOUNT ON;' COLUMN_REF
UNION ALL
SELECT 6 SEQ, 1 ROW, 'INSERT INTO ' + @TABLE_NAME + ' (' COLUMN_REF
UNION ALL
SELECT 7 SEQ, ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) ROW, '[' + COLUMN_NAME + ']' COLUMN_REF
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME
UNION ALL
SELECT 8 SEQ, 1 ROW, ') VALUES (' COLUMN_REF
UNION ALL
SELECT 9 SEQ, ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) ROW, '@' + COLUMN_NAME COLUMN_REF
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME
UNION ALL
SELECT 10 SEQ, 1 ROW, ')' COLUMN_REF
UNION ALL
SELECT 11 SEQ, 1 ROW, 'END'
) TAB1
ORDER BY SEQ, ROW
-
You really should use the sys.* views to generate the code, rather than using the INFORMATION_SCHEMA views, because, according to Books Online:
"
Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.
"
And when you gen code, you should include the schema on tables and other objects.
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".
-
SSCrazy Eights. Wow, I knew there had to be a solution, and Patrick I am still gonna run your code to check it out!:-D:cool:
-
Lowell (6/19/2012)
and another way:download and install the SSMS add-on Called SSMS Tools Pack:
that adds a CRUD generator to the right click of any table, which is exactly what you were after:
Hi Lowell,
Would you mind explaining when exactly would you go for CRUD generator? Please provide couple of scenario where you would use this. I know the above scenario is about inserting some rows, but am not very clear about it. Please advise.
Thanks
SueTons
-
A Crud generator simply creates some procedures for you (for Create Read Update Delete:CRUD) based on a template and might assume one row inserts or updates for example.
In a perfect database, NOONE has direct access to the underlying tables, The you would only be able to call procedures they have been granted execute on.
The procedures, in theory, Should perform better that ad hoc statements due to the saved execution plan.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply