Select = + ', ', '') + = case type_name(c.system_type_id) I created the following procedure: if object_id('tool.create_insert', 'P') is nullĮxec('create procedure tool.create_insert as') Īlter procedure varchar(200) = varchar(max) = int = varchar(max) output)ĭeclare table(description varchar(max)) ''' FROM ' + + ' WHERE ' + + ' = ' + AS VARCHAR)ĭECLARE cur CURSOR FOR SELECT Sql FROM cur ''')' + CASE WHEN = 1 THEN CHAR(13) + + ' OFF' ELSE '' END + WHERE object_id = OBJECT_ID('dbo.' + XML PATH('')ĬASE WHEN NOT EXISTS(SELECT * FROM WHERE FieldName = name) THENĬASE WHEN EXISTS(SELECT * FROM WHERE SysId = system_type_id) THEN '' ELSE ''''''''' + ' END +ĬASE WHEN EXISTS(SELECT * FROM WHERE SysId = system_type_id) THEN '' ELSE ' + ''''''''' END + SELECT CASE WHEN NOT EXISTS(SELECT * FROM WHERE FieldName = name) THEN ', ' + name ELSE '' END INSERT INTO SELECT N.value('.', 'NVARCHAR(128)') FROM AS T(N)ĭECLARE NVARCHAR(128) = 'SET IDENTITY_INSERT ' + AS NVARCHAR(4000) = 'SELECT ''' + CASE WHEN = 1 THEN + ' ON' + CHAR(13) ELSE '' END + 'INSERT INTO ' + + ' (' resultant INSERT statement, but you may need to add more (from sys.types)ĭECLARE INT = (SELECT COUNT(*) FROM INT = 1 This will ensure INT and BIT types are not surrounded with quotes in the INSERT INTO (Name, IdField, IdInsert, Excluded) VALUES ('MyTable2', 'Id', 1, 'Created,Modified') INSERT INTO (Name, IdField, IdInsert, Excluded) VALUES ('MyTable1', 'Id', 0, 'Created,Modified')
Excluded: Any fields you do not wish to include in the INSERT statement
IdInsert: If the primary key field is to be included in the INSERT statement IdField: The field on which to filter the dataset Add any tables you wish to generate INSERT statements for here. SET NOCOUNT ONĭECLARE TABLE (Name NVARCHAR(128), IdField NVARCHAR(128), IdInsert BIT, Excluded NVARCHAR(128))
You can then just run this query in a query window and it will print the INSERT statements you require. To use the code, please modify according to the in line comments which explain its usage. This is a more versatile solution (that can do a little more than the question asks), and can be used in a query window without having to create a new stored proc - useful in production databases for instance where you don't have write access. SELECT ''INSERT INTO Insert_Scripts FROM + AS AS TEXT I)And created the sp sp_generate_insertscriptsġ- Just put the table name for which you want insert scriptĢ- Filter condition if you want specific results -Final Procedure To generate Script. H) ISNULL is used because if any row has NULL have used below data in quote, )Į) ''INSERT Insert_Scripts FROM + Finally Executed the above query EXECUTE(TEXT) A)Syntax for inserting data in table is as below Insert into table(col1,col2,col3,col4,col5)
0 kommentar(er)
