February 5, 2010 at 2:23 pm
want to pull [state name] from another database
create a row for every state and add the same values to the other fields
DECLARE @MORDER INT
SET @MORDER = (SELECT COALESCE(Max(fOrder),0) + 1 FROM ASPXM_forums WHERE fCatID = 2);
INSERT INTO ASPXM_forums (2, fName, fName &' Forum', 8, 8, 8, 0, 0, 0, @MORDER, -1, -1)
select [State Name] as fName from npa.dbo.States
sorry, sql not my thing
February 5, 2010 at 2:27 pm
Would help if you provided the table defs (CREATE TABLE statements), some sample data (series of INSERT INTO statements), and expected output.
As a newbie on ssc, please take the time to read the first article that I reference below in my signature block, it will help you with posting the information requested above.
February 5, 2010 at 2:40 pm
need [State Name] from the states table here
USE [NPA]
GO
/****** Object: Table [dbo].[States] Script Date: 02/05/2010 16:35:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[States](
[State Code] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State Abbreviation] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State Name] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
into the ASPXM_Forum table here
USE [TPATB]
GO
/****** Object: Table [dbo].[ASPXM_forums] Script Date: 02/05/2010 16:33:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ASPXM_forums](
[FID] [int] IDENTITY(1,1) NOT NULL,
[fCatID] [int] NOT NULL,
[fName] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[fDescription] [nvarchar](500) COLLATE Latin1_General_CI_AS NULL,
[fTopicCount] [int] NOT NULL CONSTRAINT [DF_forums_fTopicCount] DEFAULT ((0)),
[fLastPost] [datetime] NULL CONSTRAINT [DF_forums_fLastPost] DEFAULT (getdate()),
[fReplyCount] [int] NOT NULL CONSTRAINT [DF_forums_fReplyCount] DEFAULT ((0)),
[fReadPermission] [int] NOT NULL CONSTRAINT [DF_forums_fReadPermission] DEFAULT ((1)),
[fPostPermission] [int] NOT NULL CONSTRAINT [DF_forums_fPostPermission] DEFAULT ((1)),
[fStartPermission] [int] NOT NULL CONSTRAINT [DF_forums_fStartPermission] DEFAULT ((1)),
[fModerated] [int] NULL CONSTRAINT [DF_forums_fModerated] DEFAULT ((0)),
[fModerateReplys] [smallint] NULL CONSTRAINT [DF_forums_fModerateReplys] DEFAULT ((0)),
[fLocked] [smallint] NOT NULL CONSTRAINT [DF_forums_fLocked] DEFAULT ((0)),
[fAlllowEncryption] [smallint] NOT NULL CONSTRAINT [DF_forums_fAlllowEncryption] DEFAULT ((0)),
[fAllowUpload] [smallint] NOT NULL CONSTRAINT [DF_forums_fAllowUpload] DEFAULT ((0)),
[fAllowSubscription] [smallint] NOT NULL CONSTRAINT [DF_forums_fAllowSubscription] DEFAULT ((0)),
[fURL] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[fIsLink] [smallint] NOT NULL CONSTRAINT [DF_forums_fIsLink] DEFAULT ((0)),
[fPassword] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[fLastTopicID] [int] NOT NULL CONSTRAINT [DF_forums_fLastTopicID] DEFAULT ((0)),
[fLastMemberID] [int] NOT NULL CONSTRAINT [DF_forums_fLastMemberID] DEFAULT ((0)),
[fLastTopic] [nvarchar](500) COLLATE Latin1_General_CI_AS NULL CONSTRAINT [DF_forums_fLastTopic] DEFAULT (''),
[fLastMember] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL CONSTRAINT [DF_forums_fLastMember] DEFAULT (''),
[fOrder] [int] NOT NULL CONSTRAINT [DF_forums_fOrder] DEFAULT ((0)),
[fHide] [smallint] NOT NULL CONSTRAINT [DF_forums_fHide] DEFAULT ((0)),
[fText] [ntext] COLLATE Latin1_General_CI_AS NULL,
[fForumID] [int] NULL CONSTRAINT [DF_ASPXM_forums_fForumID] DEFAULT ((-1)),
CONSTRAINT [PK_forums] PRIMARY KEY CLUSTERED
(
[FID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [TPATB]
GO
ALTER TABLE [dbo].[ASPXM_forums] WITH CHECK ADD CONSTRAINT [FK_forums_ASPXM_categories] FOREIGN KEY([fCatID])
REFERENCES [dbo].[ASPXM_categories] ([CatID])
------------------------------------------
need these values for each row, note the concated third value -@Morder defined above
(2, [State Name], [State Name] & ' Forum', 8, 8, 8, 0, 0, 0, @MORDER, -1, -1)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply