 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Programming / SQL / July 2008Table Creation
Hi I have query like this Declare @var nvarchar(max) SET @var= 'SELECT A.Customer_Name, A.Address1, A.Address2, B.city, B.state, B.pincode, c.age, ''2008-07-31'' AS DATE FROM book1_238 A, book2_238 B, book3_238 C WHERE A.customer_id = B.customer_id AND B.customer_id = C.customer_id AND A.pincode = B.Pincode AND B.Pincode = C.Pincode' EXEC SP_EXECUTESQL @VAR After run this query i got result set like Name1 Address1 Address2 Chennai Tamil Nadu 5744856 85 2008-07-31 Name2 Address2 Address3 Madurai Tamil Nadu 57448562 80 2008-07-31 Name3 Address3 Address4 Mysore Karnataka 570007 79 2008-07-31 Name4 Address4 Address5 Bangalore Karnataka 570007 52 2008-07-31 Name5 Address5 Address6 Belgum Karnataka 570006 74 2008-07-31 Name6 Address6 Address7 Delhi Delhi 5700085 64 2008-07-31 Name7 Address7 Address8 Ramnagar Karnataka 570042 41 2008-07-31 I want to create table for above result set while run time please help on this thanks raj Hi What is a table name? What is the 570007 79 2008-07-31? What is the datatype INT,VARCHAR(n)...? > Hi > [quoted text clipped - 38 lines] > thanks > raj > Hi > What is a table name? What is the 570007 79 2008-07-31? What is the datatype [quoted text clipped - 46 lines] > > thanks > > raj How to insert above result into table Note:- Based on the result set we have to create table 57000779 INT or Numeric 2008-07-31 Datetime Thanks Raj Where is the column for date? The idea is to build a daynamic sql and execute an output Untested Declare @var nvarchar(max) SET @var= 'SELECT +' INSERT INTO tblname VALUES (A.Customer_Name,A.Address1,A.Address2) VALUES (B.city,B.state,B.pincode,c.age ) ' +FROM book1_238 A, book2_238 B, book3_238 C WHERE A.customer_id = B.customer_id AND B.customer_id = C.customer_id AND A.pincode = B.Pincode AND B.Pincode = C.Pincode' EXEC SP_EXECUTESQL @VAR >> Hi >> What is a table name? What is the 570007 79 2008-07-31? What is the [quoted text clipped - 57 lines] > Thanks > Raj Or Run SELECT * INTO #tmp FROM A.Customer_Name, A.Address1, A.Address2, B.city, B.state, B.pincode, c.age, ''2008-07-31'' AS DATE FROM book1_238 A, book2_238 B, book3_238 C WHERE A.customer_id = B.customer_id AND B.customer_id = C.customer_id AND A.pincode = B.Pincode AND B.Pincode = C.Pincode > Where is the column for date? > [quoted text clipped - 80 lines] >> Thanks >> Raj > Or > [quoted text clipped - 101 lines] > >> Thanks > >> Raj I got error near From statement thanks raj Sorry , try this SELECT A.Customer_Name, A.Address1, A.Address2, B.city, B.state, B.pincode, c.age, ''2008-07-31'' AS DATE INTO #tmp FROM book1_238 A, book2_238 B, book3_238 C WHERE A.customer_id = B.customer_id AND B.customer_id = C.customer_id AND A.pincode = B.Pincode AND B.Pincode = C.Pincode >> Or >> [quoted text clipped - 106 lines] > thanks > raj > Sorry , try this > [quoted text clipped - 133 lines] > > thanks > > raj Hi Uri the query is dynamic how i will include the part of query before From statement its not possible Its dynamically generates thanks raj (snip) >the query is dynamic how i will include the part of query before From >statement its not possible > >Its dynamically generates Hi raj, It's possible, you'll just have to make sure that you generate all the parts of the SQL in the correct order. However, make sure to read this first: http://www.sommarskog.se/dynamic_sql.html  SignatureHugo Kornelis, SQL Server MVP My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis On 31 Juli, 12:48, rajdak...@gmail.com wrote: > > Hi > > What is a table name? What is the 570007 79 2008-07-31? What is the datatype [quoted text clipped - 58 lines] > > - Visa citerad text - SET @var= 'SELECT A.Customer_Name, A.Address1, A.Address2, B.city, B.state, B.pincode, c.age, ''2008-07-31'' AS DATE FROM book1_238 A, book2_238 B, book3_238 C WHERE A.customer_id = B.customer_id AND B.customer_id = C.customer_id AND A.pincode = B.Pincode AND B.Pincode = C.Pincode' create table #temp( Name varchar(30), Address1 varchar(30), Address2 varchar(30), x1 varchar(30), x2 varchar(30), Number1 int, Number2 int, Somedate Datetime ) INSERT INTO #temp EXECUTE(@var) Hi The result set in not predefined whether the columns less for first time it may be counting high next time Note I got only the query string based on the string i have t insert into table thanks raj On Jul 31, 7:14 am, rajdak...@gmail.com wrote: > Hi > [quoted text clipped - 6 lines] > thanks > raj You may want to rethink this design; while I appreciate the need for dynamic SQL in specific situations, I'd be nervous about implementing a solution where a string is passed to a stored proc and executed. What happens if soemone executes your stored procedure with a DROP TABLE command? Stu > Note I got only the query string based on the string i have t insert > into table You mean someone on your web site is telling you the SQL to use to create a table? Well that sounds perfectly safe. <smacks forehead> |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.