Insert Script

Question

Insert Script

Chandru -734144

Ten Centuries

Points: 1040
More actions
July 3, 2008 at 8:45 am

#93594

Hai all,
I'm new to T-Sql can anyone help me on this ..
How to prepare insert script to insert data from excel sheet to sql server database tables.
Thanks in Advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Well, there are a few ways you can do this. Are you trying to do a one-time insert of data or are you trying to create a re-usable automated import process?

If this is a one-time import, you can use Excel to generate the T-SQL INSERT statements. Since you're new to T-SQL, you'll first need to know how to construct a basic INSERT statement, then use the Excel CONCATENATE function to build a SQL INSERT based off of the Cell values. I've attached a simple spreadsheet as an example. Add some data into the spreadsheet and watch it build the INSERTS.

If you are trying to create an automated imported that will be re-usable, you'll want to create a SSIS package to do this for you. This is a pretty straight forward task with SSIS, but not something that I want to do for you. Take some time and play around with creating the SSIS tasks to do this and post questions as you have them.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

GSquared SSC Guru Points: 260824 More actions · Answer 2

You might be better off using the Import Wizard in Management Studio. Right-click on the database you want to insert the data into, then Tasks, Import Data, and walk through the wizard.

If you'll be doing this multiple times, you can save the task as an SSIS package and re-use it.

The other option is to use OpenRowset. Look that one up in Books Online. It's not as difficult as the documentation makes it look.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Chandru -734144 Ten Centuries Points: 1040 More actions · Answer 3

Thanks John I can understand the insert statement well and im trying to use one time import , i dint get any idea about Excel CONCATENATE function to build a SQL INSERT can you help me on this.

Chandru -734144 Ten Centuries Points: 1040 More actions · Answer 4

Thanks John,

I got the idea about Excel CONCATENATE function ..

Thanks for your kindly help..

John Rowan SSC Guru Points: 56440 More actions · Answer 5

Yep, not problem. I would like to re-iterate that I would use this method sparingly as SQL Server has other built-in ways to import data from a xls file. As I stated, you can create a SSIS package that would be re-usable, or do as Barry suggests and use the wizard to do the import (and then save it as a SSIS package) or use OPENROWSET. Any of these methods would be preferable if this is something that you are going to reuse, automate, or build a business process around.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden