can any one just help help me here i am having 1 table with 1000 of record from that table data i want insert data to five table?
-
here i the table which is having records
CREATE TABLE [dbo].[tblMemberDatadetail](
[Id] [int] IDENTITY(1,1) NOT NULL,
[BusinessUrlId] [int] NULL,
[BusinessDetailId] [int] NULL,
[MemberId] [int] NULL,
[categoryid] [int] NULL,
[BusinessName] [varchar](200) NULL,
[CategoryName] [varchar](200) NULL,
[State] [varchar](20) NULL,
[TotalBlocks] [int] NULL,
[Locality] [varchar](200) NULL,
[ContactPerson] [varchar](200) NULL,
[PhoneNumbers] [varchar](8000) NULL,
[Address] [varchar](8000) NULL,
[CreatedOn] [datetime] NULL,
[Email] [varchar](8000) NULL,
[Website] [varchar](8000) NULL,
which is having data
select * from dbo.tblMemberDatadetail
out put is
Id BusinessUrlId BusinessDetailIdMemberId categoryid BusinessNameCategoryNameStateTotalBlocksLocalityContactPersonPhoneNumbersAddressCreatedOnEmailWebsite
120617321101JP HotelrestaurantsCHE4Koyambedu1131, Inner Ring Road, Koyambedu,Chennai - 600107044-6688808008:19.9contact@hoteljpchennai.comhttp://www.hoteljpchennai.com
220618322101The Pasta Bar VenetorestaurantsCHE4T NagarMr Vinodh N Sharma32/59, Burkit Road, T Nagar,Chennai - 6000179176720005,044-4212668008:19.2http://www.thepastabarveneto.com
320619323101Le Sandz RestaurantrestaurantsCHE4VelacheryMrs Sridevi Thiagarajan15, 3rd Cross Street, Vijayanagar, Velachery,Chennai - 6000429841427677,044-22592999,2259333008:19.5lesandz.restaurant@gmail.com
420620324101Cream En CreamyrestaurantsCHE4ArumbakkamMr I Surender Singh25, Jaganathan Nagar, Ist Main Road, Arumbakkam,Chennai - 6001069884029977,044-4555403008:19.6softking@hotmail.comhttp://www.softicecreammachines.net
520621325101Madras Craft FoundationrestaurantsCHE4Besant NagarMrs Vishalam RamanathanG-3, Madhuram Flats, 23, Urur Olcott Road, Besant Nagar,Chennai - 6000909841423149,044-2446243508:19.7mcfdak@md3.vsnl.net.inhttp://www.dakshinachitra.net
620622326101Doveton CaferestaurantsCHE4PurasaivakkamMr Ramesh5, Purasawalkam High Road, Purasaivakkam,Chennai - 6000079841910117,044-2642631308:19.7dovetoncafe@gmail.com
720623327101Cine City HotelsrestaurantsCHE4KodambakkamMr S Rajkumar#28, 1st Main Road, United India Colony, Kodambakkam,Chennai - 6000249894206007,9003010607,9283420335,044-24840840,24838379,24838379,4558833308:19.8gm.srk_cinecity@yahoo.co.inemail info@cinecityhotels.comhttp://www.cinecityhotels.com
for ur convinent i mentioned one insert query
INSERT INTO [tblMemberDatadetail]
([BusinessUrlId],[BusinessDetailId],[MemberId],[categoryid],[BusinessName]
,[CategoryName],[State],[TotalBlocks],[Locality],[ContactPerson],[PhoneNumbers]
,[Address],[CreatedOn],[Email],[Website])
VALUES
(1,1,2,2,'football','sports','hy',4,'sert','john','984355,8567532,564321','no16,primaryblock,ft road hy-867456321',GETDATE(),
'ser@gmail.com,asert@yahoo.com','www.tre.com')
GO
then iam having 2 static table
create table category
(
catid int,categoryname varchar(100)
)
insert into category
select 1,'cricket' union all
select 2,'football'
create table contacttype
(
contactId int,datas varchar(100)
)
insert into category
select 1,'locality' union all
select 2,'phone1' union all
select 3,'phone2' union all
select 4,'phone3' union all
select 5,'phone4' union all
select 6,'landmark' union all
select 7,'email1' union all
select 8,'email2' union all
select 9,'email3' union all
select 10,'pincode' union all
select 11,'address' union all
by using this three table i want to insert datas to the following tables
CREATE TABLE [dbo].[tblSpace](
[SpaceId] [int] IDENTITY(1,1) NOT NULL,
[MemberId] [int] NULL,
[AdSpaceName] [varchar](200) NULL,
[TotalBlocks] [int] NULL,
[PaymentType] [smallint] NULL
)
CREATE TABLE [dbo].[tblAdInf](
[AdInfId] [int] IDENTITY(1,1) NOT NULL,
[SpaceId] [int] NULL,
[BusinessName] [varchar](200) NULL,
[Notes] [varchar](500) NULL
)
CREATE TABLE [dbo].[tblAppCat](
[AppCatId] [int] IDENTITY(1,1) NOT NULL,
[AdInfId] [int] NULL,
[CategoryId] [int] NULL
)
CREATE TABLE [dbo].[tblContactInf](
[ContactInfId] [int] IDENTITY(1,1) NOT NULL,
[AdInfId] [int] NULL,
[ContactId] [int] NULL,
[datas] [varchar](200) NULL
)
the output should be like this for these data which was inserted
(1,1,2,2,'football','sports','hy',4,'sert','john','984355,8567532,564321','no16,primaryblock,ft road hy-867456321',GETDATE(),
'ser@gmail.com,asert@yahoo.com','www.tre.com')
[SpaceId] [MemberId] [AdSpaceName] [TotalBlocks] [PaymentType]
1 2 football 4 NULL
[AdInfId] [SpaceId][BusinessName] [Notes]
1 1 football NULL
[AppCatId] [AdInfId] [CatId]
1 1 1
[ContactInfId] [AdInfId] [ContactId] [datas]
1 1 1 sert
2 1 2 984355
3 1 3 8567532
4 1 4 564321
5 1 7 ser@gmail.com
6 1 8 asert@yahoo.com
7 1 10 867456321
8 1 11 no16,primaryblock,ft road hy
-
You started with posting some really useful ddl and sample data. Then unfortunately you lost it. You have a number of tables that have some sort of ID but no clue which column they come from in your original table. I would recommend that you create views instead of duplicating this data into multiple locations. The way you have this laid out updates are going to be incredibly painful, if not totally impossible.
Whichever path you chose, you are going to have to map columns in the source table to columns in the destination. Nobody here can help you with that.
The basic syntax of what you need is:
insert DestinationTable [columns]
Select [columns]
from SourceTable
You have to figure out what columns go where.
_______________________________________________________________
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/ -
for example
here i inserted five value form the source table for example iam start in from the of the first table to insert with i dentity column
output of table [tblSpace]
[SpaceId] [MemberId] [AdSpaceName] [TotalBlocks] [PaymentType]
56 2 bus 4 NULL
57 2 bus1 4 NULL
58 2 bus2 4 NULL
and
how i want insert the data [tblAdInf] which was inserted in [tblSpace] and [tblMemberDatadetail](source table)
[AdInfId] [SpaceId][BusinessName] [Notes]
71 56 football NULL
72 57 ser1 NULL
73 58 ser2 NULL
i just mentioned this as a example i want to insert 100 for record in [tblAdInf] at a time if i insert one by one means i will set @@identity in adspace table and i will insert manually [SpaceId] is a foreginkey for [tblAdInf]
so plz give one idea
-
Here is my suggestion. Build a map showing how the data from the source table maps to the target tables. You can do this using a variety of tools, including (but not limited to) Excel or Visio.
Once you see how the data needs to move from source to destination, you can start to figure out how to do it using SQL.
-
like this only i transfer data lynn for the first table
insert into [dbo].[tblSpace] (MemberId, AdSpaceName, TotalBlocks, PaymentType)
SELECT
t1.MemberId,
NULL, -- for [AdSpaceName] (data not provided)
t1.TotalBlocks,
NULL -- for [PaymentType] (data not provided)
FROM tblMemberDatadetail t1
spaceid will be foregin key for [tblAdInf]
-
Sorry, not getting your question completely. As per the scenario given by you(simplifying):
Table_1
[SpaceId] [MemberId] [AdSpaceName] [TotalBlocks] [PaymentType]
56 2 bus 4 NULL
57 2 bus1 4 NULL
58 2 bus2 4 NULL
Table_2
[AdInfId] [SpaceId][BusinessName] [Notes]
71 56 football NULL
72 57 ser1 NULL
73 58 ser2 NULL
1) you have inserted data in Table_1 where SpaceId is identity
2) you want to insert data in Table_2 where AdInfId is identity, but you want your SpaceId (identity value) from table_1 to be inserted in SpaceId column of Table_2
Is this what you are looking for??
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
sivag,
I don't think you understand. We can't give you the code to move data from one table to five tables without knowing the how the data maps between the source data and the destination tables. We don't know if there needs to be computations done on the data during the transfer from the source table to the destination tables. You haven't provided us with the information we need.
We have told you that you need to develop the map between the source table and the destiniation tables. This will help you understand what needs to be done as well. It will help you determine if any calculations are needed during the transfer of data.
There really isn't much more that we can do from here. We can't see what you see, and you aren't giving us what we need to understand what it is you need to accomplish other than you want to move data from Table A to Tables V,W,X,Y, and Z.
-
insert into [dbo].[tblSpace] (MemberId, AdSpaceName, TotalBlocks, PaymentType)
SELECT
t1.MemberId,
t1BusinessName as [AdSpaceName]
t1.TotalBlocks,
NULL -- for [PaymentType] (data not provided)
FROM tblMemberDatadetail t1
insert into [dbo].[tblAdInf] (SpaceId,BusinessName,Notes)
SELECT
t2.SpaceId,
t1.BusinessName
'null' as notes
FROM tblMemberDatadetail t1
inner join [tblSpace]
on t1.BusinessName =t2.AdSpaceName
-
Not sure about Lynn but my crystal ball is still broken. We can't see your screen, we have no idea how you want to map these columns, we still don't have any idea how this data relates to the other tables.
Try think link and see if it helps.
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
_______________________________________________________________
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/ -
from the above reply will tell u how iam getting values for the two table
for this i can get adinforid
here i wrote a function to split a emails in memberdetails
SELECT* FROM [dbo].[characterSplit1] ('a@gmail.comemail b@gmail.co.inemail c@gmail.com','email ')
ALTER FUNCTION [dbo].[characterSplit1](@String varchar(8000), @Delimiter char(5))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
--declare @String varchar(8000)
--declare @Delimiter varchar(8000)
--set @String='a@gmail.comemail b@gmail.co.inemail c@gmail.com'
--set @Delimiter='email'
select @idx = 6
if len(@String)<6 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
-- select @idx Idx
if @idx!=0
Begin
set @slice = left(@String,@idx -1)
Select @slice= replace(@slice,'mail ','')
--Select @String IfString
End
else
Begin
set @slice = @String
Select @slice=replace(@slice,'mail ','')
-- Select @String elsestring
End
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
-- Select @slice mail
-- select @String BeforeFinal
set @String = right(@String,len(@String) - (@idx))
--select @String FinalString
if len(@String) = 0 break
end
return
end
select
s.[AdInfId]
,ds.items
from tblAdSpace s
left join
[dbMemberdetail].[dbo].[tblMemberDatadetail] a
on
a.BusinessName=s.BusinessName
CROSS APPLY dbo.characterSplit1(a.Email,'Email')ds
order by s.[AdInfId]
the output will be like this
AdInfIditems
how i have to insert this vaue with combined of static table
tblcontacttype
as
7,'email1'
8,'email2'
9,'email3'
in the table [tblContactInf]
as the output iam expecting
AdInfId contactId datas
-
Is this a blog post or a request for help?
Your splitter is not the most optimal. Take a look at the link in my signature for splitting strings.
That aside the details are totally lacking and clarity and the details keep changing. You have been asked repeatedly to provide details and you continue not to. Best of luck mapping this out. I am done.
_______________________________________________________________
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/ -
Sean Lange (7/23/2012)
Not sure about Lynn but my crystal ball is still broken. We can't see your screen, we have no idea how you want to map these columns, we still don't have any idea how this data relates to the other tables.Try think link and see if it helps.
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
My crystal ball is in the shop for repairs, has been for years and I don't expect to get it back for years to come.
Here is the problem as I see it. We simply aren't speaking the same version of English.
If, sivag, you really want our help you need to do a lot of homework.
We need the DDL (that means the CREATE TABLE statements) for each of your tables. We need a clear map showing where each column from the source table goes to each column of the destination tables. This mapping needs to include any transformations, calculations, modifications, etc needed to the data to get it into the source table.
This is what we have been asking you for since you asked for help.
What you have posted so far, sorry it just hasn't been helpful. I don't know your tables, or the data stored in them, nor what changes need to be made to move the data from source to destination. You know these things and I am sorry to say that you just haven't communicated the information we need in a format that we are able to comprehend.
-
sorry i didn't under stand what kind of detail you are asking from me
-
plz tel me what are the requirement u need from me
-
sivag (7/23/2012)
plz tel me what are the requirement u need from meWe don't even know what the question is yet. You talk about making 1 table into 5. We don't know what you want. We can't see your screen. We can't read your mind.
It appears there is some of relationship between these tables but we have no idea what that might be.
Now all of a sudden you bring a string splitter into the discussion. Step back and read what you posted and ask yourself if you think YOU could answer your questions based on what you posted.
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply