• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

Bulk insert

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.
0
Mike Eghtebas
Asked:
Mike Eghtebas
2 Solutions
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
CREATE PROCEDURE [dbo].[InsertData]  (
@SourceTable as varchar(50)
)
AS
BEGIN
    insert into [dbo].DataList select * from @SourceTable ;
END
0
 
Robert SchuttSoftware EngineerCommented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
eghtebas, do you still need help with this question?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now