How to generate SQL script pragmatically in c#

Question

I want to generate txt/sql file using c# code which will have "insert script" for all the table data available in database. E.g. My database name is "Test". It has two tables

Employee
Department.

Department data:

ID Name
1 Account

Employee data

ID Name DeptD
1 Ravi 1
6 Ripal 1

Now my c# code should generate txt/sql file with following content.

USE [Test]
GO
SET IDENTITY_INSERT [dbo].[Department] ON 

GO
INSERT [dbo].[Department] ([ID], [Name]) VALUES (1, N'Account')
GO
SET IDENTITY_INSERT [dbo].[Department] OFF
GO
SET IDENTITY_INSERT [dbo].[Employee] ON 

GO
INSERT [dbo].[Employee] ([ID], [Name],  [DeptID]) VALUES (1, N'Ravi', 1)
GO
INSERT [dbo].[Employee] ([ID], [Name],  [DeptID]) VALUES (6, N'Ripal', 1)
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO

From what ?? Please state your requirement clearly and with all inputs and the code to implemented. — Mahesh
– Mahesh, Commented Sep 13, 2018 at 4:43
SSMS allows you to do this: Right-click on the database then Tasks->Generate Scripts. It will open a pop-up window. — Alex
– Alex, Commented Sep 13, 2018 at 4:55
I have created one procedure which is mentioned in answer that is generating insert script if you you would like to modify according to your situation then you cab. Check it — gulshan arora
– gulshan arora, Commented Sep 13, 2018 at 5:50

gulshan arora · Accepted Answer · 2018-09-13 04:56:14Z

You can create procedure for this and call that procedure from C#

CREATE PROCEDURE sp_generate_insertscripts
(
    @TABLE_NAME VARCHAR(MAX),
    @FILTER_CONDITION VARCHAR(MAX)=''
)
AS
BEGIN

SET NOCOUNT ON

DECLARE @CSV_COLUMN VARCHAR(MAX),
        @QUOTED_DATA VARCHAR(MAX),
        @TEXT VARCHAR(MAX)

SELECT @CSV_COLUMN=STUFF
(
    (
     SELECT ',['+ NAME +']' FROM sys.all_columns 
     WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND 
     is_identity!=1 FOR XML PATH('')
    ),1,1,''
)

SELECT @QUOTED_DATA=STUFF
(
    (
     SELECT ' ISNULL(QUOTENAME('+NAME+','+QUOTENAME('''','''''')+'),'+'''NULL'''+')+'','''+'+' FROM sys.all_columns 
     WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND 
     is_identity!=1 FOR XML PATH('')
    ),1,1,''
)

SELECT @TEXT='SELECT ''INSERT INTO '+@TABLE_NAME+'('+@CSV_COLUMN+')VALUES('''+'+'+SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)+'+'+''')'''+' Insert_Scripts FROM '+@TABLE_NAME + @FILTER_CONDITION

--SELECT @CSV_COLUMN AS CSV_COLUMN,@QUOTED_DATA AS QUOTED_DATA,@TEXT TEXT

EXECUTE (@TEXT)

SET NOCOUNT OFF

END

--EXEC sp_generate_insertscripts 'tableName'

codelearner · Accepted Answer · 2018-09-13 04:49:14Z

0

Most of the database has export functionality that you can use to generate INSERT script to transfer the data into another database but it differs from database to database.

answered Sep 13, 2018 at 4:49

codelearner

15 bronze badges

Collectives™ on Stack Overflow

How to generate SQL script pragmatically in c#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related