VBBRett
asked on
How to Insert a numerical value along a string
I'd like to insert a numerical value along a string value into a table into a database. However, I have over 150 values. Here is an example:
1, maranello
1, Sicily
So when entering these values, I want a number first and a string second into the database. Besides manually inserting into a SQL table, how could I get this to be a quick insert?
1, maranello
1, Sicily
So when entering these values, I want a number first and a string second into the database. Besides manually inserting into a SQL table, how could I get this to be a quick insert?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As David asked, what is your source and what is the desired results in the database? (number of columns and datatype(s) )
Are you trying to concatenate a number and string like Jimhorn demonstrated, or is your example the source and you are trying to split it into 2 columns of different datatypes?
There are many ways to go about this and they depend on your exact situation, please provide some more details.
Are you trying to concatenate a number and string like Jimhorn demonstrated, or is your example the source and you are trying to split it into 2 columns of different datatypes?
There are many ways to go about this and they depend on your exact situation, please provide some more details.
ASKER
Desired data type is an int column for the first field and the second column should be an nvarchar. So it would be the following:
Table - Name
--------------------
Nametype Int,
NameString Nvarchar(max)
Table - Name
--------------------
Nametype Int,
NameString Nvarchar(max)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, let me be a little more specific. The table is called Model as in a car model. ModelId is the primary key and it increments by 1 as a new record is inserted since it is the identity. As far as the MakeId, it is a foreign key from another table. To give an example,
MakeId Make
----------- ------------------
47 Ferrari
Since the make or brand is in alphabetic order, Ferrari comes in 47th place. Now since I need the MakeId foreign key to be on the Model table so I know what Model belongs to a specific brand, I use the MakeId column as a foreign key.
Now below is the Model table. Here is the table design creation below:
Obviously ModelId will increment by one since it is the primary key/identity. Since Ferrari would have several specific models, you would need the following to be entered into the database: NOTE: ModelId is a primary key and is not to be edited or changed.
ModelId MakeId Model
------------- ------------- -------------
1 47 250 Dino
2 47 Testarossa
3 47 F355
4 47 550 Maranello
5 47 575 Maranello
6 47 F40
7 47 F50
8 47 360 Modena
9 47 Enzo
10 47 SuperAmerica
I hope this example above shows what I am looking for. How would I insert the above into a database or how could it be inserted into a specific table without the mundane insert every new row. Thanks!
MakeId Make
----------- ------------------
47 Ferrari
Since the make or brand is in alphabetic order, Ferrari comes in 47th place. Now since I need the MakeId foreign key to be on the Model table so I know what Model belongs to a specific brand, I use the MakeId column as a foreign key.
Now below is the Model table. Here is the table design creation below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Model](
[ModelId] [int] IDENTITY(1,1) NOT NULL,
[MakeId] [int] NOT NULL,
[Model] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Model] PRIMARY KEY CLUSTERED
(
[ModelId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOObviously ModelId will increment by one since it is the primary key/identity. Since Ferrari would have several specific models, you would need the following to be entered into the database: NOTE: ModelId is a primary key and is not to be edited or changed.
ModelId MakeId Model
------------- ------------- -------------
1 47 250 Dino
2 47 Testarossa
3 47 F355
4 47 550 Maranello
5 47 575 Maranello
6 47 F40
7 47 F50
8 47 360 Modena
9 47 Enzo
10 47 SuperAmerica
I hope this example above shows what I am looking for. How would I insert the above into a database or how could it be inserted into a specific table without the mundane insert every new row. Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hello JimHorn, I am just trying to insert values into the table mentioned above.
State for us in abundantly simple terms the schema of the source table(s) and destination table, as the volume of extra information here makes it hard to determine.
ASKER
OK jimhorn, give me a few minutes to post.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, the source table has the following columns:
Please see the example of the data of a select * from car_models_full where model_make_id = 'ferrari' displays the data attached.
dataexample.jpg
CREATE TABLE [dbo].[car_models_full](
[model_id] [int] NULL,
[model_make_id] [nvarchar](255) NULL,
[model_name] [nvarchar](255) NULL,
[model_trim] [nvarchar](255) NULL,
[model_year] [int] NULL,
[model_body_style] [nvarchar](255) NULL,
[model_engine_position] [nvarchar](255) NULL,
[model_engine_cc] [float] NULL,
[model_engine_num_cyl] [float] NULL,
[model_engine_type] [nvarchar](255) NULL,
[mode_engine_valves_per_cyl] [float] NULL,
[model_engine_power_ps] [float] NULL,
[model_engine_power_rpm] [float] NULL,
[model_engine_torque_nm] [float] NULL,
[model_engine_torque_rpm] [float] NULL,
[model_engine_bore_mm] [float] NULL,
[model_engine_stroke_mm] [float] NULL,
[model_engine_compression] [nvarchar](255) NULL,
[model_engine_fuel] [nvarchar](255) NULL,
[model_top_speed_kph] [int] NULL,
[model_0_to_100_kph] [float] NULL,
[model_drive] [nvarchar](255) NULL,
[model_transmission_type] [nvarchar](255) NULL,
[model_seats] [int] NULL,
[model_doors] [nvarchar](255) NULL,
[model_weight_kg] [float] NULL,
[model_length_mm] [int] NULL,
[model_width_mm] [int] NULL,
[model_height_mm] [int] NULL,
[model_wheelbase] [int] NULL,
[model_lkm_hwy] [nvarchar](255) NULL,
[model_lkm_mixed] [nvarchar](255) NULL,
[model_lkm_city] [nvarchar](255) NULL,
[model_fuel_cap_l] [int] NULL,
[model_sold_in_us] [int] NULL,
[model_co2] [nvarchar](255) NULL,
[model_make_display] [nvarchar](255) NULL
) ON [PRIMARY]Please see the example of the data of a select * from car_models_full where model_make_id = 'ferrari' displays the data attached.
dataexample.jpg
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jimhorn, are you asking me to post the table columns in the source table and the table columns in the target table?
Yes. And no other columns not relevant to this question.
ASKER
Ok, I understand jimhorn.
The source table car_models_full
all I need from that table are the following fields:
model_make_id - nvarchar(255) - this will be the field I will query against to match up the model based on the name of the make:
model_name - nvarchar(255) - this will be the field that I want to grab the model information from. This will be the field that gets inserted into the target table based on my query that I run against this table.
Target Table:
Model
--------
modelId - Primary key which will get incremented per insert
makeId - This will be a numerical value based on the Make table
Model - String value which contains the model name
Below is the Make table which will be used as a point of reference for the name of the Make
MakeId int
MakeName nvarchar(255)
so to give you an example
MakeId MakeName
---------- ------------------
47 Ferrari
I hope I was clear.
The source table car_models_full
all I need from that table are the following fields:
model_make_id - nvarchar(255) - this will be the field I will query against to match up the model based on the name of the make:
model_name - nvarchar(255) - this will be the field that I want to grab the model information from. This will be the field that gets inserted into the target table based on my query that I run against this table.
Target Table:
Model
--------
modelId - Primary key which will get incremented per insert
makeId - This will be a numerical value based on the Make table
Model - String value which contains the model name
Below is the Make table which will be used as a point of reference for the name of the Make
MakeId int
MakeName nvarchar(255)
so to give you an example
MakeId MakeName
---------- ------------------
47 Ferrari
I hope I was clear.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
BRILLIANT GGLuttrell!
Thanks, glad to help.
Chris
Chris
So ... done deal?
btw the original question title was 'How to Insert a numerical value along a string', which was answered in the first comment, and it appears that real question had to be clarified multiple times until it ended up being 'how to insert separate columns for a numerical value and a string'.
btw the original question title was 'How to Insert a numerical value along a string', which was answered in the first comment, and it appears that real question had to be clarified multiple times until it ended up being 'how to insert separate columns for a numerical value and a string'.
What is your source?
Excel can be really easy, as in write a formula that creates the insert statement, copy the column and paste as values, then copy this column into SQL and execute it.
HTH
David