Need Help for Bulk Insert - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Need Help for Bulk Insert

Author

Topic

rnavarro
Starting Member

10 Posts

Posted - 2012-09-10 : 14:13:58

People need to insert data from a file. Csv file that I need to select some columns col1, col14, col15.col13 only and import it
into MS SQL 2008 R2
database: Haony
Table: TLA_SGRG

occur when importing 2 options UPDATE or INSERT INTO
Any idea how to?, pleae i need this Thank you very much

Structure file .csv with data

EMPLID, COMPANY, FIRST_NAME, LAST_NAME, NAME, CONTRACT_BEGIN_DT, CONTRACT_END_DT, STATUS, MARCAJE, NSA_NROTARJETA, TARJETA_BEGIN, TARJETA_END, GRUPO_SEGURIDAD, GRUPO_BEGIN, GRUPO_END, DEPARTAMENTO, CENTRO TRABAJO, REFER IMAGEN, REASON, TAG_STATUS,
90885,40,Manuel Federico,Cejas,Cejas Manuel Federico,09/07/1996,,True,1,0004894423,22/05/2012,22/05/2032,4001,09/07/1996,14/12/2041,ARG00640,10000016,\\ARUSFSRV36\EMPLEADOS$\090885.jpg,2,2
90885,40,Manuel Federico,Cejas,Cejas Manuel Federico,09/07/1996,,True,1,0004894423,22/05/2012,22/05/2032,4002,09/07/1996,14/12/2041,ARG00640,10000016,\\ARUSFSRV36\EMPLEADOS$\090885.jpg,2,2
90885,40,Manuel Federico,Cejas,Cejas Manuel Federico,09/07/1996,,True,1,0441447700,09/07/1996,14/12/2041,4001,09/07/1996,14/12/2041,ARG00640,10000016,\\ARUSFSRV36\EMPLEADOS$\090885.jpg,2,0
90885,40,Manuel Federico,Cejas,Cejas Manuel Federico,09/07/1996,,True,1,0441447700,09/07/1996,14/12/2041,4002,09/07/1996,14/12/2041,ARG00640,10000016,\\ARUSFSRV36\EMPLEADOS$\090885.jpg,2,0
400018,40,Raul Enrique,Toledo,Toledo Raul Enrique,14/08/1981,,True,1,0004892929,22/05/2012,22/05/2032,4001,14/08/1981,14/12/2041,ARG00640,10000014,\\ARUSFSRV36\EMPLEADOS$\400018.jpg,2,0
400018,40,Raul Enrique,Toledo,Toledo Raul Enrique,14/08/1981,,True,1,0004892929,22/05/2012,22/05/2032,4002,14/08/1981,14/12/2041,ARG00640,10000014,\\ARUSFSRV36\EMPLEADOS$\400018.jpg,2,0
400117,40,Carlos Alberto,Rodriguez,Rodriguez Carlos Alberto,16/02/1987,,True,1,0004898800,22/05/2012,22/05/2032,4001,16/02/1987,14/12/2041,ARG00640,10000005,\\ARUSFSRV36\EMPLEADOS$\400117.jpg,2,0
400117,40,Carlos Alberto,Rodriguez,Rodriguez Carlos Alberto,16/02/1987,,True,1,0004898800,22/05/2012,22/05/2032,4002,16/02/1987,14/12/2041,ARG00640,10000005,\\ARUSFSRV36\EMPLEADOS$\400117.jpg,2,0

structure format file

10.0
10
1 SQLINT 0 4 "" 1 Code ""
2 SQLINT 1 4 "" 2 SGroup ""
3 SQLTINYINT 1 1 "" 3 GroupType ""
4 SQLTINYINT 1 1 "" 4 LevelOrg ""
5 SQLNCHAR 2 20 "" 5 CodeOrg Latin1_General_CI_AS
6 SQLDATETIME 1 8 "" 6 DateFrom ""
7 SQLDATETIME 1 8 "" 7 DateTo ""
8 SQLNCHAR 2 500 "" 8 Note Latin1_General_CI_AS
9 SQLFLT8 1 8 "" 9 UpdatedBy ""
10 SQLDATETIME 1 8 "" 10 UpdatedDate ""

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 15:07:11

sorry didnt understand what issue? what method you used for update? openrowset?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rnavarro
Starting Member

10 Posts

Posted - 2012-09-10 : 15:16:38

Sorry I need import selected cols of csv, to selected fields into SQL.
The only problem is that there is information that needs to be updated or added new with the information of csv file
thank you for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 15:33:17

