dastagirid

i am assuming from your post that you want a script to generate an insert statement for each row in a table, is that correct? If so, take the following scenario

table structure is

CREATE TABLE [dbo].[aspnet_Users](

[ApplicationId] [uniqueidentifier] NOT NULL,

[UserId] [uniqueidentifier] NOT NULL DEFAULT (newid()),

[UserName] [nvarchar](256) NOT NULL,

[LoweredUserName] [nvarchar](256) NOT NULL,

[MobileAlias] [nvarchar](16) NULL DEFAULT (NULL),

[IsAnonymous] [bit] NOT NULL DEFAULT ((0)),

[LastActivityDate] [datetime] NOT NULL

)

i have 2 sample rows in this table as follows

ac2c83f9-7e04-4896-a91d-81e6b366284f f51699ad-5cc0-4f65-ac3c-a1b39a895833 Admin admin NULL False 10/02/2009 21:21:29

ac2c83f9-7e04-4896-a91d-81e6b366284f ab6ca125-a595-4851-894f-9d949371eeb9 user user NULL False 10/02/2009 21:18:54

to generate an insert statement for each row for the columns username, loweredusername, mobilealias, isanonymous and lastactivitydate you would use something along the lines of the following

select 'INSERT INTO NewTable (Username, LoweredUserName, MobileAlias, IsAnonymous, lastActivityDate)

VALUES (''' + username + ''', ''' + LoweredUserName + ''', ''' +

isnull(MobileAlias, '') + ''', ' +

cast(IsAnonymous as nvarchar(2)) + ', ''' + cast(LastActivityDate as nvarchar(12)) + ''')' AS InsertString

from dbo.aspnet_Users

which would produce the following

INSERT INTO NewTable (Username, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate) VALUES ('Admin', 'admin', ' ', '0', 'Feb 10 2009')

INSERT INTO NewTable (Username, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate) VALUES ('user', 'user', ' ', '0', 'Feb 10 2009')

notice you will have to cast or convert non char items to generate the insert strings and also account for NULL values

PS this would probably have been better posted in the T-SQL forum 😉