Click here to Skip to main content
Sign Up to vote bad
good
See more: SQL2005SQL2008
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 - 0:01


5 solutions

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  
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  
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  
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  
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  
Comments
RedDK - 17 Jan '13 - 15:27
Big C++ fan myself but the tags are uniquely SQL ... see my solution instead.

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 409
1 Mahesh Bailwal 353
2 Maciej Los 260
3 CPallini 245
4 Aarti Meswania 173
0 Sergey Alexandrovich Kryukov 9,162
1 OriginalGriff 7,194
2 CPallini 3,923
3 Rohan Leuva 3,176
4 Maciej Los 2,633


Advertise | Privacy | Mobile
Web03 | 2.6.130516.1 | Last Updated 17 Jan 2013
Copyright © CodeProject, 1999-2013
All Rights Reserved. Terms of Use
Layout: fixed | fluid