quote:
Originally posted by rnavarro

Sorry I need import selected cols of csv, to selected fields into SQL.
The only problem is that there is information that needs to be updated or added new with the information of csv file
thank you for your help

then use OPENROWSET

http://www.sqlusa.com/bestpractices2008/import-csv/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rnavarro
Starting Member

10 Posts

Posted - 2012-09-10 : 15:50:46

and how di i select a col in csv file?

thx for everything?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 15:54:26

quote:
Originally posted by rnavarro

and how di i select a col in csv file?

thx for everything?


SELECT requdcol1,reqdcol2,..
FROM OPENROWSET('MSDASQL', 

'Driver={Microsoft Text Driver (*.txt; *.csv)};

DefaultDir=your csv path;',

'SELECT * FROM yourcsvfilename.csv')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rnavarro
Starting Member

10 Posts

Posted - 2012-09-10 : 16:16:38

thx, problem resolved
Very good forum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 16:29:34

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rnavarro
Starting Member

10 Posts

Posted - 2012-09-11 : 09:53:31

can i select field to insert the data?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 11:14:33

you mean insert data to csv? or insert data from csv selected columns to table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rnavarro
Starting Member

10 Posts

Posted - 2012-09-11 : 11:49:10

i select from .csv some columns and must insert data selecting columns from table. Is possible?
example
csv Table
col1 field 2
col14 field 5
col15 field 3
col13 field 8 (In this field the value must be fixed. the value must be 6) this value is not in .csv

Sorry so many question but i dont know any method of SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 14:24:16

yes . it is


INSERT INTO Table (field2,field5,field3,field6)
SELECT col1,col14,col15,6
FROM OPENROWSET('MSDASQL', 

'Driver={Microsoft Text Driver (*.txt; *.csv)};

DefaultDir=your csv path;',

'SELECT * FROM yourcsvfilename.csv')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rnavarro
Starting Member

10 Posts

Posted - 2012-09-11 : 16:21:59

This error show when I made this query

USE Haony;
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

INSERT INTO dbo.TLA_SG2Org (CodeOrg,DateFrom,DateTo,SGroup,LevelOrg)
SELECT EMPLID,GRUPO_BEGIN,GRUPO_END,GRUPO_SEGURIDAD,6
FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};

DefaultDir=\\arxxxx76\ABM;',

'SELECT * FROM empleados.csv')

go

Msg 512, Level 16, State 1, Procedure SY_tr_Audit_TLA_SG2Org_Ins, Line 20
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

any idea how do i resolve this error?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 16:25:29

i'm sure this is not your used query as error clearly suggests you're using a procedure

Msg 512, Level 16, State 1, Procedure SY_tr_Audit_TLA_SG2Org_Ins, Line 20
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

I cant spot anything obvious in the posted code so I'm sure error is in the part of code you've not posted!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rnavarro
Starting Member

10 Posts

Posted - 2012-09-11 : 16:36:35

sorry mea culpa
this is the procedure

USE [Haony]
GO
/****** Object: Trigger [dbo].[SY_tr_Audit_TLA_SG2Org_Ins] Script Date: 09/11/2012 17:35:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[SY_tr_Audit_TLA_SG2Org_Ins] ON [dbo].[TLA_SG2Org] FOR INSERT NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON
DECLARE @ValidRun INT, @RowsAffected INT, @PreExistingID INT, @MaxID INT, @AuditedUser sysname, @AuditID int
declare @emp varchar(20)
SELECT @RowsAffected = COUNT(*) FROM INSERTED
IF( @RowsAffected > 0 )
BEGIN
SELECT @AuditedUser = NULL
exec dbo.pr_kr_AuditAutoKey 'AuditLog', @AuditID output
INSERT dbo.AuditLogDetail( AuditLogID, RowKey, ColumnID, Status, NewValue )
SELECT Distinct
@AuditID,
'Sgroup=' + CONVERT(VARCHAR, [Sgroup]) ,
0 ,
0 ,
CONVERT(NVARCHAR, [Sgroup])
FROM
INSERTED
set @emp=(select [codeorg] from INSERTED)
EXEC dbo.pr_kr_AuditLogTrailer @AuditID, 384, @RowsAffected, 'I', @AuditedUser,@emp
END
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 16:53:03

hmm...this is a trigger not a procedure and issue is with this statement

set @emp=(select [codeorg] from INSERTED)

you cant store resultset in a variable
INSERTED will have multiple records in case of batch DML operation (in this case INSERT)
so if you want to store it you should be using table variable instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources