Here i chose to script a specific table and click next.
Next thing that came to mind was right click on the database -> Tasks -> Generate Scripts…Īnother wizard starts. Well, not really…what i got was an insert statement with all the columns – INSERT INTO Table(Columns) VALUES(Columns), not the data. Immediately i saw: Script Table as -> Insert To -> New Query Editor Window…Īlright here we go, another – soon to be, satisfied customer! I started to right click on a table just to see what options there are. My immediate thought was that surely Management Studio incorporates this kind of functionality. The other day a customer approached me with a question: “Is there a way to generate insert statements, with the data included, for a specific table ?” The prefixed clause will only face removal of the trailing comma.Ok, i might be the last person on earth to know about this but here it goes: Return END WHILE IF in ( ' varchar', ' char', ' nchar', ' nvarchar')īEGIN SET ' ' ,'''')+'''''',''+' END ELSE if in ( ' text', ' ntext') - if the datatype - is text or something else BEGIN SET ' ' as varchar(2000)),'''')+'''''',''+' END ELSE IF = ' money' - because money doesn't get converted - from varchar implicitly BEGIN SET ' ' as varchar(200)),''0.0000'')+''''''),''+' END ELSE IF ' datetime' BEGIN SET ' ' as varchar(200)),''0'')+''''''),''+' END ELSE IF ' image' BEGIN SET ' ' )Īs varchar(6)),''0'')+'''''',''+' END ELSE - presuming the data type is int,bit,numeric,decimal BEGIN SET ' ' as varchar(200)),''0'')+'''''',''+' END SET ' ,' FETCH NEXT FROM cursCol INTO ENDĪfter both of the clauses are built, the VALUES clause contains a trailing comma which needs to be replaced with a single quote. Declare a cursor to retrieve column specific information - for the specified table DECLARE cursCol CURSOR FAST_FORWARD FOR SELECT column_name,data_type FROM information_lumnsĭECLARE nvarchar( 3000) - for storing the first half - of INSERT statement DECLARE nvarchar( 3000) - for storing the data - (VALUES) related statement DECLARE nvarchar( 1000) - data types returned - for respective columns SET ' INSERT ' (' SET ' ' DECLARE nvarchar( 50)įETCH NEXT FROM cursCol INTO IF print ' Table ' not found, processing skipped.' close curscol The procedure accepts an input varchar type parameter that has to be the table name under consideration for statement generation. The output can be redirected to either text format (Ctrl+T in Query Analyzer) or Output to a text file. Using the codeThis small yet useful stored procedure will take as parameter the table name and generates the INSERT SQL statements for the same. Simplest example is when small or large amount of data needs to be taken out on a removable storage media and copied to a remote location, INSERT. However, there exists a need to generate INSERT statements from the tables for porting data. The workaround is to make use of DTS for transferring data across servers. SQL Server doesn’t allow generation of INSERT statements for the table data, when Generate SQL Script option is selected.
VALUES statements for the specified table name. The stored procedure InsertGenerator generates the INSERT.
0 kommentar(er)
