November 12, 2013 at 3:32 am
Dear All
Want to create insert statement fron the view. Folloing steps followed SSMS -> Task - > generate script
Selected the view. Seclected advace option as Type of data to script as Data
But it does not create insert statements
Regards
November 12, 2013 at 3:49 am
Why do you want to create INSERT script from a VIEW? You cannot insert data into all views.
INSERTS are generally done on tables and you should creating scripts out of tables and not views.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 12, 2013 at 3:50 am
Where are you finding the Task option? And are you sure you want to create INSERT scripts for a view and not a table? I'm not saying you shouldn't - I'm just checking that's what you really want.
John
November 12, 2013 at 4:08 am
I dont want to insert to view. I have table and want to insert data retrived from this view.
November 12, 2013 at 4:13 am
INSERT INTO MyTable (<column list>)
SELECT <column list>
FROM MyView
WHERE <whatever>
It's probably easier to write it manually than to attempt to script it. If the column list is long then you can script the view as SELECT, and/or script the table as INSERT, and take it from there.
John
Edit - added parentheses to first line
November 12, 2013 at 4:16 am
I don't think you need to generate an INSERT script from the VIEW for that
Try something like this
INSERTYourTableName( Column1, Column2, ... )
SELECTColumn1, Column2, ...
FROMYourViewName
(optional WHERE Clause)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 12, 2013 at 11:42 pm
thaks for you inputs. As i need to run the script on the differnt server, to which do not have acess , I can not use insert statement mentioned.
I think bcp out could be an option
Thanks and regards
November 13, 2013 at 12:36 am
The below given script is one that I use for such random tasks
DECLARE@strSQL VARCHAR(MAX)
DECLARE@strTableName VARCHAR(100)
DECLARE @strWhereClause VARCHAR(MAX)
SET@strTableName = 'mstEmployees' -- Put you View Name here
--SET@strWhereClause = 'EmployeeID IN (2,26,38)' -- Optional Where Clause here
SELECT@strSQL= COALESCE( @strSQL + ', ', '' )
+CASE
WHEN IC.DATA_TYPE IN ( 'varchar', 'char', 'nvarchar', 'nchar' )
THEN ''' + CASE WHEN [' + IC.COLUMN_NAME + '] IS NULL THEN ''NULL'' ELSE '''''''' + REPLACE( [' + IC.COLUMN_NAME + '], '''''''', '''''''''''' ) + '''''''' END + '''
WHEN IC.DATA_TYPE IN ( 'smalldatetime', 'datetime' )
THEN ''' + CASE WHEN [' + IC.COLUMN_NAME + '] IS NULL THEN ''NULL'' ELSE '''''''' + CAST( [' + IC.COLUMN_NAME + '] AS VARCHAR(20) ) + '''''''' END + '''
ELSE ''' + CASE WHEN [' + IC.COLUMN_NAME + '] IS NULL THEN ''NULL'' ELSE CAST( [' + IC.COLUMN_NAME + '] AS VARCHAR(20) ) END + '''
END
FROMsys.Columns Col
INNER JOININFORMATION_SCHEMA.COLUMNS IC
ONCol.name = IC.COLUMN_NAME
ANDCol.is_identity = 0
ANDCol.is_computed = 0
AND OBJECT_NAME( Col.[object_id] ) = IC.TABLE_NAME
WHEREIC.TABLE_NAME = @strTableName
SET@strSQL = 'SELECT ''INSERT INTO ' + @strTableName + ' VALUES( ' + @strSQL + ' )'' FROM dbo.' + @strTableName + COALESCE( NULLIF( ' WHERE ' + @strWhereClause, ' WHERE ' ), '' )
EXECUTE ( @strSQL )
Check the script generated and let me know if it helps
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 13, 2013 at 12:56 am
Hi thnaks it works. Can you explain a bit how its working.
November 13, 2013 at 7:43 am
You can use SSMS to script data for you too.
Right click the database -> Tasks -> Generate Scripts.
Choose whatever object(s) you want. Then go to Set Scripting Options. Click the Advanced button. The last choice in the General options is "Types of data to script". The default here is "Schema only". You can change that so that you can have it generate insert statements for you too. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply