CODEWITHSUNDEEP
sql-serverexcelvbasql-server-2008
Helodia
Helodia

Reputation: 108

Generate SQL insert statements of some records in a table

I try to auto generate insert SQL statement from an existing table in SQLServer 2008 but I do not need all record, only a small part of them. --> I thus need to filter the generated inserts. Adding a WHERE clause when generating the insert SQL statements would do the trick but I do not know how to do it.

This article answer to my question partly (SSMS internal generator) : What is the best way to auto-generate INSERT statements for a SQL Server table?

But it exports all the data of a table. The insert scripts generated are not sorted thus I cannot filter the row I need easily (heavy manual work).

I also tried this stored procedure here (I also had to correct a part of the procedure to make it work with SQLServer 2008 replace char(255) by varchar as explained here)

But it is still not working : I get the following error :

Msg 8169, Level 16, State 2, Line 6 Conversion failed when converting from a character string to uniqueidentifier.

Could you then give me the best way to auto generate SQL Insert in SQL server 2008 from a part of a portion of a table (thus not all the rows of the table) ?

Upvotes: 1

Views: 3327

Answers (3)

Aubrey Love
Aubrey Love

Reputation: 1034

There is an easier way to do this, other than going through all the fuss of an excel sheet.

This will return all the data in a table (much like the GUI version) where you right click on the database and select “Tasks” then select “Generate scripts”. However, unlike the GUI version or the “export to excel” version, with this line of code, you can specify a filter in a “WHERE” clause to return only items for a particular day, or range of days, or any other filter that would normally be used in a “WHERE” clause.

In the code below, I am using 2 simple tables. One is populated with data, the other is not. I want to transfer some or all of the data from table2 to table3. Again, I can filter by date or parts of other columns. (for example… WHERE colB LIKE 'ging%';

This will generate a string of “INSERT” statements preformed in SQL query ready to run.

Note, before running this, switch your output display in SQL server from “Grid” to “Text”. 

SELECT 'INSERT', + 'INTO', + 'TestTable3', + '(',  + 'colA', + ',', + 'colB', + ',', + 'colDate', + ')', + 'values', + '(', + '''', + CAST(colA AS VARCHAR(10)), + '''', + ',', + '''', + CAST(colB AS VARCHAR(10)), + '''', + ',', + '''', + CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) AS 'colDate', + '''', + ')', + ';'
FROM TestTable2
WHERE colDate LIKE '2018-10-14';
GO

Here is a snippet of what this will return.

enter image description here Simply copy/paste the results into a new query and run it.

Too easy.

Upvotes: 0

Helodia
Helodia

Reputation: 108

I found a way myself using Excel.

  1. Make needed query including WHERE clause in SSMS
  2. Select all the result
  3. Copy with header
  4. Paste in Excel file here under in 4th row, 1st column
  5. Change in macro output path
  6. Change in cell table name
  7. Launch macro --> take the file generated and you have a copy of your data ready to be insert again

https://dl.dropboxusercontent.com/u/49776541/GenerateInsert.xlsm

Upvotes: 1

Amul Harad
Amul Harad

Reputation: 146

You can use merge syntax to insert data in table based on specific condition using merge you can also delete and update data in table.you can also do multiple operation in single sql statement.

Upvotes: 0

Related Questions