March 8, 2011 at 12:58 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:16 pm
take a look at the Row_Number function on bol. It will get you exactly what you need.
_______________________________________________________________
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/
March 8, 2011 at 1:27 pm
SELECT ROW_NUMBER()
OVER (
SELECT
[ProviderID],
[FinancialContactPrefix],
[FinancialContact],
[FinancialPosition],
[FinancialPhone],
[FinancialExtension] ,
[FinancialFax] ,
[FinancialEmail]
FROM tblServiceProvider) AS Row
not sure how to do this?
March 8, 2011 at 1:33 pm
The Row_Number function creates a new column.
select Row_Number() over(order by someColumn) as myRowCount
, othercolumns here
from mytable
_______________________________________________________________
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/
March 8, 2011 at 1:49 pm
SELECT
(
[ProviderID],
[FinancialContactPrefix],
[FinancialContact],
[FinancialPosition],
[FinancialPhone],
[FinancialExtension] ,
[FinancialFax] ,
[FinancialEmail])
FROM dbo.tblServiceProvider)
SELECT ROW_NUMBER()
OVER (ORDER BY ProviderID) AS Row
stuck
March 8, 2011 at 1:51 pm
Look at the example above, there is no subselect. Short of putting in your column names I coded it for you. 🙂
_______________________________________________________________
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/
March 8, 2011 at 2:08 pm
SELECT ROW_NUMBER() OVER (ORDER BY ProviderID) AS Row,
[ProviderID],
[FinancialContactPrefix],
[FinancialContact],
[FinancialPosition],
[FinancialPhone],
[FinancialExtension] ,
[FinancialFax] ,
[FinancialEmail]
FROM dbo.tblServiceProvider
now how do I add the insert?
March 8, 2011 at 2:10 pm
You had a valid insert above, just add the primarykey column that you are now adding and you should be good to go.
_______________________________________________________________
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/
March 8, 2011 at 2:11 pm
i think i got it
INSERT INTO tblFinancialContact (
[FinancialContactID],
[ProviderID],
[FinancialContactPrefix],
[FinancialContact],
[FinancialPosition],
[FinancialPhone],
[FinancialExtension] ,
[FinancialFax] ,
[FinancialEmail]
)
SELECT ROW_NUMBER() OVER (ORDER BY ProviderID) AS Row,
[ProviderID],
[FinancialContactPrefix],
[FinancialContact],
[FinancialPosition],
[FinancialPhone],
[FinancialExtension] ,
[FinancialFax] ,
[FinancialEmail]
FROM dbo.tblServiceProvider
March 8, 2011 at 2:16 pm
That looks about right.
_______________________________________________________________
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply