Click here to Skip to main content
12,064,368 members (44,272 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-server-2005 SQL-Server , +
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 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web04 | 2.8.160204.4 | Last Updated 17 Jan 2013
Copyright © CodeProject, 1999-2016
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