Take your career to the next level with convenient certification training. Start your 7-day Free Trial
Experts Exchange Solution brought to you by
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
CREATE Proc dbo.ScriptInsertStatements(
@WhereClause varchar(400) = Null
DECLARE @sql varchar(Max)
DECLARE @sqlColumns varchar(Max)
DECLARE @SqlColumnValues varchar(Max)
SELECT @sqlColumns = Coalesce(@SqlColumns + ',','') + Column_Name from Information_Schema.Columns where Table_name = @TableName
SELECT @SqlColumnValues = Coalesce(@SqlColumnValues + '+ '','' + ','') +
WHEN Data_Type in ('varchar','nvarchar', 'char', 'nchar', 'datetime' ) THEN 'QuoteName(' + Column_Name + ', '''''''')'
ELSE 'Cast(' + Column_Name + ' as varchar(MAX))'
Information_Schema.Columns where Table_name = @TableName
SELECT @sql = 'Select ''Insert Into ' + @TableName + ' (' + @SqlColumns + ') Values ('' + ' + @SqlColumnValues +' + '')'' FROM ' + @TableName + IsNull(' WHERE ' + @WhereClause, '')
Open in new window
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.