use datatable in select statement

Question

2.00/5 (1 vote)

See more:

SQL

da.InsertCommand = new SqlCommand(@"insert into Customers (nameCustomer)
select DISTINCT nameCustomer from dt where NOT EXISTS (select nameCustomer from Customers where Customers.nameCustomer=dt.nameCustomer)", cn);

                cn.Open();
                da.InsertCommand.ExecuteNonQuery();
                cn.Close();

Customers is table in sql server database
dt is datatable filled from excel sheet

when i run the project this error appear "Invalid object name 'dt'."
so how can i use datatable in select statement.

Posted 16-Feb-15 8:33am

tranesyasd

Add a Solution

Comments

Zoltán Zörgő 16-Feb-15 14:39pm

dt is a datatable? What does this mean in your case? In your statement dt looks a regular sql table in the same database as Customers. If it is not, it won't work.

tranesyasd 16-Feb-15 14:43pm

i have datatable (dt) filled from excel sheet, i need to insert the values in this datatable in database in table (Customers) without duplicate the data so i used this sql statement in my c# code but when i run the project there is error (invalid object named dt) so how i can i use it in this statement

PIEBALDconsult 16-Feb-15 14:50pm

That won't work.
If this is something you need to do often, then I suggest you look into SSIS or another ETL product.

2 solutions

Solution 1

No, that won't work because dt is a DataTable in your C# code, and the SQL is very likely to to running on a totaly diffferent machine. So passing the name of your c# object as part of the command string is never goign to work...

Instead, look at using SqlBulkCopy: SqlBulkCopy.WriteToServer Method (DataTable)[^]

Posted 16-Feb-15 8:49am

OriginalGriff

Comments

tranesyasd 16-Feb-15 14:56pm

how can i use sqlbulkcopy without duplicate the data.
if the data is exist don't insert it just insert the new values as i did in my sql statement
thank you

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Accepted Answer · 2015-02-16T08:49:00

Solution 2

You can't mix data sources this way. Your SQL server has no information about any data stored on your application level.
You either parse the datable rows one by one (see: http://www.dotnetperls.com/datatable-foreach[^]), or you can create a stored procedure that accepts your datatable as table typed parameter and does the insertion, or use bulkcopy. See the asnwers here: http://stackoverflow.com/questions/10405373/insert-entire-datatable-into-database-at-once-instead-of-row-by-row[^]

Posted 16-Feb-15 8:49am

Zoltán Zörgő

Comments

tranesyasd 16-Feb-15 15:00pm

how can i use it without duplicate the data

Zoltán Zörgő 16-Feb-15 15:04pm

Well do the check you tried to do

tranesyasd 16-Feb-15 15:11pm

i create stored procedure like this :

CREATE TYPE dbo.dtExcel -- you can be more speciifc here
AS TABLE
(
nameCustomer NVARCHAR(50),
nameItem NVARCHAR(50)
-- etc etc. The columns you have in your data table.
);
GO

CREATE PROCEDURE dbo.InsertMyDataTable
@dt AS dbo.dtExcel READONLY
AS
BEGIN
SET NOCOUNT ON;

insert into Customers (nameCustomer)
select DISTINCT nameCustomer from @dt where NOT EXISTS (select nameCustomer from Customers where Customers.nameCustomer=@dt.nameCustomer)
END
GO

but there is error
can you help me?

Zoltán Zörgő 16-Feb-15 15:14pm

And the error is...?

tranesyasd 16-Feb-15 15:14pm

Must declare the scalar variable "@dt"

Zoltán Zörgő 16-Feb-15 15:18pm

Check underlined parts:

CREATE TYPE dbo.dtExcel
AS TABLE
(
nameCustomer NVARCHAR(50),
nameItem NVARCHAR(50)
);
GO
 
CREATE TABLE Customers
(
	nameCustomer NVARCHAR(50),
)
GO

CREATE PROCEDURE dbo.InsertMyDataTable
@dt AS dbo.dtExcel READONLY
AS
BEGIN
SET NOCOUNT ON;
 
insert into Customers (nameCustomer)
select DISTINCT nameCustomer from @dt as dt where NOT EXISTS (select nameCustomer from Customers where Customers.nameCustomer=dt.nameCustomer)
END
GO

tranesyasd 16-Feb-15 15:27pm

it's working
thank you very much

tranesyasd 16-Feb-15 15:29pm

i added this code in c# :
DataTable tvp = new DataTable();
tvp = dtexcel;

using (cn)
{
SqlCommand cmd = new SqlCommand("dbo.InsertMyDataTable", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", tvp);
tvparam.SqlDbType = SqlDbType.Structured;
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
and work correctly without any duplicate
thank you very much zoltan zorgo

Zoltán Zörgő 16-Feb-15 15:29pm

Well, than feel free to upvote and accept my answer! :)

tranesyasd 16-Feb-15 15:36pm

i did that :)

Zoltán Zörgő 16-Feb-15 15:37pm

Thank you.