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?
VBBRettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You need to CAST the number as a char data type, then concatenate it with the rest of the string.
INSERT INTO YourTable (YourColumn) 
SELECT CAST(1 as char(1)) + ', maranello'

Open in new window

0
David ToddSenior DBACommented:
Hi,

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
0
Chris LuttrellSenior Database ArchitectCommented:
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.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

VBBRettAuthor Commented:
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)
0
David ToddSenior DBACommented:
Hi,

We still don't know enough about what you are trying to achieve and where exactly you've gotten to.

So, the basics then.

insert dbo.Name( Nametype, NameString ) values( 1, 'maranello' )
insert dbo.Name( Nametype, NameString ) values( 1, 'Sicily' )

The results should be
1 row(s) affected

1 row(s) affected

Or you can do this:
insert dbo.Name( Nametype, NameString )
    select 1, 'maranello'
    union all select 1, 'Sicily'

The results should be
2 row(s) affected

HTH
  David
0
Chris LuttrellSenior Database ArchitectCommented:
Some sample code showing some ways to do this:
-- a sample table like you described
CREATE TABLE someTable (NameType INT, NameString NVARCHAR(MAX));

-- Example 1, from a single variable passed in or something:
DECLARE @BigString NVARCHAR(MAX)
SET @BigString = '1, maranello'

INSERT INTO dbo.someTable
        ( NameType, NameString )
VALUES  ( SUBSTRING(@BigString, 1, CHARINDEX(',',@BigString)-1), -- Start at begining and go to 1 position before the comma
          SUBSTRING(@BigString, CHARINDEX(',',@BigString)+1,LEN(@BigString))  -- Find the comma and start at the next position till end
          )
-- another value
SET @BigString = '1, Sicily'

INSERT INTO dbo.someTable
        ( NameType, NameString )
VALUES  ( SUBSTRING(@BigString, 1, CHARINDEX(',',@BigString)-1), -- Start at begining and go to 1 position before the comma
          SUBSTRING(@BigString, CHARINDEX(',',@BigString)+1,LEN(@BigString))  -- Find the comma and start at the next position till end
          )

SELECT *
from dbo.someTable


-- Example 2, say those values are in another table or you can load them into a table easily, but just the one big string
CREATE TABLE ValueTable (BigString NVARCHAR(MAX));
INSERT INTO ValueTable (BigString)
VALUES
('1, maranello'),
('1, Sicily')

-- Now you can use similar logic to read from the source table spliting the values into the columns in the new table
INSERT INTO dbo.someTable
        ( NameType, NameString )
SELECT  SUBSTRING(BigString, 1, CHARINDEX(',',BigString)-1), -- Start at begining and go to 1 position before the comma
          SUBSTRING(BigString, CHARINDEX(',',BigString)+1,LEN(BigString))  -- Find the comma and start at the next position till end
FROM ValueTable

SELECT *
from dbo.someTable


DROP TABLE dbo.someTable
DROP TABLE dbo.ValueTable

Open in new window

Resulting in this with either exampleResults of queryAnd again as David has pointed out, we still do not have a lot to go on here, we are shooting in the dark.  
Beter details yield better results.
Chris
0
VBBRettAuthor Commented:
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:

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]

GO

Open in new window


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!
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Based on the above here's how you would display everything:

SELECT ModelID, MakeId, CAST(MakeID as varchar(3)) + ', ' + Make as Model
FROM Model

Open in new window

>you would need the following to be entered into the database:
It's still not clear to me what 'into the database' means, as the schema of the data you are working with, and the desired action, needs to be laid out better.  Are you trying to just display these values (i.e. SELECT), or populate a different table with the values (i.e. INSERT)?

If Insert, based on the last sample data, that would be something like...
INSERT INTO SomeOtherTable (ModelID, MakeID, Model) 
SELECT ModelID, MakeID, Model
FROM Model

Open in new window

0
VBBRettAuthor Commented:
hello JimHorn, I am just trying to insert values into the table mentioned above.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
VBBRettAuthor Commented:
OK jimhorn, give me a few minutes to post.
0
Chris LuttrellSenior Database ArchitectCommented:
I understand you are trying to Insert into your Model table.  As Jim stated, what is your source?  I supplied 2 examples above for single value insertion and a mass insertion from another table with a single string value as soruce.  Neither of those must be exactly what you are wanting to do.  
The problem with any business logic type question in IT is there are usually many ways to get the end results (I have supplied 2 already) but when it comes to the "Right" solution the standard answer "It Depends?" always comes into play.  
It depends on:
Where your source data is coming from?
Is this a one time load or needing to be a repeatable process?
What tools do you have access to and the knowledge to use?  
And there are probably others depending on how the first answers go.  
"I have a spread sheet I need to periodically read from and insert into this table using an OPENQUERY"
"I want to use Integration Services to load this data from a flat file into my table."
"I have a spread sheet I need to read from this one time and insert into this table using the SQL Server Import and Export Wizard"
"I am writing an application that users will be entering in one record at a time and I need to persist their record in the database via a call to a Stored Procedure."
etc.
No one here is trying to be difficult or dense, there is just not "1 simple clean cut answer" that would apply in all cases, that is why everyone is asking you for details.
0
VBBRettAuthor Commented:
Ok, the source table has the following columns:

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]

Open in new window


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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Allow me to rephrase, and state that this question is running the risk of being abandoned...

State for us in abundantly simple terms the schema of the source table(s) and destination table, for ONLY the parts that are relevent to this question, as the volume of extra information here makes it hard to determine.

Source is the table(s) where the data is now.
Destination is where you wish to insert, meaning add, the source data.
So you need to show us at least two tables.
0
VBBRettAuthor Commented:
Jimhorn, are you asking me to post the table columns in the source table and the table columns in the target table?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yes.  And no other columns not relevant to this question.
0
VBBRettAuthor Commented:
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.
0
Chris LuttrellSenior Database ArchitectCommented:
I think you are looking for something like this to create your Model table with the distinct values of Model from your source table tied by Foriegn Key to a Make table that I assume already exists.  Let me know if this is closer to what you are looking for.
INSERT INTO dbo.Model
        ( MakeId, Model )
SELECT DISTINCT Make.MakeId, cmf.model_name 
FROM dbo.car_models_full cmf
INNER JOIN dbo.Make Make ON cmf.model_make_id = Make.MakeName

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VBBRettAuthor Commented:
BRILLIANT GGLuttrell!
0
Chris LuttrellSenior Database ArchitectCommented:
Thanks, glad to help.
Chris
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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'.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.