Dynamic insert of a recordset into a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic insert of a recordset into a table

Author

Topic

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-11-28 : 02:55:23

Hi,
I have a Staging table that contains one column (Recor_Line).
Record_Line
03 productNum ProductName
09 House Green
06 Temp Red
etc......

I need to insert the above into new LanTable tabel as follows:
OpCode Data1 Data2
03 productNum ProductName
09 House Green
06 Temp Red
etc....

for that I used the following stored procedure:
###############################
CREATE PROCEDURE Lan_BuildTable
@col1 nvarchar(60), @Pos1 int=null, @Len1 int=null,@col2 nvarchar(60) = null,@Pos2 int = null, @Len2 int=null,@col3 nvarchar(60) = null,@Pos3 int = null, @Len3 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

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

Etc........
######################
From my vb application i call the SP as follows:
exec Lan_BuildTable OpCode,1,2,Col1,4,18,Col2,23,10

All i want is to insert recordsets Data1,Data2,Data3 into LanTable and i always get the following error:
Insert Error: Column name or number of supplied values does not match table definition.

Please help.
Thanks

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-28 : 13:29:27

from your code

Insert into LanTable SELECT Data1= substring ( Re.......

When you do an insert statement into a table always list the columns your inserting into.

Insert into LanTable(col1,col2,col3) select value1,value2,value3

-----------------------
SQL isn't just a hobby, It's an addiction

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-02 : 02:18:15

quote:

from your code

Insert into LanTable SELECT Data1= substring ( Re.......

When you do an insert statement into a table always list the columns your inserting into.

Insert into LanTable(col1,col2,col3) select value1,value2,value3

-----------------------
SQL isn't just a hobby, It's an addiction

Thanks mate it helps.......
Yossi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources