July 10, 2013 at 11:11 pm
CREATE TABLE MYTABLE
(
NAME NVARCHAR(MAX),
ID INT
)
INSERT INTO MYTABLE(NAME,ID) VALUES('AAA',1)
INSERT INTO MYTABLE(NAME,ID) VALUES('AAA',2)
INSERT INTO MYTABLE(NAME,ID) VALUES('AAA',3)
so the output is,
NAME ID
AAA 1
AAA 2
AAA 3
in front end i am having checkbox list
in that i selected 3 items then the values will come like this '4,5,6'
after getting those selected value items i want to insert it into database
for example,
NAME='BBB'
IDs='4,5,6'
so my requirement is if i send these above values to MYTABLE in database
then the expected output looks like
NAME ID
BBB 4
BBB 5
BBB 6
please help me ,
your help will be appreciable
Thanks in advance,
Venkatesh Desai.k
July 11, 2013 at 12:11 am
Caps off, please. No need to shout. :ermm:
You would have to code 3 inserts
or
( since you are posting in the SQL2008 forum )
You could code a single insert and provide the values for the 3 rows:
e.g.:insert into myschema, mytable ( col1, col2 ) values ('a', 1), ('a', 2), ('a', 3);
ref: BOL Insert: http://msdn.microsoft.com/en-us/library/ms174335%28v=sql.105%29.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 11, 2013 at 12:41 am
Hi Sir,
Thanks for replying.
It is working if i write the query
INSERT INTO MYTABLE(NAME,ID) VALUES('AAA',1),('AAA',2),('AAAA',3)
so for this i write a stored procedure,
ALTER PROCEDURE MYPROCEDURE
(
@String NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @AAA VARCHAR(MAX)
SET @AAA= 'INSERT INTO MYTABLE(NAME,ID) VALUES'
SET @AAA = @AAA + @String
EXEC @AAA
END
Now when i run this i.e.,
EXEC MYPROCEDURE ('AAA',1),('AAA',2),('AAAA',3)
It is getting error like below one,
The name 'INSERT INTO MYTABLE(NAME,ID) VALUES('AAA',1),('AAA',2),('AAAA',3)' is not a valid identifier.
How to solve this issue.
July 11, 2013 at 7:30 am
If I understand what you are doing here you have 2 parameters. 1 is Name and the 2nd is a comma delimited list? I think what you need to do is look at the article in my signature about splitting strings. In there you will find the code for the DelimitedSplit8K iTVF.
This is an example of doing this based on your description.
declare @Name varchar(10) = 'BBB'
declare @Values varchar(10) = '4,5,6'
CREATE TABLE #MYTABLE
(
NAME NVARCHAR(MAX),
ID INT
)
INSERT INTO #MYTABLE(NAME,ID)
select @Name, s.Item
from dbo.DelimitedSplit8K(@Values, ',') s
select * from #MYTABLE
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2013 at 11:05 am
A word of warning, venkidesaik: One you move over to the tally table, you won't ever want to come back. 😀 It is an absolutely beautiful thing.
July 12, 2013 at 12:06 am
Sean Lange (7/11/2013)
...
This isn't only a great solution for the problem,
it also gets rid of the dynamic sql in the sproc, which is always a horrible thing you should avoid at most times.
Hence, making it all way better maintainable, securable, tunable, ...
Another thing you may use, since you're posting in a SQL2008 forum is input paremeter tables.
This will have the benifit of even avoiding the dbo.DelimitedSplit8K[/url].
Have a look at:
- http://www.sqlservercentral.com/articles/News/3182/
- http://msdn.microsoft.com/en-us/library/bb510489%28v=sql.105%29.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 12, 2013 at 5:05 am
Thank you sir for replying,
Actually it is great solution (INSERT INTO SELECT STATEMENT).
it is working fine.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply