Import and Update Excel data into sql

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

General SQL Server Forums

New to SQL Server Programming

Import and Update Excel data into sql

Author

Topic

pradeepmanne
Starting Member

31 Posts

Posted - 2011-09-08 : 07:38:37

hi,
am working on a project which import data into sqlserver,i have written SP for insertion but at the same time i need to update the data in sql server
i have written sample SP ,that SP is inserting and updating last row in the excelsheet ,i need to import data from excelsheet to sql and need to update the data in sqlserver

CREATE proc [dbo].[ConvertCharToBinary1](@Value varchar(8000))
as
Begin
declare @Flag int;

IF EXISTS(select *from [dbo].table1)
begin
set @Flag = 1;
end
else
begin
set @Flag = 0;
end
if @Flag = 0
begin
insert into [dbo].table1([Char_c]) values(cast(CAST(@Value as char(8000)) as binary(8000)))
End
else
begin
UPDATE table1 SET [char_c]=(cast(cast(@Value as char(4000))as binary(4000)))
End
End
GO

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 07:49:45

What's your question?

pradeepmanne
Starting Member

31 Posts

Posted - 2011-09-08 : 07:56:13

quote:
Originally posted by russell

What's your question?

i need to insert excelsheet data into table,and also when i correct(update) data in excelsheet and reimport it into table the updated data should overwrite in the table

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 08:00:41

The script you posted should work for that. Is the question how to get the Excel data in the first place?

I'll use SSIS for that, but you can use OPENROWSET for that too.

pradeepmanne
Starting Member

31 Posts

Posted - 2011-09-08 : 08:03:07

quote:
Originally posted by russell

The script you posted should work for that. Is the question how to get the Excel data in the first place?

I'll use SSIS for that, but you can use OPENROWSET for that too.

the script is working but only last one row is inserting and updating
rest of the rows are skiping

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 08:04:22

Well, how are you pulling the data from Excel?

pradeepmanne
Starting Member

31 Posts

Posted - 2011-09-08 : 08:09:25

quote:
Originally posted by russell

Well, how are you pulling the data from Excel?

hi,
am using asp.net for getting excelsheet data

public void ConvertCharToBinary1(string Value_c)
{

string connectionString = "server=localhost; database=sample1;user id=sa; Password=sa";
SqlConnection _con = new SqlConnection(connectionString);
SqlCommand _cmd = new SqlCommand("ConvertCharToBinary1", _con);
_cmd.CommandType = CommandType.StoredProcedure;
_cmd.Parameters.Add(new SqlParameter("@Value", Value_c)).Direction = ParameterDirection.Input;
// _cmd.Parameters.Add(new SqlParameter("@ConvertType", Type_c)).Direction = ParameterDirection.Input;
_con.Open();
_cmd.ExecuteNonQuery();
_con.Close();
}

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 08:14:16

The way your SP is setup, you need to pass one record at a time to it from the .net script.

Else you can change it to use the other methods I mentioned, or pass the entire set as a table variable and modify the stored procedure accordingly.

pradeepmanne
Starting Member

31 Posts

Posted - 2011-09-08 : 08:27:17

quote:
Originally posted by russell

The way your SP is setup, you need to pass one record at a time to it from the .net script.

Else you can change it to use the other methods I mentioned, or pass the entire set as a table variable and modify the stored procedure accordingly.

hi sir,
when am only inserting the data into sql without update part its working properly,but when i have written the update part ,am getting this problem its inserting and updating lastrow of the excelsheet

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 08:41:59

You need a way to identify rows. Your procedure will update every row if the table isn't empty.

something like this:

CREATE proc [dbo].[ConvertCharToBinary1]
	@Value varchar(8000),
	@id int
AS

SET NOCOUNT ON;

IF EXISTS(select * from [dbo].table1)
begin
	UPDATE table1 SET [char_c]=(cast(cast(@Value as char(4000))as binary(4000)))
	WHERE id = @id
end
else
begin
	insert into [dbo].table1([Char_c]) values(cast(CAST(@Value as char(8000)) as binary(8000)), @id)
end
GO

pradeepmanne
Starting Member

31 Posts

Posted - 2011-09-08 : 11:50:30

quote:
Originally posted by russell

You need a way to identify rows. Your procedure will update every row if the table isn't empty.

something like this:
CREATE proc [dbo].[ConvertCharToBinary1]
@Value varchar(8000),
@id int
AS

SET NOCOUNT ON;

IF EXISTS(select * from [dbo].table1)
begin
UPDATE table1 SET [char_c]=(cast(cast(@Value as char(4000))as binary(4000)))
WHERE id = @id
end
else
begin
insert into [dbo].table1([Char_c]) values(cast(CAST(@Value as char(8000)) as binary(8000)), @id)
end
GO

Hi
I Have changed my code ,but insertion is not happening
kindly check my code
CREATE proc [dbo].[ConvertCharToBinary1](@Value1 varchar(8000),@Value3 varchar(8000))
as

Begin
declare @Flag int;

IF EXISTS(select *from [dbo].table1)
begin
set @Flag = 1;
end
else
begin
set @Flag = 0;
end
if @Flag = 0
begin
UPDATE table1 SET [int_c]=convert(binary,cast(@Value3 as int)),
[char_c]=(cast(cast(@Value1 as char(4000))as binary(4000))) where [int_c]=@Value3
End
else
begin

insert into [dbo].table1([char_c],[int_c]) values
(
convert(binary,cast(@Value3 as int)),
cast(CAST(@Value1 as char(50)) as binary(50))

)

End
End
GO

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 12:10:07

If insert isn't happening, it's because the table isn't empty.

And your WHERE clause is still wrong, because you're casting the value on insert, but not in the WHERE clause.

pradeepmanne
Starting Member

31 Posts

Posted - 2011-09-08 : 12:19:16

quote:
Originally posted by russell

If insert isn't happening, it's because the table isn't empty.

And your WHERE clause is still wrong, because you're casting the value on insert, but not in the WHERE clause.

I have changed the where clause as but only single row is inserting
only one row is inserting and upadting ,i manually inserting it but one row is insertinG
UPDATE table1 SET [int_c]=convert(binary,cast(@Value3 as int)),
[char_c]=(cast(cast(@Value1 as char(4000))as binary(4000))) where [int_c]=convert(binary,cast(@Value3 as int))

pradeepmanne
Starting Member

31 Posts

Posted - 2011-09-13 : 09:18:01

quote:
Originally posted by russell

If insert isn't happening, it's because the table isn't empty.

And your WHERE clause is still wrong, because you're casting the value on insert, but not in the WHERE clause.

hi,i rectified the problem its working now
below is the solution script

CREATE proc [dbo].[ConvertCharToBinary1](@Value3 varchar(8000),@Value1 varchar(8000))
as

Begin
IF EXISTS (select * from [dbo].table1 WHERE [int_c]=convert(binary,cast(@Value3 as int)))

UPDATE table1 SET [int_c]=convert(binary,cast(@Value3 as int)),
[char_c]=(cast(cast(@Value1 as char(4000))as binary(4000))) where [int_c]=convert(binary,cast(@Value3 as int))

ELSE

insert into [dbo].table1([int_c],[char_c]) values
(
convert(binary,cast(@Value3 as int)),
cast(CAST(@Value1 as char(50)) as binary(50))

)

End
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources