March 8, 2011 at 11:49 am
Given the output table:
TABLE [dbo].[tblFinancialContact](
[FinancialContactID] [int] NOT NULL,
[ProviderID] [int] NULL,
[FinancialContactPrefix] [nvarchar](4) NULL,
[FinancialContact] [nvarchar](50) NULL,
[FinancialPosition] [nvarchar](50) NULL,
[FinancialPhone] [nvarchar](14) NULL,
[FinancialExtension] [nvarchar](4) NULL,
[FinancialFax] [nvarchar](14) NULL,
[FinancialEmail] [nvarchar](50) NULL,
CONSTRAINT [PK_tblFinancialContact] PRIMARY KEY CLUSTERED
(
[FinancialContactID] ASC
)
I need to create an sql to read from one table the values and insert into the above table.
Here is the input table:
CREATE TABLE [dbo].[tblServiceProvider](
[ProviderID] [int] NOT NULL,
[FinancialContactPrefix] [nvarchar](4) NULL,
[FinancialContact] [nvarchar](50) NULL,
[FinancialPosition] [nvarchar](50) NULL,
[FinancialPhone] [nvarchar](14) NULL,
[FinancialExtension] [nvarchar](4) NULL,
[FinancialFax] [nvarchar](14) NULL,
[FinancialEmail] [nvarchar](50) NULL,
[NewProvider] [bit] NOT NULL,
CONSTRAINT [PK_tblServiceProvider] PRIMARY KEY CLUSTERED
(
[ProviderID] 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
I need to also increment the values start at 0 for the financialcontactid
providerid will join the table.
Any help would be awesome.
March 8, 2011 at 11:58 am
Generally, you can use INSERT INTO.... SELECT (col1, col2) FROM syntax. See Books Online (BOL) for the detailed syntax and requirements for the columns to specify and their values.
As for the auto incrementing value starting at zero, make that field a 'Identity' with the seed value of 1 (counts by one) and starting at 0. See BOL for how to specify that column as an Identity.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 8, 2011 at 12:57 pm
I need to do an insert.
create your ID column as identity column :
but this column is not an idenity column
is there a way I can set it to identity and the then set off
or if there is a way I can use a counter.
[FinancialContactID] int identity (1,1)
INSERT INTO tblFinancialContact (
[ProviderID],
[FinancialContactPrefix],
[FinancialContact],
[FinancialPosition],
[FinancialPhone],
[FinancialExtension] ,
[FinancialFax] ,
[FinancialEmail]
)
SELECT
[ProviderID],
[FinancialContactPrefix],
[FinancialContact],
[FinancialPosition],
[FinancialPhone],
[FinancialExtension] ,
[FinancialFax] ,
[FinancialEmail]
FROM tblServiceProvider
Any help would be awesome.
March 8, 2011 at 1:21 pm
Declaring the column as an identity column will allow it to continue to add sequential values when new records are added, so you probably will not want to turn it off.
That said, you can look up IDENTITY_INSERT in BOL; you can SET IDENTITY_INSERT ON to add whatever values you want to an identity column, and then SET IDENTITY_INSERT OFF when you're done.
Beware of doing this into existing datasets, as it has the potential to mess up your sequencing if you're not careful.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply