Post reply

Insert Script

  • 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

  • July 3, 2008 at 9:22 am

    #837937

    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

  • July 3, 2008 at 11:49 am

    #838047

    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

  • July 3, 2008 at 11:46 pm

    #838215

    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.

  • July 4, 2008 at 12:52 am

    #838235

    Thanks John,

    I got the idea about Excel CONCATENATE function ..

    Thanks for your kindly help..

  • July 7, 2008 at 12:55 pm

    #839113

    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

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

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