Post reply

Help me for insert on query please

  • June 30, 2009 at 4:05 pm

    #206534

    how can i insert more that one row with query on a singel table

  • June 30, 2009 at 4:13 pm

    #1018042

    I have no idea. You haven't provided us with anything to work with here.

  • June 30, 2009 at 4:41 pm

    #1018053

    ok i have one table with MemberID, Name, Surname and City and information for inserted on this table and i have on text file for example 1001, John, Smith, Washington another member is 1002, Nick, Dagle, London etc etc. I request to insert at the same time on this table.

    for example

    INSERT INTO TblMember

    (MemberID, Name, Surname, City)

    VALUES (1001,John,Smith,Washington;

    1002, Nick, Smith, London

    1003,....,.....,.....)

    I hope you know what I think now

    thanks

  • June 30, 2009 at 4:51 pm

    #1018061

    Nardig (6/30/2009)

    ok i have one table with MemberID, Name, Surname and City and information for inserted on this table and i have on text file for example 1001, John, Smith, Washington another member is 1002, Nick, Dagle, London etc etc. I request to insert at the same time on this table.

    for example

    INSERT INTO TblMember

    (MemberID, Name, Surname, City)

    VALUES (1001,John,Smith,Washington;

    1002, Nick, Smith, London

    1003,....,.....,.....)

    I hope you know what I think now

    thanks

    You want to import a text file into a table? Each row of the text file contains a record with MemberId, FirstName, LastName, City?

    What is the delimiter between columns? Rows? Are the string values delimited and if so with what, double quotes?

  • June 30, 2009 at 5:25 pm

    #1018073

    Yes the text file looks like this

    1,'John','Smith','Washington'

    2,'Nick','Smith','London'

    3,'blabla','blabla','blaa'

    .

    .

    .

    this text contains 1200 records

  • June 30, 2009 at 9:43 pm

    #1018126

    Nardig (6/30/2009)

    Yes the text file looks like this

    1,'John','Smith','Washington'

    2,'Nick','Smith','London'

    3,'blabla','blabla','blaa'

    .

    .

    .

    this text contains 1200 records

    Check out Books-Online (BOL) for "BULK INSERT". If your destination table has additional columns, you may need a format file (see "BCP" in BOL for how to create one). Since your field terminator isn't a tab, you'll need to specify that in the BULK INSERT command.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 30, 2009 at 10:56 pm

    #1018146

    Hi Friend ,

    Fallow below steps .

    1. Create one function with three parameter

    String( RowData),Delimeter,Position that return the data for the passed position from the passed rowdata.

    Create Function [Functin Name]

    (

    @Rowdata VARCHAR(8000),

    @Delimeter CHAR(1),

    @Position INT

    )

    RETURNS VARCHAR(300)

    AS

    BEGIN

    --Write here code for find the data from the passed Rowdata

    END

    2. Use bulk insert command and store the data in a #Temp table( Having only one column with size varchar(4000) .

    3. After completing above steps in one select statement all the data from the text file will be inserted in your table . For Ex.

    Insert into [Table Name]

    Select Function(Rowdata,delimeter,position) from #TempTable

    Thanks

    Vinit Srivastava

  • June 30, 2009 at 11:11 pm

    #1018151

    Sample code for you to play with. You will need to put the data into a text file, and be sure to modify the line where I have the code looking for the file to point to where you put it.

    /*

    Sample data from file CustInfo.txt:

    1,"John","Smith","Washington"

    2,"Nick","Smith","London"

    3,"blabla","blabla","blaa"

    */

    create table dbo.TestTable (

    MemberID int,

    FirstName varchar(30),

    LastName varchar(30),

    City varchar(30)

    );

    bulk insert dbo.TestTable

    from 'C:\Databases\ImportData\CustInfo.txt'

    with (

    codepage = 'RAW',

    datafiletype = 'char',

    fieldterminator = ',',

    rowterminator = ''

    );

    update dbo.TestTable set

    FirstName = replace(FirstName,'"',''),

    LastName = replace(LastName,'"',''),

    City = replace(City,'"','');

    select

    *

    from

    dbo.TestTable;

    drop table dbo.TestTable;

  • June 30, 2009 at 11:13 pm

    #1018153

    Okay, my post above looks strange to me.

  • July 3, 2009 at 5:42 am

    #1019718

    Sorry Friend ,

    In my last post i think i have given you the steps for inserting data from any file into any table .

    But now i realise that that is worthless for you . Now for your post

    how can i insert more that one row with query

    so Now use this simple query .

    Insert into table [Table Name]

    Select [Col1,Col2,Col3,Col4....etc] from [Table Name]

    Use This Query

Viewing 10 posts - 1 through 9 (of 9 total)

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