Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Posts
    46

    Unanswered: Recordset insert into a table in a SP

    Hi,
    I need to insert a recordset into a single columns in a table in a Stored procedure.
    I have dine the following:

    Insert into Staging_Table Values(Recordset)

    I get an error that only constants are allowed.

    How can I insert the recordset values into that table?

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: Recordset insert into a table in a SP

    RE:
    Hi,
    I need to insert a recordset into a single columns in a table in a Stored procedure. I have dine the following:
    Insert into Staging_Table Values(Recordset)
    I get an error that only constants are allowed.
    Q1 How can I insert the recordset values into that table?
    A1 Insert values inserts (explicit) values. (Try posting the applicable ddl and some sample statements if this is what you are doing.)

    For Example:

    Use TempDB
    Go

    CREATE TABLE TestTable ( column_1 varchar(32))
    Go

    INSERT TestTable VALUES ('Row #1 Value')
    INSERT TestTable VALUES ('Row #2 Value')

    SELECT * From TestTable

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    How do you want to store the recordset in the column and how would you extract information from it once is has been stored in the column ? Please post your existing code.

  4. #4
    Join Date
    Aug 2002
    Posts
    46
    Originally posted by rnealejr
    How do you want to store the recordset in the column and how would you extract information from it once is has been stored in the column ? Please post your existing code.
    Hi,
    My existing code is:

    if @col2 is null
    Begin
    set @SQL = 'create table LanTable ( ' + @col1 + ' nvarchar(60))'
    exec (@SQL)
    Insert into LanTable SELECT Data1= substring ( Record_Line , @Pos1,@Len1) FROM Staging_Table
    End
    else if @col3 is null
    Begin
    set @SQL = 'create table LanTable ( ' + @col1 + ' nvarchar(60),' + @col2 + ' nvarchar(60))'
    exec (@SQL)
    Insert into LanTable SELECT Data1= substring ( Record_Line , @Pos1,@Len1), Data2= substring ( Record_Line , @Pos2,@Len2) FROM Staging_Table
    End

    @Pos1,@Len1,@col2.... all are input parameters of the SP.
    Staging_Table consists of one column (Record_Line) that contains the data (ex. 03 Bank 2222 -etc)
    Data1 and Data2 are recordsets that each contain their values from the Staging_Table and i want it to be inserted into LanTable.
    Can it be done?
    Thanks

  5. #5
    Join Date
    Aug 2002
    Posts
    46
    Originally posted by garfild
    Hi,
    My existing code is:

    if @col2 is null
    Begin
    set @SQL = 'create table LanTable ( ' + @col1 + ' nvarchar(60))'
    exec (@SQL)
    Insert into LanTable SELECT Data1= substring ( Record_Line , @Pos1,@Len1) FROM Staging_Table
    End
    else if @col3 is null
    Begin
    set @SQL = 'create table LanTable ( ' + @col1 + ' nvarchar(60),' + @col2 + ' nvarchar(60))'
    exec (@SQL)
    Insert into LanTable SELECT Data1= substring ( Record_Line , @Pos1,@Len1), Data2= substring ( Record_Line , @Pos2,@Len2) FROM Staging_Table
    End

    @Pos1,@Len1,@col2.... all are input parameters of the SP.
    Staging_Table consists of one column (Record_Line) that contains the data (ex. 03 Bank 2222 -etc)
    Data1 and Data2 are recordsets that each contain their values from the Staging_Table and i want it to be inserted into LanTable.
    Can it be done?
    Thanks

    Hi again,
    Try to use this SP and tell me what i did wrong:

    CREATE PROCEDURE Lan_BuildTable
    @col1 nvarchar(60), @Pos1 int=null, @Len1 int=null,@col2 nvarchar(60) = null,@Pos2 int = null, @Len2 int=null
    AS
    declare @SQL as varchar(3000)


    if @col2 is null
    Begin
    set @SQL = 'create table LanTable ( ' + @col1 + ' nvarchar(60))'
    exec (@SQL)
    Insert into LanTable SELECT Data1= substring ( Record_Line , @Pos1,@Len1) FROM Staging_Table
    End
    else if @col3 is null
    Begin
    set @SQL = 'create table LanTable ( ' + @col1 + ' nvarchar(60),' + @col2 + ' nvarchar(60))'
    exec (@SQL)
    Insert into LanTable SELECT Data1= substring ( Record_Line , @Pos1,@Len1), Data2= substring ( Record_Line , @Pos2,@Len2) FROM Staging_Table
    End


    In the VB code I used:
    exec Lan_BuildTable OpCode,1,2,Product,4,5

    Thanks
    Yossi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •