We help IT Professionals succeed at work.

Bulk insert

Mike Eghtebas
on
The following is a proc to accept a data table as parameter and insert it to another table:
CREATE PROCEDURE [dbo].[InsertData]
    @TableType TableType readonly
AS
BEGIN
    insert into [dbo].DataList select * from @TableType; 
END

Open in new window


When I run this code, I get "Parameter or variable '@TableType' has an invalid data type."

Q1: What is correct syntax for line  @TableType TableType readonly?

Q2: Could you please code to create a sample table with two lines of data so I could test this proc?
fileID       filename        createdOn
1              File_1          2014-02-26 	
2              File_2          2014-03-28

Open in new window


Note that fileID is identity column.
Comment
Watch Question

Raheman M. AbdulMessaging and Directory Services
Commented:
CREATE PROCEDURE [dbo].[InsertData]  (
@SourceTable as varchar(50)
)
AS
BEGIN
    insert into [dbo].DataList select * from @SourceTable ;
END
Software Engineer
Commented:
Using this MSDN example I suggest you try:
CREATE TYPE TableType AS TABLE 
( /*fileID INT IDENTITY(1, 1),*/ [filename] VARCHAR(100), createdOn DATE);
GO

CREATE PROCEDURE [dbo].[InsertData]
    @TableType TableType readonly
AS
BEGIN
    insert into [dbo].DataList select * from @TableType; 
END
GO

-- the calling part

DECLARE @myTableVar AS TableType;

INSERT INTO @myTableVar ([filename], createdOn) VALUES
 ('File_1', '2014-02-26')
,('File_2', '2014-03-28')

EXEC [dbo].[InsertData] @myTableVar;
GO

SELECT * FROM [dbo].DataList;
GO

Open in new window

Note that you don't need the id in the table variable but I left it in the code (commented out) should you need it anyway. But in that case you also need to set IDENTITY_INSERT ON for the DataList table (and OFF again after the insert) which would significantly complicate the situation because you would need to avoid duplicates yourself.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
eghtebas, do you still need help with this question?