将Excel数据导入并更新到sql中 [英] Import and Update Excel data into sql

问题描述

我正在一个将数据导入sqlserver的项目中，我已经写了SP进行插入，但是同时我需要在sql server中更新数据
我已经编写了示例SP，SP正在插入并更新excelsheet中的最后一行，我需要将数据从excelsheet导入sql，并且需要更新sqlserver中的数据

样本sp

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

sample sp

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

推荐答案

调用了存储过程，如果存储过程需要更新数据已经存在，如果不存在，则将其插入.

A stored procedure is called and the data needs to be updated if it already exists and inserted if it does not.

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
    INSERT INTO Table1 VALUES (...)

上述问题的解决方案

创建proc [dbo].[ConvertCharToBinary1](@ Value3 varchar(8000)，@ Value1 varchar(8000))
如

开始
如果存在(从[dbo] .table1中选择*，在[int_c] = convert(binary，cast(@ Value3 as int))中)

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


ELSE


插入[dbo] .table1([int_c]，[char_c])值
(
convert(binary，cast(@ Value3 as int))，
cast(CAST(@ Value1 as char(50))as binary(50))

)


结束
开始

This solution for above Question

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

这篇关于将Excel数据导入并更新到sql中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！