How to write a script to generate script SQL Server 2008

Question

I want to write a script for generate insert into script.

I have a NewJob table. It has some rows. I want to generate insert into statements using script like this:

INSERT [Computer A].[newjob] ([Id], [Newjob], [jobNo]) VALUES (1, N'A', 4)
INSERT [Computer A].[newjob] ([Id], [Newjob], [jobNo]) VALUES (2, N'b', 8)
INSERT [Computer A].[newjob] ([Id], [Newjob], [jobNo]) VALUES (3, N'c', 12)

Can I do this ?

TGH · Accepted Answer · 2013-11-06 05:57:50Z

2

You can do this using a select statement and some basic string manipulation. The Select statement will produce an insert statement for each row returned.

SELECT 'INSERT INTO [Computer A].[newjob] ([Id]) VALUES (' + Id + ')'
FROM [Computer A].[newjob]

I have only included one parameter here, but you can fill in the rest by adding the columns you want to add

edited Nov 6, 2013 at 5:57

answered Nov 6, 2013 at 5:51

TGH

39.4k13 gold badges106 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

James A Mohler Over a year ago

Are you sure you want 2 inserts?

James A Mohler Over a year ago

I think the from is missing a [ too

Madhivanan · Accepted Answer · 2013-11-06 06:08:27Z

1

I think the best way is to make use Script option from SSMS

SSMS-->Right click on database-->Tasks-->Generate scripts-->Choose the table-->Advanced

In the option Types of data to script, choose Data only

answered Nov 6, 2013 at 6:08

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Collectives™ on Stack Overflow

How to write a script to generate script SQL Server 2008

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related