Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Is it possible to insert any array directly to table using Insert statement
 

If yes then let me know...
 
I don't want to create any function ...
i just want to use simple sql query...
Posted 17-Jan-13 1:01am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You could insert all the elements of a delimiter limited string. However, I don't think that would be of much help.
It would be best to insert the elements of the array one by one into a table.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

No, not as an array of values intended for different rows - SQL doesn't have any concept of arrays, so it won't understand them. It is possible (though a PITA to do this via a string made up of comma delimited values, but that is best done as a stored procedure, rather than by an INSERT statement.
 
If it is an array of bytes that you want into a varBinary field on the other hand, then yes - pass the array content to SQL as a parameter value and it will work fine. (Exactly how you do that depends on your coding language)
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

for that you have to create query like that:
assume you have ArrayList arrList having 5 employeename so you can create query like
 string strQuery = "Insert Into YourTableName (YourColumnName) values (";
 for(int i=0; i<arrlist.count;i++)>
 {
   strQuery += arrList[i];
   if(i<arrlist.count-1)>
       strQuery += ",";
 }
 
 now use this query so that you can insert multiple value in single insert statement.
  Permalink  
v2
Comments
RedDK at 17-Jan-13 15:27pm
   
Big C++ fan myself but the tags are uniquely SQL ... see my solution instead.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

This is what I do, regularly and with increasing frequency:
CREATE TABLE [solutionfour].[test].[theanswer](
	[text] [nvarchar](MAX)
	)
				
CREATE TABLE [solutionfour].[test].[theanswerIdx](
	[idx][int]IDENTITY(1,1) NOT NULL,
		[text] [nvarchar](MAX)
		)
 
BULK INSERT [solutionfour].[test].[theanswer] 
	FROM 'C:\arrayMine.txt'							
		WITH (
			--CODEPAGE = 'RAW'
			CODEPAGE = 'ACP' 
			)
 
INSERT INTO [solutionfour].[test].[theanswerIdx]
	SELECT [text] FROM [solutionfour].[test].[theanswer]
 
Then go to work. See help for TSQL string commands and info on casting and converting as needed ...
 
Any further questions?
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

The relational model itself is dealing with multi-value attributes. Most of the relational database management systems are targeting the 3NF[^]. But not all, Firebird for example is supporting array data types[^].
SQL Server is not supporting such data type, but if you really need it, you can use several tools to emulate this feature. This is a really good collection of them: http://www.sommarskog.se/arrays-in-sql-2005.html[^]
I, for myself would prefer the XML approach.
  Permalink  

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

  Print Answers RSS
0 Afzaal Ahmad Zeeshan 256
1 OriginalGriff 251
2 BillWoodruff 240
3 Maciej Los 230
4 Sergey Alexandrovich Kryukov 195
0 OriginalGriff 6,419
1 Sergey Alexandrovich Kryukov 6,008
2 DamithSL 5,183
3 Manas Bhardwaj 4,673
4 Maciej Los 4,070


Advertise | Privacy | Mobile
Web04 | 2.8.1411019.1 | Last Updated 17 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100